屡次发生的Oracle谓词越界

痛定思痛。 2022-10-22 07:58 196阅读 0赞

近期在客户现场屡次遇到由于统计信息过旧导致执行计划选错引发的数据库性能问题，今天做个**总结**：  
谓词越界常见发生在where谓词是时间字段的，总的来说统计信息记录的是一个过旧的时间，而SQL传入的时间是一个最新的时间范围（往往是<time time1<c<time2），由于统计信息不全，按照CBO计算出来的结果集就很小，在多表关联的情况下，CBO就会选择认为的最优的关联方式，而实际执行时发现不是那么回事，有大量结果集需要扫描，就会爆发SQL性能问题。

谓词越界就是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%以上才会进行统计信息收集，大表不常收集统计信息就容易爆发谓词越界

## 预防方式： ##

可对关键表实行按谓词查询条件分区，即按天或者按月分区可规避此问题发生。