Be careful when setting nls_sort and nls_comp to become performance killer.

Nls_sort and nls_comp are two initialization parameters for Oracle. As the name suggests, nls_sort and nls_comp are the methods for setting sorting and comparison. You can set these two parameters to customize the sorting and comparison methods. The values of these two parameters can be specified during database creation or modified using the alter session Statement. If it is set during database creation, it cannot be modified and plays a role in all sessions. However, we also use the alter session command to temporarily change the values of these two parameters, and temporarily use a specific sort and comparison method in the session. To change the sorting and comparison of all sessions, we can also modify the Registry. If the system adopts a multi-layer architecture and all the logic layersCodeAll deployed in the same applicationProgramOn the server (such as IIS.

To view the two parameter values in a session, we can use the following query statement:

Select * From nls_session_parameters;

Parameter Value
--------------------------------------------------------------------------------------------------------------------------------------------
Nls_language Simplified Chinese
Nls_territory China
Nls_currency RMB
Nls_iso_currency China
Nls_numeric_characters .,
Nls_calendar Gregorian
Nls_date_format DD-MON-RR
Nls_date_language Simplified Chinese
Nls_sort binary

Nls_time_format HH. Mi. ssxff AM
Nls_timestamp_format DD-MON-RR HH. Mi. ssxff AM
Nls_time_tz_format HH. Mi. ssxff am tzr
Nls_timestamp_tz_format DD-MON-RR HH. Mi. ssxff am tzr
Nls_dual_currency RMB
Nls_comp binary
Nls_length_semantics byte
Nls_nchar_conv_excp false

In one of our projects, the customer suddenly asked that all the queries be case-insensitive. However, because the values of the two parameters were not set during database creation, it is very clumsy and absurd to execute the alter session Statement in none of the query statements to set these two values. Therefore, we use the method of modifying the Registry to achieve this goal, however, one day, the customer suddenly reported that the program was running very fast and suddenly became very slow. The slow representation is that the user creates a large change order, which contains thousands of change records. From the error information, I analyzed the cause of timeout. Timeout means that the system cannot respond for a long time or the transaction cannot be completed for a long time.

Production Environment error is fatal. Based on the data provided by the user, we immediately conducted a test. The results showed that the test was completed quickly in a few seconds! The user exports the data to another test server and runs the same result. the user is remotely instructed to run the stored procedure directly on the test database. The result is displayed to me the next day, it took over 20 hours to complete the stored procedure !!

It is also an analysis and optimization of the storage process, and an analysis of the settings of various aspects of server settings, but the problem still cannot be solved. Another day has passed, so it's really fascinating. When I searched for information at home in the evening, the user told me on MSN that the problem was solved and I was surprised. How did I solve it? The User told me that the registration items of nls_sort and nls_comp modified in the registry will be deleted, and IIS will be restarted, and the program running speed will suddenly increase, it was done in a few seconds !!

In the Oracle official document, I did mention that changing the default values of nls_sort and nls_comp may affect the performance, but I didn't think it would have such a big impact. This is the description of nls_sort in the official Oracle documentation.

Nls_sort

property		description
parameter type	string
syntax	nls_sort = {binary | linguistic_definition }
default value	derived from nls_language
modifiable	alter session
Range of Values	Binary or any valid linguistic definition name

 

Nls_sort specifies the collating sequence for order by queries.

• If the value is binary, then the collating sequence for order by queries is based on the numeric value of characters (a binary sort that requires less system overhead ).
  
• If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. most (but not all) supported ages supported by the nls_language parameter also support a linguistic sort with the same name.
  

  note:
  

  setting nls_sort to anything other than binary causes a sort to use a full table scan, regardless of the path chosen by the optimizer. binary is the exception because indexes are built according to a binary order of keys. thus the optimizer can use an index to satisfy the order by clause when nls_sort is set to binary. if nls_sort is set to any linguistic sort, the optimizer must include a full table scan and a full sort in the execution plan.
  

You must use the nls_sort operator with comparison operations if you want the linguistic sort behavior.

According to the above marked red watch, if nls_sort is not set to "binary", it will cause a full table scan and will not use the index, in our system, the data involved in the change order is a huge table. If indexes are not used, the query efficiency will not be affected!

 

Nls_comp

property	description
parameter type	string
syntax	nls_comp = {binary | linguistic | ANSI}
default value	binary
modifiable	alter session
Basic	No

 

Nls_comp specifies the collation behavior of the database session.

Values:

• Binary
  

Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless you specify the nlssort function.

• Linguistic
  

Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks shocould use the linguistic sort specified in the nls_sort parameter.To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.

• ANSI
  

A setting of ANSI is for backwards compatibility; in general, you shoshould set nls_comp to linguistic

To improve the performance, you can createLinguistic Index.