【转】 小心NLS_SORT和NLS_COMP的设置成为性能杀手

小心NLS_SORT和NLS_COMP的设置成为性能杀手

http://hi.baidu.com/yzcp558/blog/item/ba753b1f9c509e0d314e1576.html

 

2008-11-02 20:03

NLS_SORT和NLS_COMP是Oracle两个初始化参数。顾名思义，NLS_SORT和NLS_COMP是设置排序和比较的方式。通过设置这两个参数可以实现自定义的排序和比较的方式。设置这两个参数的值可以在数据库创建的时候指定，也可以通过Alter Session语句来修改。如果是在创建数据库的时候设定，那么是不能再修改的，并在所有的Session中起作用。但是我们也通过Alter Session命令临时改变这两个参数的值，在会话中临时使用一种特定的排序和比较的方式。要改变所有的会话的排序和比较的方式，我们也可以通过修改注册表的方式来达到目的，如果系统采用的多层体系架构，并且所有的逻辑层代码都部署在相同的应用程序服务器（如IIS）上的。 查看会话中这两个参数值，我们可以使用如下的查询语句： 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      在我们的一个项目中，客户突然要求所有的查询都大小写无关，但是由于在创建数据库的时候没有设置两个参数的值，在没一个查询语句中去执行Alter Session语句来设置这两个值又显得十分的笨拙和荒唐，所以我们采用修改注册表的方式来达到这个目的，但是有一天客户突然报告原来运行的很快的程序，突然变得特别的慢。慢的表征是用户创建一个比较大的变更单，变更单里包含了几千条变更记录。从出错的信息中我分析出是超时的原因，超时也就意味者系统长时间无法响应或者事务长时间无法完成。      生产环境出错是非常致命的。根据用户提供的数据我们马上做了测试，结果表明是在几秒钟就迅速完成了！用户将数据导出到另外一台测试服务器上运行，还是相同的结果，远程指导用户直接在测试库上运行存储过程，结果第二天用户告诉我，执行完该存储过程整整花了20多个小时！！ 又是一番针对存储过程的分析和优化，针对服务器设置的各个方面的设置的分析，但是问题仍然得不到解决，百思不得其解。又是一天过去了，真让人心焦啊。晚上在家里查找资料的时候，用户在MSN上告诉我，问题得到解决了，我一惊，是怎么解决的？用户告诉我，将在注册表中修改了NLS_SORT和NLS_COMP的注册项删除掉，重新启动IIS，程序运行的速度就一下子提高了，确实就是在几秒钟内完成的！！ 在Oracle官方提高的文档中，确实提到如果改变了NLS_SORT和NLS_COMP的缺省值，可能会对性能产生影响但是我没有想到会有这么大的影响。在Oracle官方文档中对NLS_SORT是这样描述的。 NLS_SORT NLS_SORT specifies the collating sequence for ORDER BY queries. NLS_COMP NLS_COMP specifies the collation behavior of the database session. 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     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) languages 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. 根据上文中标红部分的注视，如果NLS_SORT不是设置为"Binary"，那么就会引起全表扫描，是不会使用索引的，在我们的系统中变更单涉及到的数据都是数据庞大的表，如果不使用到索引，查询的效率不受到影响才怪呢！     Property Description Parameter type String Syntax NLS_COMP = { BINARY | LINGUISTIC | ANSI } Default value BINARY Modifiable ALTER SESSION Basic No     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 should 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 should set NLS_COMP to LINGUISTIC 根据标红的部分，要提高性能可以在需要比较的列上建立一个linguistic index。