數(shù)據(jù)庫性能問題總結(jié)--屢次發(fā)生的Oracle謂詞越界
本文轉(zhuǎn)載自微信公眾號(hào)「數(shù)據(jù)和云」，作者任艷杰。轉(zhuǎn)載本文請(qǐng)聯(lián)系數(shù)據(jù)和云公眾號(hào)。
近期在客戶現(xiàn)場屢次遇到由于統(tǒng)計(jì)信息過舊，導(dǎo)致執(zhí)行計(jì)劃選錯(cuò)引發(fā)的數(shù)據(jù)庫性能問題，今天做個(gè)總結(jié)。
謂詞越界常見發(fā)生在 where 謂詞是時(shí)間字段的情況，總的來說統(tǒng)計(jì)信息記錄的是一個(gè)過舊的時(shí)間，而 SQL 傳入的時(shí)間是一個(gè)最新的時(shí)間范圍(往往是 <time time1<c<time2）由于統(tǒng)計(jì)信息不全，按照 CBO 計(jì)算出來的結(jié)果集就很小，在多表關(guān)聯(lián)的情況下，CBO 就會(huì)選擇認(rèn)為的最優(yōu)的關(guān)聯(lián)方式，而實(shí)際執(zhí)行時(shí)發(fā)現(xiàn)不是那么回事，有大量結(jié)果集需要掃描，就會(huì)爆發(fā) SQL 性能問題。
謂詞越界就是 select 的謂詞的條件不在統(tǒng)計(jì)信息 low_value 和 high_value 之間，在實(shí)際選擇結(jié)果集要大于 CBO 記錄的結(jié)果集數(shù)量，即實(shí)際的 selectivity 偏大，這種情況下 CBO 評(píng)估出來的 selectivity 會(huì)出現(xiàn)嚴(yán)重的偏差，導(dǎo)致 CBO 選錯(cuò)執(zhí)行計(jì)劃。
測試驗(yàn)證
下面做一組測試，從執(zhí)行計(jì)劃 cost 看謂詞越界的發(fā)生過程，先插入部分?jǐn)?shù)據(jù)：
- DECLARE
- i INT;
- BEGIN
- i := 78179;
- WHILE(i < 100000)
- LOOP
- i := i + 1;
- INSERT INTO test_obj(object_id) VALUES(i);
- COMMIT;
- END LOOP;
- END;
- /
查看此時(shí)的 num_rows：
- TEST@PROD1> select count(*) from test_obj;
- COUNT(*)
- ----------
- 94283
- TEST@PROD1> select max(object_ID),dump(max(object_id),16) from test_obj;
- MAX(OBJECT_ID) DUMP(MAX(OBJECT_ID),16)
- -------------- ----------------------------------------
- 100000 Typ=2 Len=2: c3,b
- TEST@PROD1> select min(object_ID),dump(min(object_id),16) from test_obj;
- MIN(OBJECT_ID ) DUMP(MIN(OBJECT_ID),16)
- ------------------------------ ----------------------------------------
- 2 Typ=2 Len=2: c1,3 --C103
不收集統(tǒng)計(jì)信息，此時(shí)統(tǒng)計(jì)列統(tǒng)計(jì)信息過舊，HIGH_VALUE 依然是原來的值 78179。
- TEST@PROD1> select low_value ,high_value,num_distinct,num_nulls from DBA_TAB_COL_STATISTICS where table_name='TEST_OBJ' and owner='TEST';
- Distinct Number
- LOW_VALUE HIGH_VALUE Values Nulls
- ------------------------------ ------------------------------ ------------ ----------
- C103 C3085250 72,462（原值） 0
查詢結(jié)果返回 2081 行結(jié)果集。
- TEST@PROD1> select count(*) from test_obj where object_id between 78200 and 81000;
- COUNT(*)
- ----------
- 2801
- 計(jì)算公式為：
- selectivity=((VAL2 - VAL1) / (HIGH_VALUE - LOW_VALUE)+2 / NUM_DISTINCT) * null_adjust
- null_adjust=(NUM_ROES - NUM_NULLS) / NUM_ROES
- 計(jì)算結(jié)果為：
- TEST@PROD1> select round(((81000-78200)/(100000-2)+2/94283)*(94283-0)/94283*94283) from dual;
- ROUND(((81000-78200)/(100000-2)+2/94283)*(94283-0)/94283*94283)
- ---------------------------------------------------------------
- 2642
查看結(jié)果集發(fā)現(xiàn) dictionary 值為 1，這明顯是一個(gè)錯(cuò)誤的執(zhí)行計(jì)劃，由于統(tǒng)計(jì)信息過舊，已經(jīng)低于謂詞條件區(qū)間(謂詞過界)導(dǎo)致 CBO 低估了查詢成本。
- TEST@PROD1> select count(*) from test_obj where object_id between 78200 and 81000;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2217143630
- -------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 5 | 289 (1)| 00:00:04 |
- | 1 | SORT AGGREGATE | | 1 | 5 | | |
- |* 2 | TABLE ACCESS FULL| TEST_OBJ | 1 | 5 | 289 (1)| 00:00:04 |
- -------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("OBJECT_ID">=78200 AND "OBJECT_ID"<=81000)
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 1117 consistent gets
- 0 physical reads
- 0 redo size
- 423 bytes sent via SQL*Net to client
- 419 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
重新收集統(tǒng)計(jì)信息再次查看執(zhí)行計(jì)劃。
- TEST@PROD1> exec dbms_stats.gather_table_stats('test','test_obj');
- TEST@PROD1> select low_value ,high_value,num_distinct,num_nulls from DBA_TAB_COL_STATISTICS where table_name='TEST_OBJ' and owner='TEST';
- Distinct Number
- LOW_VALUE HIGH_VALUE Values Nulls
- -------------------- -------------------- ------------ ----------
- C103 C30B 94,283 0
此時(shí)統(tǒng)計(jì)信息 HIGH_VALUE 已經(jīng)和最初計(jì)算的值相等，Typ=2 Len=2: c3,b。再次查看執(zhí)行計(jì)劃，此時(shí) CBO 已經(jīng)能夠產(chǎn)生了正確的執(zhí)行計(jì)劃了。
執(zhí)行計(jì)劃為：
- TEST@PROD1> select count(*) from test_obj where object_id between 78200 and 81000;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2217143630
- -------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 5 | 314 (1)| 00:00:04 |
- | 1 | SORT AGGREGATE | | 1 | 5 | | |
- |* 2 | TABLE ACCESS FULL| TEST_OBJ | 2642 | 13210 | 314 (1)| 00:00:04 |
- -------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("OBJECT_ID">=78200 AND "OBJECT_ID"<=81000)
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 1117 consistent gets
- 0 physical reads
- 0 redo size
- 423 bytes sent via SQL*Net to client
- 419 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
謂詞越界主要發(fā)生在大表，按照 Oracle 統(tǒng)計(jì)信息收集機(jī)制，表的數(shù)據(jù)變化量達(dá)到 10% 以上才會(huì)進(jìn)行統(tǒng)計(jì)信息收集，大表不常收集統(tǒng)計(jì)信息就容易爆發(fā)謂詞越界。
預(yù)防方式
可對(duì)關(guān)鍵表實(shí)行按謂詞查詢條件分區(qū)，即按天或者按月分區(qū)可規(guī)避此問題發(fā)生。
