由于NLS參數(shù)設(shè)置不當(dāng)導(dǎo)致的性能問(wèn)題案例

2024-07-21 02:43:34

字體：大中小

供稿：網(wǎng)友

這篇論壇文章（賽迪網(wǎng)技術(shù)社區(qū)）針對(duì)一個(gè)由于NLS參數(shù)設(shè)置不當(dāng)導(dǎo)致的性能問(wèn)題案例進(jìn)行了深入的講解，更多內(nèi)容請(qǐng)參考下文：

錯(cuò)誤現(xiàn)象：

服務(wù)器CPU增加、響應(yīng)慢。

問(wèn)題原因：

取回statspack報(bào)告一看，問(wèn)題很快找到了，有一條語(yǔ)句的physical reads非常高，初步判斷這條語(yǔ)句沒(méi)中索引，是全表掃描。但奇怪的是，這條語(yǔ)句結(jié)構(gòu)很簡(jiǎn)單，對(duì)一個(gè)表的查詢(xún)，帶一個(gè)查詢(xún)條件。類(lèi)似如下：

select a, b from ttt where b like 'aaa%'

ttt表是個(gè)大表，這樣簡(jiǎn)單而且會(huì)導(dǎo)致全表掃描的語(yǔ)句沒(méi)道理能輕易跑到生成庫(kù)上去的啊。

查了一下，果然字段b上面也是有索引的，而且b的cardinality值很高，以上語(yǔ)句幾乎肯定命中索引，在開(kāi)發(fā)庫(kù)上看了它的查詢(xún)計(jì)劃，確實(shí)沒(méi)錯(cuò)，命中了索引，效率也很高，幾乎沒(méi)有物理讀：

Execution Plan

----------------------------------------------------------

Plan hash value: 240739660

-------------------------------------------------------------------------

-------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 60 | 2 (0)| 00:00:01 |

| 1 | TABLE access BY INDEX ROWID| TTT | 2 | 60 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | TTT_IDX | 2 | | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------

PRedicate Information (identified by operation id):

---------------------------------------------------

2 - access("B" LIKE 'aaa%')

filter("B" LIKE 'aaa%')

Statistics

----------------------------------------------------------

246 recursive calls

0 db block gets

57 consistent gets

4 physical reads

0 redo size

464 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

1 rows processed

嗯，難道是生產(chǎn)庫(kù)上的分析數(shù)據(jù)不正確（采用的是CBO），于是讓現(xiàn)場(chǎng)獲取語(yǔ)句的查詢(xún)計(jì)劃，竟然和開(kāi)發(fā)庫(kù)結(jié)果是一樣的！這就奇怪了，statspack報(bào)告上顯示這條語(yǔ)句每次都幾百萬(wàn)的物理讀啊，對(duì)比一下剛剛從生產(chǎn)庫(kù)拿到的報(bào)告，還是和前面一樣。難道有什么東西改變了語(yǔ)句的查詢(xún)計(jì)劃？為了證實(shí)這個(gè)想法，決定用更高級(jí)別的statspack來(lái)獲取該語(yǔ)句運(yùn)行時(shí)的查詢(xún)計(jì)劃（具體方法可以參加另一篇文章《利用statspack來(lái)獲取生成環(huán)境中top SQL及其執(zhí)行計(jì)劃》）。

先讓現(xiàn)場(chǎng)產(chǎn)生2個(gè)級(jí)別為6的快照，從這兩個(gè)快照生成的報(bào)告中找到了語(yǔ)句的hash值，然后生成語(yǔ)句的報(bào)告，果然和我們得到的查詢(xún)計(jì)劃不同，是全表掃描！

SQL Text

~~~~~~~~

select a, b from ttt where b like 'aaa%'

Plans in shared pool between Begin and End Snap Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Shows the Execution Plans found in the shared pool between the begin and end

snapshots specified. The values for Rows, Bytes and Cost shown below are those

which existed at the time the first-ever snapshot captured this plan - these

values often change over time, and so may not be indicative of current values

-> Rows indicates Cardinality, PHV is Plan Hash Value

-> ordered by Plan Hash Value

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

|SELECT STATEMENT | 2 | 60 | | 56 |

| TABLE ACCESS FULL | | 2 | 60 | 56 |

--------------------------------------------------------------------------------

為什么會(huì)這樣呢？

檢查程序代碼，結(jié)果在調(diào)用這條語(yǔ)句所在函數(shù)之前的語(yǔ)句中，我們發(fā)現(xiàn)了以下語(yǔ)句：

Execute Immediate ' alter session set nls_comp=LINGUISTIC'

Execute Immediate ' alter session set nls_sort=BINARY_CI'

嗯，這下我基本明白了。看到這兩個(gè)會(huì)話(huà)參數(shù)的設(shè)置我知道程序員是想對(duì)查詢(xún)條件不區(qū)分大小寫(xiě)。這是10gR2的新特性，我也曾經(jīng)在我的blog上介紹過(guò)，但是，我也提到過(guò)，這會(huì)導(dǎo)致查詢(xún)無(wú)法正確命中索引，這一點(diǎn)卻被該程序員忽略了。可以看以下結(jié)果：

SQL> alter session set nls_sort='BINARY_CI';

Session altered.

SQL> alter session set nls_comp='LINGUISTIC';

Session altered.

SQL> set autot trace

SQL> select a, b from ttt where b like 'aaa%';

Execution Plan

----------------------------------------------------------

Plan hash value: 774701505

-------------------------------------------------------------------------

-------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 60 | 56 (2)| 00:00:01|

|* 1 | TABLE ACCESS FULL| TTT | 2 | 60 | 56 (2)| 00:00:01|

-------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("B" LIKE 'aaa%')

Statistics

----------------------------------------------------------

364 recursive calls

0 db block gets

321 consistent gets

73 physical reads

0 redo size

560 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

8 sorts (memory)

0 sorts (disk)

5 rows processed

SQL> alter session set nls_sort='BINARY';

Session altered.

SQL> alter session set nls_comp='BINARY';

Session altered.

SQL> select a, b from ttt where b like 'aaa%';

Execution Plan

----------------------------------------------------------

Plan hash value: 240739660

-------------------------------------------------------------------------

-------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 60 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TTT | 2 | 60 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | TTT_IDX | 2 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------

---------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("B" LIKE 'aaa%')

filter("B" LIKE 'aaa%')

Statistics

----------------------------------------------------------

8 recursive calls

0 db block gets

6 consistent gets

3 physical reads

0 redo size

464 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

這里關(guān)于這個(gè)問(wèn)題再說(shuō)多兩句，當(dāng)時(shí)我的文章中認(rèn)為通過(guò)nls設(shè)置不區(qū)分大小寫(xiě)的實(shí)質(zhì)是Oracle內(nèi)部加了upper函數(shù)，這個(gè)結(jié)論應(yīng)該是錯(cuò)的。實(shí)質(zhì)上，此時(shí)在做精確匹配時(shí)應(yīng)該是做了NLSSORT函數(shù)轉(zhuǎn)換：

SQL> alter session set nls_sort='BINARY_CI';

Session altered.

SQL> alter session set nls_comp='LINGUISTIC';

Session altered.

SQL> select a, b from ttt where b = 'aaa';

Execution Plan

----------------------------------------------------------

Plan hash value: 774701505

-------------------------------------------------------------------------

-------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 60 | 57 (4)| 00:00:01|

|* 1 | TABLE ACCESS FULL| TTT | 2 | 60 | 57 (4)| 00:00:01|