date列統計資訊陳舊導致sql沒有選擇最優執行計劃
開發人員報告一條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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 統計資訊不正確導致執行計劃的錯誤選擇
- 統計資訊過舊導致SQL無法執行出來SQL
- _optimizer_invalidation_periond導致收集統計資訊後執行計劃沒有改變
- 統計資訊不準確導致執行計劃走了笛卡爾積
- ORACLE analyse table方式收集表統計資訊導致SQL執行計劃不準確而效能下降OracleSQL
- 【sql調優之執行計劃】獲取執行計劃SQL
- 執行計劃錯誤導致系統負載高負載
- 【YashanDB知識庫】收集分割槽表統計資訊取樣率小於1導致SQL執行計劃走偏SQL
- Grant許可權導致執行計劃失效
- 【PG執行計劃】Postgresql資料庫執行計劃統計資訊簡述SQL資料庫
- sql 執行計劃SQL
- Oracle優化案例-統計資訊對執行計劃的影響（十三）Oracle優化
- 【sql調優之執行計劃】sort operationsSQL
- 【sql調優之執行計劃】estimator iSQL
- 【sql調優之執行計劃】hash joinSQL
- 完美的執行計劃導致的效能問題
- 執行計劃的偏差導致的效能問題
- Oracle sql執行計劃OracleSQL
- SQL Server執行計劃SQLServer
- SQL的執行計劃SQL
- SQL執行計劃分析SQL
- SQL調優--表統計資訊未及時更新導致查詢超級慢SQL
- ORACLE SQL調優之統計資訊缺失導致的邏輯讀暴增OracleSQL
- 執行資訊統計後沒有使用索引索引
- autotrace 和explain plan for可能導致執行計劃錯誤AI
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- 使用leading(,)優化sql執行計劃優化SQL
- 【sql調優之執行計劃】query transformerSQLORM
- 【sql調優之執行計劃】temp table transformationSQLORM
- 一次ORACLE SQL謂詞跨界導致的執行計劃不準OracleSQL
- 通過鎖定表的統計資訊來穩定sql的執行計劃SQL
- 統計資訊過期導致SQL進行NESTED LOOPS查詢緩慢SQLOOP
- MySQL 5.7獲取指定執行緒正在執行SQL的執行計劃資訊MySql執行緒
- 找出導致db file scattered read等待事件發生的SQL及其執行計劃事件SQL
- Laravel 修改資料與原資料一樣 導致沒有執行sqlLaravelSQL
- 獲取SQL執行計劃SQL
- SQL 執行計劃案例1SQL
- 剖析SQL Server執行計劃SQLServer