Alter session slows down the query through Hibernate

Question

I'm using Oracle 11gR2 and Hibernate 4.2.1. My application is a searching application.

Only has SELECT operations and all of them are native queries.

Oracle uses case-sensitive sort by default. I want to override it to case-insensitive.

I saw couple of option here http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76966/ch2.htm#91066

Now I'm using this query before any search executes.

ALTER SESSION SET NLS_SORT='BINARY_CI'

If I execute above sql before execute the search query, hibernate takes about 15 minutes to return from search query. If I do this in Sql Developer, It returns within couple of seconds.

Why this kind of two different behaviors, What can I do to get rid of this slowness?

Note: I always open a new Hibernate session for each search.

Here is my sql:

SELECT *
FROM (SELECT
        row_.*,
        rownum rownum_
      FROM (SELECT
               a, b, c, d, e,
               RTRIM(XMLAGG(XMLELEMENT("x", f || ', ') ORDER BY f ASC)
                     .extract('//text()').getClobVal(), ', ') AS f,
               RTRIM(
                  XMLAGG(XMLELEMENT("x", g || ', ') ORDER BY g ASC)
                  .extract('//text()').getClobVal(), ', ')    AS g
             FROM ( SELECT src.a, src.b, src.c, src.d, src.e, src.f, src.g
                      FROM src src
                     WHERE upper(pp) = 'PP'
                       AND upper(qq) = 'QQ'
                       AND upper(rr) = 'RR'
                       AND upper(ss) = 'SS'
                       AND upper(tt) = 'TT')
             GROUP BY a, b, c, d, e
             ORDER BY b ASC) row_
      WHERE rownum <= 400
) WHERE rownum_ > 0;

There are so may fields comes with LIKE operation, and it is a dynamic sql query. If I use order by upper(B) asc Sql Developer also takes same time. But order by upper results are same as NLS_SORT=BINARY_CI. I have used UPPER('B') indexes, but nothings gonna work for me.

A's length = 10-15 characters

B's length = 34-50 characters

C's length = 5-10 characters

A, B and C are sort-able fields via app. This SRC table has 3 million+ records. We finally ended up with a SRC table which is a materialized view.

Business logic of the SQL is completely fine. All of the sor-table fields and others are UPPER indexed.

Answer 1

I investigated and found that The parameters NLS_COMP y NLS_SORT may affect how oracle make uses of execute plan for string ( when it is comparing or ordering).

Is not necesary to change NLS session. adding

ORDER BY NLSSORT(column , 'NLS_SORT=BINARY_CI') 

and adding a index for NLS is enough

create index column_index_binary as NLSSORT(column , 'NLS_SORT=BINARY_CI')

I found a clue to a problem in this issue so i'm paying back.

Why oracle stored procedure execution time is greatly increased depending on how it is executed?

Answer 2

UPPER() and BINARY_CI may produce the same results but Oracle cannot use them interchangeably. To use an index and BINARY_CI you must create an index like this:

create index src_nlssort_index on src(nlssort(b, 'nls_sort=''BINARY_CI'''));

Sample table and mixed case data

create table src(b varchar2(100) not null);
insert into src select 'MiXeD CAse '||level from dual connect by level <= 100000;

By default the upper() predicate can perform a range scan on the the upper() index

create index src_upper_index on src(upper(b));

explain plan for
select * from src where upper(b) = 'MIXED CASE 1';

select * from table(dbms_xplan.display(format => '-rows -bytes -cost -predicate 
    -note'));

Plan hash value: 1533361696

------------------------------------------------------------------
| Id  | Operation                   | Name            | Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 | 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SRC             | 00:00:01 |
|   2 |   INDEX RANGE SCAN          | SRC_UPPER_INDEX | 00:00:01 |
------------------------------------------------------------------

BINARY_CI and LINGUISTIC will not use the index

alter session set nls_sort='binary_ci';
alter session set nls_comp='linguistic';

explain plan for
select * from src where b = 'MIXED CASE 1';

select * from table(dbms_xplan.display(format => '-rows -bytes -cost -note'));

Plan hash value: 3368256651

---------------------------------------------
| Id  | Operation         | Name | Time     |
---------------------------------------------
|   0 | SELECT STATEMENT  |      | 00:00:02 |
|*  1 |  TABLE ACCESS FULL| SRC  | 00:00:02 |
---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NLSSORT("B",'nls_sort=''BINARY_CI''')=HEXTORAW('6D69786564
              2063617365203100') )

Function based index on NLSSORT() enables index range scans

create index src_nlssort_index on src(nlssort(b, 'nls_sort=''BINARY_CI'''));

explain plan for
select * from src where b = 'MIXED CASE 1';

select * from table(dbms_xplan.display(format => '-rows -bytes -cost -note'));

Plan hash value: 478278159

--------------------------------------------------------------------
| Id  | Operation                   | Name              | Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   | 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SRC               | 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SRC_NLSSORT_INDEX | 00:00:01 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NLSSORT("B",'nls_sort=''BINARY_CI''')=HEXTORAW('6D69786564
              2063617365203100') )