數據庫性能問題總結--屢次發生的Oracle謂詞越界

作者：任艷杰 2021-04-09 21:07:07

近期在客戶現場屢次遇到由于統計信息過舊，導致執行計劃選錯引發的數據庫性能問題，今天做個總結。

本文轉載自微信公眾號「數據和云」，作者任艷杰。轉載本文請聯系數據和云公眾號。

近期在客戶現場屢次遇到由于統計信息過舊，導致執行計劃選錯引發的數據庫性能問題，今天做個總結。

謂詞越界常見發生在 where 謂詞是時間字段的情況，總的來說統計信息記錄的是一個過舊的時間，而 SQL 傳入的時間是一個最新的時間范圍(往往是 <time time1<c<time2）由于統計信息不全，按照 CBO 計算出來的結果集就很小，在多表關聯的情況下，CBO 就會選擇認為的最優的關聯方式，而實際執行時發現不是那么回事，有大量結果集需要掃描，就會爆發 SQL 性能問題。最優的關聯方式，而實際執行時發現不是那么回事，有大量結果集需要掃描，就會爆發="" 計算出來的結果集就很小，在多表關聯的情況下，cbo="" cbo="" time1

謂詞越界就是 select 的謂詞的條件不在統計信息 low_value 和 high_value 之間，在實際選擇結果集要大于 CBO 記錄的結果集數量，即實際的 selectivity 偏大，這種情況下 CBO 評估出來的 selectivity 會出現嚴重的偏差，導致 CBO 選錯執行計劃。

測試驗證

下面做一組測試，從執行計劃 cost 看謂詞越界的發生過程，先插入部分數據：

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; 
/

查看此時的 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

不收集統計信息，此時統計列統計信息過舊，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

查詢結果返回 2081 行結果集。

TEST@PROD1> select count(*) from test_obj where object_id between 78200 and 81000; 
  COUNT(*) 
---------- 
      2801 
計算公式為： 
selectivity=((VAL2 - VAL1) / (HIGH_VALUE - LOW_VALUE)+2 / NUM_DISTINCT) * null_adjust 
null_adjust=(NUM_ROES - NUM_NULLS) / NUM_ROES 
 
計算結果為： 
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

查看結果集發現 dictionary 值為 1，這明顯是一個錯誤的執行計劃，由于統計信息過舊，已經低于謂詞條件區間(謂詞過界)導致 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

重新收集統計信息再次查看執行計劃。

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

此時統計信息 HIGH_VALUE 已經和最初計算的值相等，Typ=2 Len=2: c3,b。再次查看執行計劃，此時 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 |   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

謂詞越界主要發生在大表，按照 Oracle 統計信息收集機制，表的數據變化量達到 10% 以上才會進行統計信息收集，大表不常收集統計信息就容易爆發謂詞越界。

預防方式

可對關鍵表實行按謂詞查詢條件分區，即按天或者按月分區可規避此問題發生。

責任編輯：武曉燕來源：數據和云

Oracle 數據庫謂詞