ORACLE , 通过10053分析"谓词越界"引起执行计划不准确
有客户问起在看10053 trace时 其中的
Using prorated density : xxx as selectivity of out-of-range value pred 这句怎么理解.
IMG_5769.PNG
简单说: 发现谓词越界, 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
***************************************
....
可以看出 ,其实一般情况下, 即使越界了, 估算也是很准确的.
