About

Martin Klier

usn-it.de

How NLS settings can affect Oracle’s join behavior – v$ example

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

Oracle SQL: Optimizing a WHERE predicate inequality with a Function Based Index
DOAG Konferenz 2013: “Oracle Architektur – nicht nur für Einsteiger”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.