This content has been marked as final. Show 1 reply
Oracle Performance issue after change the session parameters NLS_SORT and NLS_COMP.
The default value of NLS_SORT='BINARY_CI' and NLS_COMP='BINARY' which you could get from "select * from nls_database_parameters". When people use Oracle full text search function, as suggestion to correctly return the sorted result those parameters will be changed to NLS_SORT='BINARY_CI' and NLS_COMP='LINGUISTIC'.
The parameter 'LINGUISTIC' will completely change the search execution plan. If the index is not created for "Linguistic" purpose (like case insensitive, multilingal), the index espacially with text type (VARCHAR2) may not be selected by Oracle even you specify an index hint. See reason in Oracle document.
To avoid this happen, do not change those two parameters in global level. Only use it in your specific session which requires Linguistic search. It could be done by set SQL command
"ALTER SESSION SET NLS_COMP='LINGUISTIC'" or "ALTER SESSION SET NLS_COMP='ansi'"
and don't forget change your index creation with following parameter:
CREATE INDEX myCaseLessIndex
NLSSORT(searchColum, 'NLS_SORT = BINARY_CI')
Note: As the NLS_SORT parameter will affect all indexed search critera, make sure all expected indexes have the corresponding NLS_SORT type index. Another word using case insensitive search you have to create additional Linguistic indexes for all your PK and indexes fileds otherwise Oracle may do full table scan!!!
In addition to this below doc link is must for more details :
I found below also a good note :