NLS_SORT and execution plans

we faced an interesting performance issue last week. the situation was ( 11.2.0.2 on Linux x64 ):

• every day at the same time there was ORA-1652 reported in the alert log
• at the same time ORA-12012 was logged

figuring out what the job executes and extracting the statement which caused this was not a big deal. as we did not know the password for the user in question we tried to reproduce this like:

alter session set current_schema=THE_USER_IN_QUESTION;
-- exec statement

when we looked at the execution plan nothing seemed to be wrong. the statement executed in a fraction of a second and the right indexes were used. all fine. our next guess was that the data might change just before the job starts so we executed the statement at the same time the job starts. but again, everything was fine. but the same error messages where logged to the alert log from the job execution. at this point it was almost clear that the optimizer environment is somehow different when the user executes the statement. ok, time to request the password for the user and to test in a real environment ( another option would have been to generate the execution plan out of the awr data ). and suddenly the statement took almost seven minutes to complete. what happened?

looking at the execution plan again no index was used and the problematic part was this one:

1 - filter(NLSSORT("A",'nls_sort=''BINARY_CI''')=HEXTORAW

a quick check over dba_source figured out the issue:

create trigger my_trg after logon on schema
begin
  execute immediate 'alter session set NLS_COMP=LINGUISTIC';
  execute immediate 'alter session set NLS_SORT=BINARY_CI';
end;
/

this caused the statement to consume huge amounts of temp space ( it was a “create table as select where” statement ), no index could be used and the execution time bumped up from nearly zero to around seven minutes.

the documentation clearly says: be careful when setting this as this might change the execution plans and normal indexes can not be used.

simple test case:

drop table t1;
create table t1 ( a varchar2(100), b number );
insert into t1
with blubb as 
( select lpad('A',100,'A')
       , trunc(dbms_random.value(1,10000))
    from dual
   connect by rownum < 10001
)
select *
  from blubb;
create index i1 on t1(a);
update t1
   set a = lpad('B',100,'B')
 where mod(b,77) = 0;
commit;
set autotrace on;
select *
  from t1
 where a = lpad('B',100,'B');
alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT=BINARY_CI;
select *
  from t1
 where a = lpad('B',100,'B');
alter session set NLS_COMP=BINARY;
alter session set NLS_SORT=BINARY;