Hi All,
Database server Environment Details:
2 Node Oracle 10.2.0.4 RAC on Solaris Operating System
Parameter in Spfile:
nls_sort = BINARY_CI
nls_comp = LINGUISTIC
nls_language = AMERICAN
Parameters in database:
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 10.2.0.4.0
20 rows selected.
Parameters at client's environment variable(Its Application sever on Windows):
Oracle Client 10.2.0.4
nls_sort = BINARY_CI
nls_comp = LINGUISTIC
Now each and every index on character datatype columns in this database are created as Function Based Index for supporting Case Insensitive searches by using nls_sort to BINARY_CI as shown below.
CREATE UNIQUE INDEX UX_NAME_BR ON ONS (NLSSORT("NAME",'nls_sort=''BINARY_CI'''),"TYPE_ID", "UNIT_NUMBER", "DOMICILE", "IS_ACTIVE", "ID")
My worry started when i by mistake created a normal btree index on a charecter column. After this mistake just for curiosity i enabled index monitoring on this index, when i checked in v$object_usage it GOT USED !!!!
So i strongly suspect that there are some sessions connected to this database with different NLS values instead of nls_sort=BINARY and nls_comp=LINGUISTIC.....
Any way i did a small test in production to confirm this as shown below:
PARAMETER VALUE
-------------------------------------------------- ------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
SQL> set autotrace traceonly exp
SQL> select id,is_active from ons where domicile = 'US' and id = 440 and name = 'AMERICAN COMPANY';
Execution Plan
----------------------------------------------------------
Plan hash value: 1171456783
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 1 | 49 | 4 (0)| 00:00:01 | 1 | 3 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| ONS | 1 | 49 | 4 (0)| 00:00:01 | 1 | 3 |
|* 3 | INDEX RANGE SCAN | IDX_COD | 1 | | 4 (0)| 00:00:01 | 1 | 3 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=440)
3 - access("DOMICILE"='US' AND "NAME"='AMERICAN COMPANY')
SQL> alter session set nls_sort=BINARY_CI;
Session altered.
SQL> alter session set nls_comp=LINGUISTIC;
Session altered.
SQL> select * from nls_session_parameters;
PARAMETER VALUE
-------------------------------------------------- ------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY_CI
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP LINGUISTIC
NLS_LENGTH_SEMANTICS CHAR
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
SQL> select id,is_active from ons where domicile = 'US' and id = 440 and name = 'AMERICAN COMPANY';
Execution Plan
----------------------------------------------------------
Plan hash value: 270874147
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 2 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| ONS | 1 | 49 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | UX_NAME_BR | 1 | | 2 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('669696E6720636F6D70
616E7900') AND "ID"=440)
filter("ID"=440 AND NLSSORT(INTERNAL_FUNCTION("DOMICILE"),'nls_sort=''BINARY_CI
''')=HEXTORAW('7588700') )
IDX_COD index is an normal btree index....
So how can i find which sessions are accessing these index and find the session level nls parameters connected to this database.
I know there is no way to find nls parameters for other sessions without tracing it..
Below tables will not work me
NLS_DATABASE_PARAMETERS
NLS_SESSION_PARAMETERS
Could any one please help me in finding those sessions those sessions which are not using session level parameters as BINARY_CI and LINGUISTIC