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

旧城等待， 2022-04-08 10:15 237阅读 0赞

测试人员做应用性能测试，反馈有一条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，因此执行计划，如下所示，选择全表扫描，

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jpc2Fs_size_16_color_FFFFFF_t_70][]

按照上面的计算，选择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字段索引扫描，

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jpc2Fs_size_16_color_FFFFFF_t_70 1][]

总结：

1. 正如dbsnake书中所说，若系统批量导入数据，建议业务使用前，立即采集相关表的统计信息，因为每日22:00，才会进行统计信息自动采集，之间的时间差，就有可能因为统计信息不准，让CBO选错执行计划。

2. 虽然CBO对于执行计划cost计算，属于机密，但是10053可以间接，让我们了解CBO如何选择，某一个执行计划，再根据表、索引等统计信息，结合来看，有可能就发现一些线索。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jpc2Fs_size_16_color_FFFFFF_t_70]: /images/20220406/104489452bff406c96d4ccfd1c3158ef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Jpc2Fs_size_16_color_FFFFFF_t_70 1]: /images/20220406/fa315cceaaa947ca898fe2985da15436.png