传说中的“谓词越界“场景

约定不等于承诺〃 2022-04-10 04:38 280阅读 0赞

开发环境，碰见一个谓词越界的问题，模拟这条SQL，如下所示，其中A\_ID是表test的外键，并且存在索引，

SELECT 1 FROM test WHERE A_ID = 6052138 AND IS_VALID = 1

这张表的数据量，大约10万，

SQL> select count(*) from test;
     COUNT(*)
    ----------
        99044

查看select 1这条SQL的10053，

***************************************
    BASE STATISTICAL INFORMATION
    ***********************
    Table Stats::
      Table: TEST  Alias: TEST
        #Rows: 265702  #Blks:  13157  AvgRowLen:  180.00  ChainCnt:  0.00
    Index Stats::
      Index: IDX_TEST_01  Col#: 2
        LVLS: 2  #LB: 1777  #DK: 119696  LB/K: 1.00  DB/K: 1.00  CLUF: 118505.00
      Index: IDX_TEST_02  Col#: 3
        LVLS: 2  #LB: 2339  #DK: 381  LB/K: 6.00  DB/K: 272.00  CLUF: 103794.00
      Index: IDX_TEST_03  Col#: 7
        LVLS: 2  #LB: 786  #DK: 2292  LB/K: 1.00  DB/K: 36.00  CLUF: 82804.00
      Index: PK_TEST_ID  Col#: 1
        LVLS: 2  #LB: 1652  #DK: 265702  LB/K: 1.00  DB/K: 1.00  CLUF: 238444.00
    Access path analysis for TEST
    ***************************************
    SINGLE TABLE ACCESS PATH 
      Single Table Cardinality Estimation for TEST[TEST] 
      Column (#2): A_ID(
        AvgLen: 6 NDV: 119696 Nulls: 0 Density: 0.000008 Min: 5586857 Max: 5726449
      Column (#60): IS_VALID(
        AvgLen: 3 NDV: 1 Nulls: 0 Density: 0.000002 Min: 1 Max: 1
        Histogram: Freq  #Bkts: 1  UncompBkts: 10049  EndPtVals: 1
    Using prorated density: 0.000002 of col #2 as selectvity of out-of-range/non-existent value pred
      Table: TEST  Alias: TEST
        Card: Original: 265702.000000  Rounded: 1  Computed: 0.50  Non Adjusted: 0.50
      Access Path: TableScan
        Cost:  3577.48  Resp: 3577.48  Degree: 0
          Cost_io: 3565.00  Cost_cpu: 460365831
          Resp_io: 3565.00  Resp_cpu: 460365831
      Using prorated density: 0.000002 of col #2 as selectvity of out-of-range/non-existent value pred
      Access Path: index (AllEqRange)
        Index: IDX_TEST_01
        resc_io: 4.00  resc_cpu: 30301
        ix_sel: 0.000002  ix_sel_with_filters: 0.000002 
        Cost: 4.00  Resp: 4.00  Degree: 1
      Best:: AccessPath: IndexRange
      Index: IDX_TEST_01
             Cost: 4.00  Degree: 1  Resp: 4.00  Card: 0.50  Bytes: 0
    ***************************************
    ...
    CBRID: TEST @ SEL$1 TableLookup allocation - Failure - : disabled by parameter

看见提示，\#2这列，即A\_ID，对于超出范围的、不存在的值，使用0.000002作为选择率，即这种选择率，是预估的值，不是实际计算的，换句话说，有可能对执行成本的计算，产生偏差，

> Using prorated density: 0.000002 of col \#2 as selectvity of out-of-range/non-existent value pred

我们从这张表，A\_ID字段实际的存储，看下是否存在他所说的，“超出范围”，

SQL> select min(A_ID), max(A_ID) from TEST;
    MIN(A_ID) MAX(A_ID)
    --------- ---------
      6006992   6052756

上述结果展示，A\_ID的取值范围是6006992-6052756，而trace中，标记A\_ID的min和max则是5586857-5726449，因此，这条SQL，出现了传说中的“谓词越界”，

> Min: 5586857 Max: 5726449

trace中的min和max，怎么得来的？他是读取的dba\_tab\_col\_statistics视图，通过换算得到的，

SQL> select table_name, column_name, utl_raw.cast_to_number(low_value) low,
      2  utl_raw.cast_to_number(high_value) hight
      3  from dba_tab_col_statistics
      4  WHERE table_name='TEST' AND column_name='A_ID'
      5  and owner='BISAL';
    TABLE_NAME               COLUMN_NAME                      LOW      HIGHT
    ------------------------ ------------------------------ -------- -----------
    TEST                     A_ID                            5586857   5726449

但是庆幸的是，虽然出现了谓词越界的问题，并没有因为成本值计算偏差，导致CBO选择错误的执行计划，我觉得和这条SQL的谓词条件比较简单，有一定的关系，可选择的执行计划就这两种，

SELECT /*+gather_plan_statistics*/ 1 FROM test WHERE A_ID = 6052138 AND IS_VALID = 1
    
    
    select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
    
    Plan hash value: 1000423460
    -----------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |             |      1 |        |      2 |00:00:00.01 |       6 |
    |*  1 |  TABLE ACCESS BY INDEX ROWID| TEST        |      1 |      1 |      2 |00:00:00.01 |       6 |
    |*  2 |   INDEX RANGE SCAN          | IDX_TEST_01 |      1 |      1 |      2 |00:00:00.01 |       4 |
    -----------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter("IS_VALID"=1)
       2 - access("A_ID"=6052138)

因此这个案例中，虽然出现了“谓词越界”，对COST的计算，会有误差，但并未影响执行计划的选择，如果是一条谓词复杂的SQL，包含多种执行计划的可能，出现“谓词越界”，选错执行计划，形成性能问题，就是大概率了。

解决方法，就是重采集统计信息，以让COST的计算，更接近实际，避免使用默认值，让CBO作出正确选择。