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

测试人员做应用性能测试，反馈有一条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.

Session altered.

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

 

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

BASE STATISTICALINFORMATION

Table Stats::

    #Rows: 0 #Blks:  1  AvgRowLen: 0.00  ChainCnt:  0.00

  Index: AIRDATEINDEX  Col#: 1 3

  Index: DATEINDEX  Col#: 3

  Index: DESDATEINDEX  Col#: 5 3

  Index: ORGDATEINDEX  Col#: 4 3

Access path analysisfor TBL

SINGLE TABLE ACCESSPATH

  Column (#3): CKI_DATE(

  Table: TBL  Alias: TBL

  Access Path: TableScan

      Cost_io: 2.00  Cost_cpu: 7121

  Access Path: index (index (FFS))

    resc_io: 32982.00  resc_cpu: 7554023805

  Access Path: index (FFS)

      Cost_io: 32982.00  Cost_cpu: 7554023805

  Access Path: index (index (FFS))

    resc_io: 27762.00  resc_cpu: 7259606781

  Access Path: index (FFS)

      Cost_io: 27762.00  Cost_cpu: 7259606781

Access Path: index(index (FFS))

    resc_io: 35534.00  resc_cpu: 7854852632

  Access Path: index (FFS)

      Cost_io: 35534.00  Cost_cpu: 7854852632

  Access Path: index (index (FFS))

    resc_io: 35255.00  resc_cpu: 7775449582

  Access Path: index (FFS)

      Cost_io: 35255.00  Cost_cpu: 7775449582

 

    SS scan sel: 0.010000  SS filter sel: 0.010000  ANDV (#skips): 253.290000

    Skip Scan rejected

    Index: AIRDATEINDEX

    ix_sel: 1.000000  ix_sel_with_filters: 1.000000

 Final IO cst 0.00 , CPU cst 50.00

    Cost: 122013.77  Resp: 122013.77  Degree: 1

    Index: DATEINDEX

    ix_sel: 0.010000  ix_sel_with_filters: 0.010000

 

    SS scan sel: 0.010000  SS filter sel: 0.010000  ANDV (#skips): 12165.640000

kip Scan rejected

    Index: DESDATEINDEX

    ix_sel: 1.000000  ix_sel_with_filters: 1.000000

 Final IO cst 0.00 , CPU cst 50.00

    Cost: 131445.04  Resp: 131445.04  Degree: 1

  Access Path: index (skip-scan)

    SS io: 10271.460000 vs. table scan io:2.000000

  Access Path: index (FullScan)

    resc_io: 130171.00  resc_cpu: 8984023766

 ***** Logdef predicate Adjustment ******

 ***** End Logdef Adjustment ******

  ****** trying bitmap/domain indexes ******

******** Begin indexjoin costing ********

  Access Path: index (AllEqRange)

    resc_io: 1028.00  resc_cpu: 84140440

    Cost: 1030.28  Resp: 1030.28 Degree: 0

    Used DATEINDEX

  ****** finished trying bitmap/domain indexes******

  Best:: AccessPath: TableScan

 

  Best join order: 1

  Resc: 2.0002 Resc_io: 2.0000  Resc_cpu: 7121

 

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

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

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

SQL select count() from user.tbl;
  COUNT()

---

  39751845

  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

1        2017/10/18        14378

3        2017/10/16        14127

5        2017/10/14        14436

7        2017/10/12        14389

9        2017/10/10        14671

其中有诈。

检索表的历史统计信息，

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

Session altered.

SQL select a.savtime, a.rowcnt, a.blkcnt, a.avgrln, a.analyzetime  

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，选择符合条件，需要使用自动统计信息，进行采集的对象，

// 此处DSC_AUTO_STATS_PENDING默认值为0

// 此处DSC_TARGET_LIST_BATCH_SIZE默认值为9000

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;

// 遍历检索结果，

…

// 根据TYPE#，判断此对象类型，是表、索引、分区、子分区这些类型。对于索引，用了如下函数，如果TYPE#是1、20、35则判断为索引，

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(暂时未找着出处)，

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

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

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

from sys.wri$_optstat_tab_history a, dba_objects b
where a.obj#=b.object_id and b.object_name=’TBL’;

再对如下语句，执行10053，

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

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

BASE STATISTICALINFORMATION

Table Stats::

    #Rows: 39751845  #Blks: 524416  AvgRowLen:  87.00 ChainCnt:  0.00

  Index: AIRDATEINDEX  Col#: 1 3

  Index: DATEINDEX  Col#: 3

  Index: DESDATEINDEX  Col#: 5 3

  Index: ORGDATEINDEX  Col#: 4 3

Access path analysisfor TBL_CSTD

SINGLE TABLE ACCESSPATH

  Column (#3): CKI_DATE(

  Table: TBL_CSTD  Alias: TBL_CSTD

  Access Path: TableScan

      Cost_io: 142031.00  Cost_cpu: 13275039879

  Access Path: index (index (FFS))

    resc_io: 33362.00  resc_cpu: 7656349173

  Access Path: index (FFS)

      Cost_io: 33362.00  Cost_cpu: 7656349173

  Access Path: index (index (FFS))

    resc_io: 30175.00  resc_cpu: 7883992719

  Access Path: index (FFS)

      Cost_io: 30175.00  Cost_cpu: 7883992719

  Access Path: index (index (FFS))

    resc_io: 33940.00  resc_cpu: 7492760386

  Access Path: index (FFS)

      Cost_io: 33940.00  Cost_cpu: 7492760386

  Access Path: index (index (FFS))

    resc_io: 35512.00  resc_cpu: 7852113037

  Access Path: index (FFS)

      Cost_io: 35512.00  Cost_cpu: 7852113037

Access Path: index(skip-scan)

    SS io: 193.000000 vs. table scan io:142031.000000

  Access Path: index (SkipScan)

    resc_io: 196.00  resc_cpu: 2978402

    Cost: 196.08  Resp: 196.08 Degree: 1

    Index: DATEINDEX

    ix_sel: 0.000198  ix_sel_with_filters: 0.000198

 

    SS scan sel: 0.000198  SS filter sel: 0.000198  ANDV (#skips): 582.000000

    Skip Scan chosen

    Index: DESDATEINDEX

    ix_sel: 0.000198  ix_sel_with_filters: 0.000198

Access Path: index(skip-scan)

    SS io: 421.000000 vs. table scan io:142031.000000

  Access Path: index (SkipScan)

    resc_io: 424.00  resc_cpu: 4634491

    Cost: 424.13  Resp: 424.13 Degree: 1

  ****** finished trying bitmap/domain indexes******

  Index: DATEINDEX

 

  Best join order: 1

  Resc: 25.0497 Resc_io: 25.0000  Resc_cpu:1833236

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

总结：

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

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

如果您觉得本文有帮助，欢迎朋友们关注转发：bisal的个人杂货铺，