ITPub博客

首页 > Linux操作系统 > Linux操作系统 > date列统计信息陈旧导致sql没有选择最优执行计划

date列统计信息陈旧导致sql没有选择最优执行计划

原创 Linux操作系统 作者:myownstars 时间:2011-05-11 21:06:35 0 删除 编辑

开发人员报告一条sql运行的比较慢,请求优化,该sql结构如下
select count(s.id) as orderNum,sum(s.amount) as amount
         from  kk s
        inner join  ff af
           on s.id = af.addrr_id
        where s.create_time > sysdate -60
          and af.t_num =10612711;
首先说一下这两个表大致情况,kk有1千万条记录,ff有两千万;
kk上的id为主键,create_time建有索引;ff上的addrr_id和t_num分别有索引
先查看一下执行计划
该sql 先访问kk上的create_time字段,然后跟 ff的结果集做nested loop;但是outer table的结果集候选记录太多(60天以内的所有记录),导致代价过高;
理想情况应该是先通过t_num找出ff的数据集,然后根据s.id = af.addrr_id条件访问kk的id字段(unique index scan),这样以来整个sql的执行代价应该会变小很多

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                         |                             |     1 |    30 |    10   (0)| 00:00:01 |
|   1 |  KKRT AGGREGATE                                            |                                                          |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID               | FF                                                    |     1 |    11 |     3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                                               |                                                         |     2 |    60 |    10   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID              | KK                                                  |     2 |    38 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                                     | IDX_KK_CREATE_DATE          |     2 |       |     3   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                                      | IDX_ADDRR_ID                          |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("AF"."T_NUM"=10612711)
   5 - access("S"."CREATE_TIME">SYSDATE@!-60)
   6 - access("S"."ID"="AF"."ADDRR_ID")

Statistics
----------------------------------------------------------
        493  recursive calls
          0  db block gets
    1535934  consistent gets
          0  physical reads
       1036  redo size
        579  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         12  kkrts (memory)
          0  kkrts (disk)
          1  rows processed


尝试添加hint,

SQL> select /*+ index(s,pk7) */ count(s.id) as orderNum,sum(s.amount) as amount
  2           from  kk s
  3          inner join  ff af
  4             on s.id = af.addrr_id
  5          where s.create_time > sysdate -60
  6            and af.t_num =10612711;

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     1 |    30 |   218   (0)| 00:00:03 |
|   1 |  KKRT AGGREGATE               |                     |     1 |    30 |            |          |
|   2 |   NESTED LOOPS                |                     |     2 |    60 |   218   (0)| 00:00:03 |
|   3 |    TABLE ACCESS BY INDEX ROWID| FF                  |   143 |  1573 |    57   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_T_NUM           |   148 |       |     3   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| KK                  |     1 |    19 |     2   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN         | PK7                 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("AF"."T_NUM"=10612711)
   5 - filter("S"."CREATE_TIME">SYSDATE@!-60)
   6 - access("S"."ID"="AF"."ADDRR_ID")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        579  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  kkrts (memory)
          0  kkrts (disk)
          1  rows processed

仅从显示的信息来看,采用pk7索引扫描性能提升的简直不可思议,但是运行的时候两条语句的执行时间相差并不是太大,尽管后者更快一点。
这里有两点疑问
1、采用pk7之后的代价小的有点离谱,且运行时间并没有比不加hint之前提升多少
2、为什么数据库自己不能自动访问到pk7,逻辑备库上同样的sql可以自动采用pk7
查询表ff 上t_num=10612711的候选记录,结果为0,则第一个疑问可以得到解释,当添加hint后,sql首先查找FF里的候选记录,返回0行,以此做nest loop,逻辑读肯定非常之低
SQL> select count(*) from ff where t_num =10612711;

  COUNT(*)
----------
         0

对于第二个疑问,可以通过10053事件找出答案
以下是摘录
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#24): CREATE_TIME(DATE)
    AvgLen: 8.00 NDV: 98285 Nulls: 0 Density: 1.0174e-05 Min: 2454786 Max: 2455582
  Using prorated density: 9.5809e-06 of col #24 as selectivity of out-of-range value pred
  Table: KK  Alias: S
    Card: Original: 241910  Rounded: 2  Computed: 2.32  Non Adjusted: 2.32
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  6475.05  Resp: 6475.05  Degree: 0
      Cost_io: 6424.00  Cost_cpu: 437390807
      Resp_io: 6424.00  Resp_cpu: 437390807
  Using prorated density: 9.5809e-06 of col #24 as selectivity of out-of-range value pred
  Access Path: index (RangeScan)
    Index: IDX_KK_CREATE_DATE
    resc_io: 4.00  resc_cpu: 30916
    ix_sel: 9.5809e-06  ix_sel_with_filters: 9.5809e-06
    Cost: 4.00  Resp: 4.00  Degree: 1
  Best:: AccessPath: IndexRange  Index: IDX_KK_CREATE_DATE
         Cost: 4.00  Degree: 1  Resp: 4.00  Card: 2.32  Bytes: 0  --kk表的最佳访问路径为索引IDX_KK_CREATE_DATE,且cost为4,有点太低了,但是 根据公式cost=blevel +ceiling(leaf_blocks * effective index selectivity) --+ceiling(clustering_factor * effective table selectivity) = 2 + (2406*9.5809e-06) +(95454*9.5809e-06) =4,确实结果为4

***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#3): T_NUM(NUMBER)
    AvgLen: 5.00 NDV: 105 Nulls: 0 Density: 9.6246e-05 Min: 714 Max: 8215831
    Histogram: Freq  #Bkts: 104  UncompBkts: 5195  EndPtVals: 104
  Using prorated density: 6.8165e-05 of col #3 as selectivity of out-of-range value pred
  Table: FF  Alias: AF
    Card: Original: 2029216  Rounded: 138  Computed: 138.32  Non Adjusted: 138.32
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  31755.82  Resp: 31755.82  Degree: 0
      Cost_io: 31602.00  Cost_cpu: 1317927216
      Resp_io: 31602.00  Resp_cpu: 1317927216
  Using prorated density: 6.8165e-05 of col #3 as selectivity of out-of-range value pred
  Access Path: index (AllEqRange)
    Index: IDX_AOI_T_NUM
    resc_io: 53.00  resc_cpu: 431646
    ix_sel: 6.8165e-05  ix_sel_with_filters: 6.8165e-05
    Cost: 53.05  Resp: 53.05  Degree: 1
  Best:: AccessPath: IndexRange  Index: IDX_AOI_T_NUM
         Cost: 53.05  Degree: 1  Resp: 53.05  Card: 138.32  Bytes: 0--ff表的最佳访问路径为索引IDX_AOI_T_NUM,
cost为53.05
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  KK[S]#0  FF[AF]#1--oracle在对两表的候选数据做join的时候,选择了kk表作为driving table,且kk表的访问路径为索引IDX_KK_CREATE_DATE,这个就能很好的解释为什么不会用到pk7
***************
Now joining: FF[AF]#1
***************
NL Join
  Outer table: Card: 2.32  Cost: 4.00  Resp: 4.00  Degree: 1  Bytes: 19
  Inner table: FF  Alias: AF
  Access Path: TableScan
    NL Join:  Cost: 63513.65  Resp: 63513.65  Degree: 1
      Cost_io: 63206.00  Cost_cpu: 2635885348
      Resp_io: 63206.00  Resp_cpu: 2635885348
  Access Path: index (AllEqJoinGuess)
    Index: IDX_ADDRR_ID
    resc_io: 3.00  resc_cpu: 23045
    ix_sel: 4.9348e-07  ix_sel_with_filters: 4.9348e-07
    NL Join: Cost: 10.01  Resp: 10.01  Degree: 1
      Cost_io: 10.00  Cost_cpu: 77005
      Resp_io: 10.00  Resp_cpu: 77005
  Using prorated density: 6.8165e-05 of col #3 as selectivity of out-of-range value pred
  Access Path: index (AllEqJoin)
    Index: IDX_AOI_T_NUM
    resc_io: 52.00  resc_cpu: 425375
    ix_sel: 6.8165e-05  ix_sel_with_filters: 6.8165e-05
    NL Join: Cost: 108.10  Resp: 108.10  Degree: 1
      Cost_io: 108.00  Cost_cpu: 881666
      Resp_io: 108.00  Resp_cpu: 881666
  Best NL cost: 10.01
          resc: 10.01 resc_io: 10.00 resc_cpu: 77005
          resp: 10.01 resp_io: 10.00 resp_cpu: 77005


sql选择非最优执行计划的原因为:CBO错误估计了kk表上IDX_KK_CREATE_DATE的访问代价;
其中 Using prorated density: 9.5809e-06 of col #24 as selectivity of out-of-range value pred是关键,
当where条件中的值超出了统计信息里的该列的上下限范围后,oracle将采用一个估计的selectivity,查看表kk的统计信息
SQL> select to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from user_tab_columns where table_name ='KK' and column_name ='CREATE_TIME';

TO_CHAR(LAST_ANALYZED,'YYYY-MM
------------------------------
2011-01-19 15:43:15
 最后1月19号收集的,而现在是11年5月11号,中间差了100多天,当使用create_time > sysdate -60时,无怪乎会报告 as selectivity of out-of-range value pred。

修改一下查询条件,将create_time > sysdate-60 改为sysdate -160,重新用10053跟踪一把,以下是摘录


SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#24): CREATE_TIME(DATE)
    AvgLen: 8.00 NDV: 98285 Nulls: 0 Density: 1.0174e-05 Min: 2454786 Max: 2455582
  Table: KK  Alias: S
    Card: Original: 241910  Rounded: 14586  Computed: 14585.52  Non Adjusted: 14585.52
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  6475.05  Resp: 6475.05  Degree: 0
      Cost_io: 6424.00  Cost_cpu: 437390807
      Resp_io: 6424.00  Resp_cpu: 437390807
  Access Path: index (RangeScan)
    Index: IDX_KK_CREATE_DATE
    resc_io: 6033.00  resc_cpu: 54886848
    ix_sel: 0.060293  ix_sel_with_filters: 0.060293
    Cost: 6039.41  Resp: 6039.41  Degree: 1
  Best:: AccessPath: IndexRange  Index: IDX_KK_CREATE_DATE
         Cost: 6039.41  Degree: 1  Resp: 6039.41  Card: 14585.52  Bytes: 0—最优访问路径依旧是IDX_KK_CREATE_DATE,但cost为6039,不再是4,也没有了那句Using prorated density:
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#3): T_NUM(NUMBER)
    AvgLen: 5.00 NDV: 103 Nulls: 0 Density: 9.3440e-05 Min: 1787 Max: 8215831
    Histogram: Freq  #Bkts: 102  UncompBkts: 5351  EndPtVals: 102
  Using prorated density: 6.6174e-05 of col #3 as selectivity of out-of-range value pred
  Table: FF  Alias: AF
    Card: Original: 2118837  Rounded: 140  Computed: 140.21  Non Adjusted: 140.21
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  32187.65  Resp: 32187.65  Degree: 0
      Cost_io: 32030.00  Cost_cpu: 1350695253
      Resp_io: 32030.00  Resp_cpu: 1350695253
  Using prorated density: 6.6174e-05 of col #3 as selectivity of out-of-range value pred
  Access Path: index (AllEqRange)
    Index: IDX_AOI_T_NUM
    resc_io: 52.00  resc_cpu: 425305
    ix_sel: 6.7504e-05  ix_sel_with_filters: 6.7504e-05
    Cost: 52.05  Resp: 52.05  Degree: 1
  Best:: AccessPath: IndexRange  Index: IDX_AOI_T_NUM
         Cost: 52.05  Degree: 1  Resp: 52.05  Card: 140.21  Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  FF[AF]#0  KK[S]#1—CBO选择表FF作为outer table
***************
Now joining: KK[S]#1
***************
NL Join
  Outer table: Card: 140.21  Cost: 52.05  Resp: 52.05  Degree: 1  Bytes: 11
  Inner table: KK  Alias: S
  Access Path: TableScan
    NL Join:  Cost: 906319.17  Resp: 906319.17  Degree: 1
      Cost_io: 899172.00  Cost_cpu: 61235138246
      Resp_io: 899172.00  Resp_cpu: 61235138246
  Access Path: index (UniqueScan)
    Index: PK7
    resc_io: 2.00  resc_cpu: 17253
    ix_sel: 4.1338e-06  ix_sel_with_filters: 4.1338e-06
    NL Join: Cost: 208.26  Resp: 208.26  Degree: 1
      Cost_io: 208.04  Cost_cpu: 1820368
      Resp_io: 208.04  Resp_cpu: 1820368
  Access Path: index (RangeScan)
    Index: IDX_KK_CREATE_DATE
    resc_io: 6032.00  resc_cpu: 54880576
    ix_sel: 0.060293  ix_sel_with_filters: 0.060293
    NL Join: Cost: 845428.82  Resp: 845428.82  Degree: 1
      Cost_io: 844532.00  Cost_cpu: 7683705956
      Resp_io: 844532.00  Resp_cpu: 7683705956
  Access Path: index (AllEqUnique)
    Index: PK7
    resc_io: 2.00  resc_cpu: 17253
    ix_sel: 4.1338e-06  ix_sel_with_filters: 4.1338e-06
    NL Join: Cost: 208.26  Resp: 208.26  Degree: 1
      Cost_io: 208.04  Cost_cpu: 1820368
      Resp_io: 208.04  Resp_cpu: 1820368
  Best NL cost: 208.26
          resc: 208.26 resc_io: 208.04 resc_cpu: 1820368
          resp: 208.26 resp_io: 208.04 resp_cpu: 1820368
Join Card:  140.21 = outer (140.21) * inner (14585.52) * sel (6.8559e-05)
Join Card - Rounded: 140 Computed: 140.21
………………….
Current SQL statement for this session:
select  count(s.id) as orderNum,sum(s.amount) as amount
  from  kk s
 inner join  ff af
    on s.id = af.addrr_id
 where s.create_time > sysdate -160
   and af.t_num =10612711

============
Plan Table
============
------------------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name               | Rows  | Bytes | Cost  | Time      |
------------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT               |                    |       |       |   208 |           |
| 1   |  KKRT AGGREGATE                              |                    |     1 |    30 |       |           |
| 2   |   NESTED LOOPS                                  |                    |   140 |  4200 |   208 |  00:00:03 |
| 3   |    TABLE ACCESS BY INDEX ROWID | FF                 |   140 |  1540 |    52 |  00:00:01 |
| 4   |     INDEX RANGE SCAN                        | IDX_T_NUM          |   140 |       |     3 |  00:00:01 |
| 5   |    TABLE ACCESS BY INDEX ROWID | KK                 |     1 |    19 |     2 |  00:00:01 |
| 6   |     INDEX UNIQUE SCAN                       | PK7                |     1 |       |     1 |  00:00:01 |
------------------------------------------------------------+-----------------------------------+
最后sql选择了正确的执行计划。

总结: sql选择错误执行计划的根本原因是统计信息过于陈旧,该表的统计信息又被锁定

SQL> select stattype_locked from user_tab_statistics where table_name ='KK';

STATTYPE_LOCKED
---------------
ALL
导致定时收集统计信息的job无法定期更新此表的统计信息,重新收集一下表kk的统计信息,问题得到解决。
为防止类似问题的发生,创建一个job, 定期更新kk表上的IDX_KK_CREATE_DATE索引统计信息

 

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

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3112996