Recently, I had a problem with accessing Active Session History being not fast enough on Oracle Database 11.2 and 12.1. Looking at the explain plan, wow, no doubt why: Accessing two fixed tables with TABLE ACCESS FULL and joining them with NESTED LOOP. This couldn’t have been “works as designed”, it would render gv$active_session_history nearly useless. But from the beginning.

My query

select *
from gv$active_session_history
where SAMPLE_TIME>sysdate -1
and sql_id='f29fxwd5kh2pq';

3 - filter("S"."SAMPLE_TIME">SYSDATE@!-1)
4 - filter(NLSSORT("A"."SQL_ID",'nls_sort=''BINARY_CI''')=HEXTORAW('66323966787764356
B6832707100') AND "A"."SAMPLE_TIME">SYSDATE@!-1 AND "S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR"
AND "S"."SAMPLE_ID"="A"."SAMPLE_ID" AND "S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND
NLSSORT("S"."NEED_AWR_SAMPLE",'nls_sort=''BINARY_CI''')=NLSSORT("A"."NEED_AWR_SAMPLE",'n
ls_sort=''BINARY_CI''') AND ("A"."CON_ID"=0 OR "A"."CON_ID"=3))

Can you see the issue? The key is the filter in line 4 on X$ASH which defines the data reduction by join condition NEED_AWR_SAMPLE on this table. You can see the details in the predicate information: It does not compare the columns, it wraps them in a NLSSORT() function. And obviously, the fixed index on X$ASH.NEED_AWR_SAMPLE is useless for this condition. The NLSSORT(x,nls_sort=”BINARY_CI”) call asked for a review of the NLS settings on this database.

Easy check

select *
from nls_session_parameters
where parameter in ('NLS_COMP','NLS_SORT');

3 - filter("S"."SAMPLE_TIME">SYSDATE@!-1)
4 - filter("A"."SQL_ID"='f29fxwd5kh2pq' AND "A"."SAMPLE_TIME">SYSDATE@!-1 AND
"S"."SAMPLE_ADDR"="A"."SAMPLE_ADDR" AND "S"."SAMPLE_ID"="A"."SAMPLE_ID" AND
"S"."SAMPLE_TIME"="A"."SAMPLE_TIME" AND "S"."NEED_AWR_SAMPLE"="A"."NEED_AWR_SAMPLE" AND
("A"."CON_ID"=0 OR "A"."CON_ID"=3))

Now it’s using a fixed index, and when running the query, it comes back after a few seconds.

Explanation

Because the NLS_COMP (comparison) value was set to LINGUISTIC, so Oracle
translates the join condition for you to use linguistic equivalent rather
than binary values. This allows for binary values that map to equivalent
values in some language to evaluate as equals. For purposes of the values in
NEED_SAMPLE this probably never makes a difference to the answer, but Oracle
injects the change to processing views nonetheless.

Lessons learned

The execution or explain plans are firefighter’s best friend

Always try to understand and afterwards explain predicates to yourself or a friend

Last and most important: Non-default NLS settings can bite you unexpectedly, so check for them, or, if ever possible, avoid them!

If you need …

to set NLS_COMP and NLS_SORT to my values (as my system does), you only can try to fix your own object queries by creating function based indexes like