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';
And it simply does not come back within an hour. So what?
The explain plan
PLAN_TABLE_OUTPUT -------------------------- Plan hash value: 399192445
------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 1373 | 0 (0)| 00:00:01 | | 1 | VIEW | GV$ACTIVE_SESSION_HISTORY | 1 | 1373 | 0 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 543 | 0 (0)| 00:00:01 | |* 3 | FIXED TABLE FULL| X$KEWASH | 1 | 21 | 0 (0)| 00:00:01 | |* 4 | FIXED TABLE FULL| X$ASH | 1 | 522 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
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');
PARAMETER VALUE ------------------------------ ---------- NLS_SORT BINARY_CI NLS_COMP LINGUISTIC
Make an educated guess…
What to change
Testing it with more conventional NLS_COMP and NLS_SORT settings, allowed more hope:
alter session set NLS_COMP='BINARY'; alter session set NLS_SORT='BINARY';
explain plan for select * from gv$active_session_history where SAMPLE_TIME>sysdate -1 and sql_id='f29fxwd5kh2pq'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT --------------------------- Plan hash value: 2905781256
------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1373 | 0 (0)| 00:00:01 | | 1 | VIEW | GV$ACTIVE_SESSION_HISTORY | 1 | 1373 | 0 (0)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 543 | 0 (0)| 00:00:01 | |* 3 | FIXED TABLE FULL | X$KEWASH | 1 | 21 | 0 (0)| 00:00:01 | |* 4 | FIXED TABLE FIXED INDEX| X$ASH (ind:1) | 1 | 522 | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
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
By Mark W. Farnham, from the ever-helpful oracle-l mailing list, here: http://www.freelists.org/post/oracle-l/Accessing-ASH-is-slow,8
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
create index TABLEX_TUNING_1 on TABLEX (NLSSORT(COLUMN1,'NLS_SORT='BINARY_CI''');
But of course, this is no option for fixed objects in SYS…
Versions “affected”
As stated earlier, this behavior did not change for some time. I was able to reproduce it on 11.2.0.3.0, 11.2.0.3.7 and 12.1.0.1.0.
Take care
Usn