ITPub博客

首页 > 数据库 > Oracle > Oracle CBO选错执行计划的一种场景

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

原创 Oracle 作者:bisal 时间:2018-12-10 13:08:15 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,因此执行计划,如下所示,选择全表扫描,


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



总结:

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

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7192724/viewspace-2284690/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE,10g/11g OCP,11g OCM,国内首批Oracle YEP成员(Oracle Young Expert Program,Oracle用户组年轻专家项目),EXIN DevOps Master,Oracle爱好者,微信公众号:bisal的个人杂货铺

注册时间:2013-07-26

  • 博文量
    340
  • 访问量
    2623854