ORACLE , 通过10053分析"谓词越界"引起执行计划不准确

有客户问起在看10053 trace时 其中的
Using prorated density : xxx as selectivity of out-of-range value pred 这句怎么理解.

简单说: 发现谓词越界, cost为估算;
即: 在优化器尝试计算COST时,发现where条件中某列给定的值不在 统计信息中记录的值范围内(dba_tab_col_statistics的low value和Highvalue之间), 因为只能估算cost. 这时就有可能出现偏差. 如果这个偏差很大,就会导到执行计划不准确;

做了个示例,来模拟"谓词越界"

---session A

SQL> create  table tbl_tst as select  * from dba_objects;


SQL> create index idx_tbl_tst on tbl_tst (object_id);

Index created.

#这里删除柱状图(个人不喜欢直方图)
SQL> exec DBMS_STATS.DELETE_TABLE_STATS(TABNAME=>'TBL_TST',ownname=>'JASON',no_invalidate=>FALSE,cascade_indexes=>TRUE,cascade_parts=>TRUE) ;

#重新收集统计信息
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'JASON',tabname=>'TBL_TST',estimate_percent=>0,method_opt=>'FOR ALL COLUMNS SIZE 1',no_invalidate=>false,cascade=>true,degree => 10);

#表中object_id 的实际最大小值 
SQL> select min(object_id),max(object_id) from tbl_tst;

MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
             2          97632

#查看在统计信息中记录的OBJECT最大小值 
SQL> col COLUMN_NAME for a15
SQL> select COLUMN_NAME,UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) MIN,UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) MAX,HISTOGRAM,num_buckets  from dba_tab_col_statistics cs
  2   where owner='JASON' AND table_name='TBL_TST' AND COLUMN_NAME='OBJECT_ID'; 

COLUMN_NAME            MIN        MAX HISTOGRAM                                     NUM_BUCKETS
--------------- ---------- ---------- --------------------------------------------- -----------
OBJECT_ID                2      97632 NONE                                                    1

SQL> 

SQL> select sid from v$mystat where rownum=1;

       SID
----------
       141

SQL> select spid from v$process where addr in (select paddr from v$session where sid=141);

SPID
------------------------------------------------------------------------
13964

--session B

SQL> conn / as sysdba
SQL> oradebug setospid 13964
Oracle pid: 19, Unix process pid: 13964, image: oracle@myzdb100 (TNS V1-V3)
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/myzdb/myzdb/trace/myzdb_ora_13964.trc
SQL> oradebug event 10053 trace name context forever, level 1
Statement processed.
SQL> 

--session A

SQL> select  object_name from tbl_tst where object_id=99999;

no rows selected

--session B

SQL> oradebug event 10053 trace name context off
Statement processed.

vi /u01/app/oracle/diag/rdbms/myzdb/myzdb/trace/myzdb_ora_13964.trc
....
Access path analysis for TBL_TST
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for TBL_TST[TBL_TST]
  Column (#4): OBJECT_ID(
    AvgLen: 5 NDV: 87152 Nulls: 0 Density: 0.000011 Min: 2 Max: 97632
# NDV 即number of distinct values 
# Density 密度值,越低越好, 超过0.2 的话列上的索引意义不大了.
# 这里看到OBJECT_ID 最小2,最大97632,
  Using prorated density: 0.000011 of col #4 as selectvity of out-of-range/non-existent value pred
#这里看到发现 object_id=99999 超出了 统计信息中的值,按照0.000011 的密度值来计算cost
  Table: TBL_TST  Alias: TBL_TST
    Card: Original: 87152.000000  Rounded: 1  Computed: 0.98  Non Adjusted: 0.98
#计划1: 走全表, COST算下来346
  Access Path: TableScan
    Cost:  346.86  Resp: 346.86  Degree: 0
      Cost_io: 346.00  Cost_cpu: 31717992
      Resp_io: 346.00  Resp_cpu: 31717992
  Using prorated density: 0.000011 of col #4 as selectvity of out-of-range/non-existent value pred
#计划2: 走索引, COST算下来2
  Access Path: index (AllEqRange)
    Index: IDX_TBL_TST
    resc_io: 2.00  resc_cpu: 15503
    ix_sel: 0.000011  ix_sel_with_filters: 0.000011
    Cost: 2.00  Resp: 2.00  Degree: 1
#因此best计划选择 走索引
  Best:: AccessPath: IndexRange
  Index: IDX_TBL_TST
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.98  Bytes: 0

***************************************
....

可以看出 ,其实一般情况下, 即使越界了, 估算也是很准确的.