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

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> Oracle CBO选错执行计划的一种场景

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,因此执行计划,如下所示,选择全表扫描,

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

按照上面的计算,选择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

其中有诈。

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

检索表的历史统计信息,

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字段索引扫描, Oracle CBO选错执行计划的一种场景

总结:

  1. 正如dbsnake书中所说,若系统批量导入数据,建议业务使用前,立即采集相关表的统计信息,因为每日22:00,才会进行统计信息自动采集,之间的时间差,就有可能因为统计信息不准,让CBO选错执行计划。
  1. 虽然CBO对于执行计划cost计算,属于机密,但是10053可以间接,让我们了解CBO如何选择,某一个执行计划,再根据表、索引等统计信息,结合来看,有可能就发现一些线索。

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

Oracle CBO选错执行计划的一种场景 Oracle CBO选错执行计划的一种场景
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> Oracle CBO选错执行计划的一种场景


 上一篇
MySQL 主从同步架构中你不知道的“坑” MySQL 主从同步架构中你不知道的“坑”
以下操作征对指定不同步库 1 查看主从的binlog模式 mysql show slave statusG *********************** 1. row ************************* Slave
下一篇 
无备份情况下,绕开ORA-01578错误的脚本 无备份情况下,绕开ORA-01578错误的脚本
今天做测试数据导入的时候,执行imp过程中alert.log提示了错误, Hex dump of (file 10, block 220258) in trace file /DATA/app/ora11g/diag/rdbm