Issue with NLS_SORT & NLS_COMP
Date: Wed, 18 Nov 2009 12:15:18 -0500
I was hoping to get some assistance from the list on solving an interesting problem I am having.
We are trying to have case insensitive searches within a query. In order to do so, development is setting 2 parameters at the session level.
alter session set NLS_SORT=BINARY_CI;
alter session set NLS_COMP=LINGUISTIC;
The query takes a very long time to run, and occassionally stops with the waits 'SQL*Net message to client'. When I ran the query manually, the results returned in less than 5 seconds. When I set NLS_SORT and NLS_COMP the query again hangs and I see the 'SQL*Net message to client' in the wait events. When I set one or the other NLS_COMP or NLS_SORT properties, the query finishes very quickly. In looking at the trace file (10046, level 12), the file size of the one that completes very quickly is roughly 3MB in size. The one that 'hangs' is 7k in size and stuck at 'SQL*Net message to client'.
I am using Oracle RAC on 10.2.0.4 (RHEL 5.3 64-bit) and the client is also at 10.2.0.4.
Any ideas what could be causing this too hang? Any better solutions for supporting case insensitive searches (this is an OBIEE query).
This is the predicate of how they are doing the searches.
COUNTRY_NAME like concat(concat('%', ''), '%') and PROVIDER like concat(concat('%', ''), '%') and CONNECTION_TYPE like concat(concat('%', ''), '%')
TomReceived on Wed Nov 18 2009 - 11:15:18 CST