Daniel Westermann's Blog

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;

today I quickly wanted a visual representation of an oracle schema and remembered that oracle provides sql developer data modeler which will do this for me. headed over to the download page, wanted to download … ups, only a rpm available for linux.

but, there is alien and this makes the conversion from a rpm to a deb package straightforward and easy:

$sudo apt-get install alien
$sudo alien datamodeler-3.3.0.744-1.noarch.rpm
  Warning: Skipping conversion of scripts in package datamodeler: postinst
  Warning: Use the --scripts parameter to include the scripts.
  datamodeler_3.3.0.744-2_all.deb generated

done. installed:

$sudo dpkg -i datamodeler_3.3.0.744-2_all.deb
  Selecting previously unselected package datamodeler.
  (Reading database ... 150737 files and directories currently installed.)
  Unpacking datamodeler (from datamodeler_3.3.0.744-2_all.deb) ...
  Setting up datamodeler (3.3.0.744-2) ...

ready to use:

$which datamodeler
/usr/local/bin/datamodeler
$ datamodeler 

Oracle SQL Developer Data Modeler
 Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved. 

data modeler