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.

Are you saying the `ALTER SESSION` only negatively impacts queries through hibernate? That `ALTER` could very easily prevent index access paths, making queries significantly slower. But it should work the same way in SQL Developer. Are you sure that both environments are running the exact same SQL statement? — Jon Heller, Sep 12 '13 at 05:13
@jonearles Yes I ran the same query. But via hibernate it is not a single SQL. I have to execute 2 queries using same session. On Sql developer, I open a tab, run the `ALTER` query, and then run the `SELECT *...ORDER BY` query in the same tab. Works fine. No slowness there. Sometimes this might not be a Hibernate issue I suppose. But this is happening :( — sura2k, Sep 12 '13 at 09:56
Can you post the exact SQL statements that are run against the database, both through hibernate and in SQL Developer? The solution will probably be to create a function based index with a specific sort, but we'd need to see the queries to know for sure. — Jon Heller, Sep 12 '13 at 14:41

score 1 · Answer 1 · answered Sep 13 '13 at 16:51

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') )

Still it is happening. Finally I added 3 additional columns UPPER_A, UPPER_B, UPPER_C as an interim solution. Now I just do `ORDER BY UPPER_B`. This may not be a good solution. But for now I go with this. I tried all of the combination that I can think of without thinking the purpose of them. Not a single one worked. — sura2k, Sep 15 '13 at 17:47
And this is happening only for few search scenarios. We found this under UAT phase. All the others work with even `ORDER BY UPPER`. If I use any index or put ORDER BY UPPER into inner most SELECT, it works. But that's not what I want to do. Aggregation-SELECT query doesn't like these indexes, ORDER BY UPPER or Altering NLS_SORT *SOMETIMES* only for some scenarios. — sura2k, Sep 15 '13 at 17:52

score 1 · Answer 2 · edited May 23 '17 at 12:25

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?

Alter session slows down the query through Hibernate

2 Answers2

Linked