Oracle CBO选错执行计划的一种场景

bisal

发布于 2019-01-30 09:36:39

4700

发布于 2019-01-30 09:36:39

文章被收录于专栏：bisal的个人杂货铺bisal的个人杂货铺

测试人员做应用性能测试，反馈有一条SQL语句，之前执行非常快，现在执行时间，明显慢了。

SQL语句非常简单，根据日期字段，检索当日表中记录数，

select count(*) from user.tbl where cki_date = to_date('2017-10-10','yyyy-mm-dd');

我们执行一次10053，

SQL> alter sessionset events '10053 trace name context forever,level 1'; Session altered. SQL> explain planfor select count(*) from user.tbl where cki_date =to_date('2017-10-10','yyyy-mm-dd'); Explained. SQL> alter sessionset events '10053 trace name context off'; Session altered.

看下对应的trace文件，

*************************************** BASE STATISTICALINFORMATION *********************** Table Stats:: Table: TBL Alias: TBL #Rows: 0 #Blks: 1 AvgRowLen: 0.00 ChainCnt: 0.00 Index Stats:: Index: AIRDATEINDEX Col#: 1 3 LVLS: 3 #LB: 121774 #DK: 25329 LB/K: 4.00 DB/K: 49.00 CLUF: 1258940.00 Index: DATEINDEX Col#: 3 LVLS: 2 #LB: 102501 #DK: 5038 LB/K: 20.00 DB/K: 145.00 CLUF: 730978.00 Index: DESDATEINDEX Col#: 5 3 LVLS: 3 #LB: 131196 #DK: 1216564 LB/K: 1.00 DB/K: 11.00 CLUF: 14149049.00 Index: ORGDATEINDEX Col#: 4 3 LVLS: 3 #LB: 130168 #DK: 1027146 LB/K: 1.00 DB/K: 13.00 CLUF: 13373950.00 Access path analysisfor TBL *************************************** SINGLE TABLE ACCESSPATH Single Table Cardinality Estimation forTBL[TBL] Column (#3): CKI_DATE( AvgLen: 7 NDV: 0 Nulls: 0 Density: 0.000000 Table: TBL Alias: TBL Card: Original: 0.000000 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00 Access Path: TableScan Cost: 2.00 Resp: 2.00 Degree: 0 Cost_io: 2.00 Cost_cpu: 7121 Resp_io: 2.00 Resp_cpu: 7121 Access Path: index (index (FFS)) Index: AIRDATEINDEX resc_io: 32982.00 resc_cpu: 7554023805 ix_sel: 0.000000 ix_sel_with_filters: 1.000000 Access Path: index (FFS) Cost: 33186.78 Resp: 33186.78 Degree: 1 Cost_io: 32982.00 Cost_cpu: 7554023805 Resp_io: 32982.00 Resp_cpu: 7554023805 Access Path: index (index (FFS)) Index: DATEINDEX resc_io: 27762.00 resc_cpu: 7259606781 ix_sel: 0.000000 ix_sel_with_filters: 1.000000 Access Path: index (FFS) Cost: 27958.80 Resp: 27958.80 Degree: 1 Cost_io: 27762.00 Cost_cpu: 7259606781 Resp_io: 27762.00 Resp_cpu: 7259606781 Access Path: index(index (FFS)) Index: DESDATEINDEX resc_io: 35534.00 resc_cpu: 7854852632 ix_sel: 0.000000 ix_sel_with_filters: 1.000000 Access Path: index (FFS) Cost: 35746.93 Resp: 35746.93 Degree: 1 Cost_io: 35534.00 Cost_cpu: 7854852632 Resp_io: 35534.00 Resp_cpu: 7854852632 Access Path: index (index (FFS)) Index: ORGDATEINDEX resc_io: 35255.00 resc_cpu: 7775449582 ix_sel: 0.000000 ix_sel_with_filters: 1.000000 Access Path: index (FFS) Cost: 35465.78 Resp: 35465.78 Degree: 1 Cost_io: 35255.00 Cost_cpu: 7775449582 Resp_io: 35255.00 Resp_cpu: 7775449582 Access Path: index(skip-scan) SS scan sel: 0.010000 SS filter sel: 0.010000 ANDV (#skips): 253.290000 SS io: 1266.450000 vs. table scan io:2.000000 Skip Scan rejected Access Path: index (FullScan) Index: AIRDATEINDEX resc_io: 121777.00 resc_cpu: 8734071799 ix_sel: 1.000000 ix_sel_with_filters: 1.000000 ***** Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 50.00 ***** End Logdef Adjustment ****** Cost: 122013.77 Resp: 122013.77 Degree: 1 Access Path: index (AllEqRange) Index: DATEINDEX resc_io: 1028.00 resc_cpu: 84140440 ix_sel: 0.010000 ix_sel_with_filters: 0.010000 Cost: 1030.28 Resp: 1030.28 Degree: 1 Access Path: index (skip-scan) SS scan sel: 0.010000 SS filter sel: 0.010000 ANDV (#skips): 12165.640000 SS io: 12165.640000 vs. table scan io:2.000000 kip Scan rejected Access Path: index (FullScan) Index: DESDATEINDEX resc_io: 131199.00 resc_cpu: 9076147207 ix_sel: 1.000000 ix_sel_with_filters: 1.000000 ***** Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 50.00 ***** End Logdef Adjustment ****** Cost: 131445.04 Resp: 131445.04 Degree: 1 Access Path: index (skip-scan) SS scan sel: 0.010000 SS filter sel: 0.010000 ANDV (#skips): 10271.460000 SS io: 10271.460000 vs. table scan io:2.000000 Skip Scan rejected Access Path: index (FullScan) Index: ORGDATEINDEX resc_io: 130171.00 resc_cpu: 8984023766 ix_sel: 1.000000 ix_sel_with_filters: 1.000000 ***** Logdef predicate Adjustment ****** Final IO cst 0.00 , CPU cst 50.00 ***** End Logdef Adjustment ****** Cost: 130414.54 Resp: 130414.54 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes****** ******** Begin indexjoin costing ******** ****** tryingbitmap/domain indexes ****** Access Path: index (AllEqRange) Index: DATEINDEX resc_io: 1028.00 resc_cpu: 84140440 ix_sel: 0.010000 ix_sel_with_filters: 0.010000 Cost: 1030.28 Resp: 1030.28 Degree: 0 Bitmap nodes: Used DATEINDEX Cost = 1287.851144, sel = 0.010000 ****** finished trying bitmap/domain indexes****** ******** End index joincosting ******** Best:: AccessPath: TableScan Cost: 2.00 Degree: 1 Resp: 2.00 Card: 0.00 Bytes: 0 Final cost for queryblock SEL$1 (#0) - All Rows Plan: Best join order: 1 Cost: 2.0002 Degree: 1 Card: 1.0000 Bytes: 9 Resc: 2.0002 Resc_io: 2.0000 Resc_cpu: 7121 Resp: 2.0002 Resp_io: 2.0000 Resc_cpu: 7121

Oracle CBO计算了这条SQL各种执行计划，认为全表扫描效率最高，成本cost值是2，因此执行计划，如下所示，选择全表扫描，

按照上面的计算，选择TABLE ACCESS FULL无可厚非，可为何实际执行，时间非常的久？

看下表的记录数，有千万级的数据！

SQL> select count(*) from user.tbl; COUNT(*) -------------------- 39751845

查看相邻测试日期的数据，每日1万左右记录，

SQL> SELECT cki_date,COUNT(*) FROM tbl GROUP BY cki_date ORDER BY cki_date DESC; 1 2017/10/18 14378 2 2017/10/17 14213 3 2017/10/16 14127 4 2017/10/15 13841 5 2017/10/14 14436 6 2017/10/13 14745 7 2017/10/12 14389 8 2017/10/11 14616 9 2017/10/10 14671 10 2017/10/9 14953

其中有诈。

检索表的历史统计信息，

SQL> alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS'; Session altered. SQL> select a.savtime, a.rowcnt, a.blkcnt, a.avgrln, a.analyzetime from sys.wri$_optstat_tab_history a, dba_objects b where a.obj#=b.object_id and b.object_name='TBL'; SAVTIME ROWCNT BLKCNT AVGRLN ANALYZETIME ---------------------------------------- ---------- ---------- ------------------ 2018-01-10 19:01:37 39209893 516232 87 01-JAN-18 2018-01-10 22:05:50 0 1 0 10-JAN-18

测试时间是1月11日，前一天22:00统计信息采集任务，就认为表中记录数为0，即是一张空表，但是由于索引统计信息正确，显示几千万数据，自然CBO认为全表扫描效率，要比索引扫描的效率低。

了解了下，由于做性能测试，10日曾经清理了表数据，11日进行了批量数据导入，但是统计信息，未进行手工采集，因此用了前一天自动采集的，导致统计信息，和表的实际信息不符，为CBO提供了错误的信息，因而选错了执行计划。

dbsnake的书中已经指出，每日22:00定时采集的统计信息，使用如下脚本进行创建，

$ORACLE_HOME/rdbms/admin/catmwin.sql

如下是11g脚本中，自动统计信息采集的内容，

-- Create gather stats program. BEGIN dbms_scheduler.create_program( program_name=>'gather_stats_prog', program_type=>'STORED_PROCEDURE', program_action=>'dbms_stats.gather_database_stats_job_proc', number_of_arguments=>0, enabled=>TRUE, comments =>'Oracle defined automatic optimizer statistics collection program'); EXCEPTION when others then if sqlcode = -27477 then NULL; else raise; end if; END; /

一个名称为gather_stats_prog的program，调用了存储过程dbms_stats.gather_database_stats_job_proc，使用fyunwrap工具，破解存储过程的源代码，比较晦涩，大致看出些信息，要是理解有错误，欢迎纠正，

... // 执行以下SQL，选择符合条件，需要使用自动统计信息，进行采集的对象， SELECT /*+ leading(ST o u) */ ST.OBJ# OBJNUM, U.NAME OWNER, O.NAME OBJNAME, O.SUBNAME SUBNAME, ST.TYPE# TYPE#, ST.BO# BO#, ST.FLAGS, ST.OSIZE FROM OBJ$ O, USER$ U, (SELECT /*+ no_merge */ * FROM (SELECT /*+ dynamic_sampling(s 4) dynamic_sampling_est_cdn(s) */ S.*, ROW_NUMBER() OVER (ORDER BY DECODE(TYPE#, 1,2,2,1, 19,3,20,4, 34,5,35,6,7), STALENESS,OSIZE,OBJ#) RN FROM STATS_TARGET$ S WHERE S.STATUS = DSC_AUTO_STATS_PENDING) // 此处DSC_AUTO_STATS_PENDING默认值为0 WHERE RN <= DSC_TARGET_LIST_BATCH_SIZE) ST // 此处DSC_TARGET_LIST_BATCH_SIZE默认值为9000 WHERE ST.OBJ# = O.OBJ# AND O.OWNER# = U.USER# ORDER BY DECODE(ST.TYPE#, 1,2,2,1, 19,3,20,4, 34,5,35,6,7), ST.STALENESS, ST.OSIZE, ST.OBJ#; // 结果集存储于，一个名称为TARGETTAB的自定义类型， TYPE TARGETREC IS RECORD ( OBJNUM NUMBER, OWNER VARCHAR2(30), OBJNAME VARCHAR2(30), SUBNAME VARCHAR2(30), TYPE# PLS_INTEGER, BO# NUMBER, FLAGS PLS_INTEGER, OSIZE NUMBER); TYPE TARGETTAB IS TABLE OF TARGETREC; // 遍历检索结果， FOR I IN 1..TARGET.COUNT LOOP ... // 根据TYPE#，判断此对象类型，是表、索引、分区、子分区这些类型。对于索引，用了如下函数，如果TYPE#是1、20、35则判断为索引， FUNCTION IS_OBJTYPE_INDEX(OBJTYPE PLS_INTEGER) RETURN BOOLEAN IS BEGIN RETURN OBJTYPE IN (1, 20, 35); END IS_OBJTYPE_INDEX; // 对于索引，会使用如下存储过程，执行统计信息的采集， GATHER_INDEX_STATS_AUX('"' || TARGET(I).OWNER || '"', DBMS_STATS_INTERNAL.DQ(TARGET(I).OBJNAME), SUBNAME, DEFAULT_ESTIMATE_PERCENT, NULL, NULL, NULL, TO_DEGREE_TYPE(GET_PARAM('DEGREE')), TMPGRAN, DEFAULT_GRANULARITY, TO_NO_INVALIDATE_TYPE(GET_PARAM('NO_INVALIDATE')), 'DATA', START_TIME, TRUE, FALSE, FALSE); // 对于表则会使用，如下存储过程，进行统计信息的自动采集，注意其中是否级联采集索引，使用的是DEFAULT_CASCADE，默认应为false(暂时未找着出处)， GATHER_TABLE_STATS_AUX(DBMS_STATS_INTERNAL.DQ(TARGET(I).OWNER), DBMS_STATS_INTERNAL.DQ(TARGET(I).OBJNAME), SUBNAME, DEFAULT_ESTIMATE_PERCENT, FALSE, DEFAULT_METHOD_OPT, TO_DEGREE_TYPE(GET_PARAM('DEGREE')), TMPGRAN, DEFAULT_GRANULARITY, DEFAULT_CASCADE, NULL, NULL, NULL, TO_NO_INVALIDATE_TYPE(GET_PARAM('NO_INVALIDATE')), FALSE, DSC_NON_FXT, 'DATA', START_TIME, TRUE, CMS_ONLY,FALSE, DSC_SEQ, NULL, JOBSTAB, NULL, FALSE);

既然表的统计信息不准，手工采集表和索引统计信息，

SQL> exec dbms_stats.gather_table_stats('USER','TBL',cascade=>true); PL/SQL proceduresuccessfully completed.

此时统计信息，已经是准确的了，

SQL> select a.savtime, a.rowcnt, a.blkcnt, a.avgrln, a.analyzetime from sys.wri$_optstat_tab_history a, dba_objects b where a.obj#=b.object_id and b.object_name='TBL'; SAVTIME ROWCNT BLKCNT AVGRLN ANALYZETIME ---------------------------------------- ---------- ---------- ------------------ 2018-01-10 19:01:37 39209893 516232 87 01-JAN-18 2018-01-10 22:05:50 0 1 0 10-JAN-18 2018-01-11 10:10:35 39722903 524416 87 11-JAN-18

再对如下语句，执行10053，

SQL> explain planfor select count(*) from user.tbl where cki_date =to_date('2017-10-10','yyyy-mm-dd'); Explained.

检索相应的trace文件，

*************************************** BASE STATISTICALINFORMATION *********************** Table Stats:: Table: TBL_CSTD Alias: TBL_CSTD #Rows: 39751845 #Blks: 524416 AvgRowLen: 87.00 ChainCnt: 0.00 Index Stats:: Index: AIRDATEINDEX Col#: 1 3 LVLS: 3 #LB: 123176 #DK: 287186 LB/K: 1.00 DB/K: 4.00 CLUF: 1323872.00 Index: DATEINDEX Col#: 3 LVLS: 2 #LB: 111409 #DK: 5040 LB/K: 22.00 DB/K: 154.00 CLUF: 779959.00 Index: DESDATEINDEX Col#: 5 3 LVLS: 3 #LB: 125310 #DK: 1259074 LB/K: 1.00 DB/K: 10.00 CLUF: 13360966.00 Index: ORGDATEINDEX Col#: 4 3 LVLS: 3 #LB: 131116 #DK: 990166 LB/K: 1.00 DB/K: 13.00 CLUF: 13438679.00 Access path analysisfor TBL_CSTD *************************************** SINGLE TABLE ACCESSPATH Single Table Cardinality Estimation forTBL_CSTD[TBL_CSTD] Column (#3): CKI_DATE( AvgLen: 8 NDV: 5040 Nulls: 0 Density:0.000198 Min: 2453006 Max: 2458045 Table: TBL_CSTD Alias: TBL_CSTD Card: Original: 39751845.000000 Rounded: 7887 Computed: 7887.27 Non Adjusted:7887.27 Access Path: TableScan Cost: 142390.87 Resp: 142390.87 Degree: 0 Cost_io: 142031.00 Cost_cpu: 13275039879 Resp_io: 142031.00 Resp_cpu: 13275039879 Access Path: index (index (FFS)) Index: AIRDATEINDEX resc_io: 33362.00 resc_cpu: 7656349173 ix_sel: 0.000000 ix_sel_with_filters: 1.000000 Access Path: index (FFS) Cost: 33569.55 Resp: 33569.55 Degree: 1 Cost_io: 33362.00 Cost_cpu: 7656349173 Resp_io: 33362.00 Resp_cpu: 7656349173 Access Path: index (index (FFS)) Index: DATEINDEX resc_io: 30175.00 resc_cpu: 7883992719 ix_sel: 0.000000 ix_sel_with_filters: 1.000000 Access Path: index (FFS) Cost: 30388.72 Resp: 30388.72 Degree: 1 Cost_io: 30175.00 Cost_cpu: 7883992719 Resp_io: 30175.00 Resp_cpu: 7883992719 Access Path: index (index (FFS)) Index: DESDATEINDEX resc_io: 33940.00 resc_cpu: 7492760386 ix_sel: 0.000000 ix_sel_with_filters: 1.000000 Access Path: index (FFS) Cost: 34143.12 Resp: 34143.12 Degree: 1 Cost_io: 33940.00 Cost_cpu: 7492760386 Resp_io: 33940.00 Resp_cpu: 7492760386 Access Path: index (index (FFS)) Index: ORGDATEINDEX resc_io: 35512.00 resc_cpu: 7852113037 ix_sel: 0.000000 ix_sel_with_filters: 1.000000 Access Path: index (FFS) Cost: 35724.86 Resp: 35724.86 Degree: 1 Cost_io: 35512.00 Cost_cpu: 7852113037 Resp_io: 35512.00 Resp_cpu: 7852113037 Access Path: index(skip-scan) SS scan sel: 0.000198 SS filter sel: 0.000198 ANDV (#skips): 193.000000 SS io: 193.000000 vs. table scan io:142031.000000 Skip Scan chosen Access Path: index (SkipScan) Index: AIRDATEINDEX resc_io: 196.00 resc_cpu: 2978402 ix_sel: 0.000198 ix_sel_with_filters: 0.000198 Cost: 196.08 Resp: 196.08 Degree: 1 Access Path: index (AllEqRange) Index: DATEINDEX resc_io: 25.00 resc_cpu: 1833236 ix_sel: 0.000198 ix_sel_with_filters: 0.000198 Cost: 25.05 Resp: 25.05 Degree: 1 Access Path: index (skip-scan) SS scan sel: 0.000198 SS filter sel: 0.000198 ANDV (#skips): 582.000000 SS io: 582.000000 vs. table scan io:142031.000000 Skip Scan chosen Access Path: index (SkipScan) Index: DESDATEINDEX resc_io: 585.00 resc_cpu: 5706842 ix_sel: 0.000198 ix_sel_with_filters: 0.000198 Cost: 585.15 Resp: 585.15 Degree: 1 Access Path: index(skip-scan) SS scan sel: 0.000198 SS filter sel: 0.000198 ANDV (#skips): 421.000000 SS io: 421.000000 vs. table scan io:142031.000000 Skip Scan chosen Access Path: index (SkipScan) Index: ORGDATEINDEX resc_io: 424.00 resc_cpu: 4634491 ix_sel: 0.000198 ix_sel_with_filters: 0.000198 Cost: 424.13 Resp: 424.13 Degree: 1 ****** trying bitmap/domain indexes ****** ****** finished trying bitmap/domain indexes****** Best:: AccessPath: IndexRange Index: DATEINDEX Cost: 25.05 Degree: 1 Resp: 25.05 Card: 7887.27 Bytes: 0 Final cost for queryblock SEL$1 (#0) - All Rows Plan: Best join order: 1 Cost: 25.0497 Degree: 1 Card: 7887.0000 Bytes: 63096 Resc: 25.0497 Resc_io: 25.0000 Resc_cpu:1833236 Resp: 25.0497 Resp_io: 25.0000 Resc_cpu:1833236

此时选择的最佳执行计划，是cki_date字段索引扫描，