开发人员报告一条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 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索引统计信息
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-8-1 21:41:37 |
赞(
操作
