效能調優：看看這個匪夷所思的執行計劃。
業務人員反映系統很慢。登入作業系統，用TOP命令檢視，發現大量的CPU都飆升到了100%.根據程式PID一步步找到執行的SQL，都是一個SQL導致的，如下：
SELECT AB14.BAE204 AB14_BAE204,
AB14.BAE214 AB14_BAE214,
AB14.BAE203 AB14_BAE203,
AB13.BAZ010 AB14_AAZ010,
AB13.BAE265 AB14_BAE165,
AB14.BAE006 AB14_BAE006,
(CASE
WHEN AAE140 IN ('31', '35', '37', '3A', '3B') THEN
'31'
ELSE
AAE140
END) AB14_AAE140,
SUM(NVL(AB14.BAE101, 0)) AB14_BAE101,
MAX(TRUNC(AB14.AAB191, 'DD')) AB14_AAB191,
MAX(AB14.AAE011) AB14_AAE011
FROM AB14, AB13
WHERE AB14.BAE204 = AB13.BAE204
AND NVL(BAE171, '0') = '1'
AND NVL(BAE205, 0) = 0
AND AB13.BAE265 = '1'
AND AB14.AAE140 = '11'
AND AB13.AAE036 >= TO_DATE('2010-06-01', 'YYYY-MM-DD')
AND AB13.AAE036 < TO_DATE('2010-06-24', 'YYYY-MM-DD') + 1
GROUP BY AB14.BAE204,
AB14.BAE214,
AB14.BAE203,
AB13.BAZ010,
AB13.BAE265,
AB14.BAE006,
(CASE
WHEN AAE140 IN ('31', '35', '37', '3A', '3B') THEN
'31'
ELSE
AAE140
END)
;
檢視執行計劃：
執行計劃
----------------------------------------------------------
Plan hash value: 2048427295
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 8 (13)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 99 | 8 (13)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 99 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 22 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 1 | 77 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_AB14 | 2 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "AB13"."AAE036" 6 - access(NVL("BAE205",0)=0 AND NVL("BAE171",'0')='1' AND "AB14"."AAE140"='11' AND
"AB14"."BAE204"="AB13"."BAE204")
從執行計劃來看，這個SQL不應該出現效能問題，莫非是CBO評估出現了問題。給SQL增加HINT/*+ gather_plan_statistics */，再次執行。執行了18: 42.75，將近19分鐘。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
Plan hash value: 2048427295
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 730 |00:18:14.73 | 16M| 3 |
| 2 | NESTED LOOPS | | 1 | 1 | 2974 |00:19:03.76 | 16M| 3 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 1 | 9790 |00:00:00.24 | 13301 | 3 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | 1 | 21607 |00:00:00.06 | 125 | 0 |
|* 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 9790 | 1 | 2974 |00:18:02.19 | 16M| 0 |
|* 6 | INDEX RANGE SCAN | IND_AB14 | 9790 | 2 | 582M|00:09:43.72 | 2398K| 0 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AB13"."AAE036" 5 - filter("AB14"."BAE204"="AB13"."BAE204")
6 - access("AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1' AND "AAE140"='11')
匪夷所思的事情發生了：
1）看ID=6的A-Rows列，實際透過索引IND_AB14返回的行數高達582M行。而評估出來的只有兩條。檢視統計資訊都不缺失。
2）IND_AB14是一個函式索引。建立語句如下：create index IND_AB14 on AB14 (NVL(BAE205,0), NVL(BAE171,'0'), AAE140)，但是看執行計劃的謂詞部分（id為6的行），"AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1'從何而來啊，索引裡是沒有這兩個列的資訊的。貌似SYS_NC00031$對於的是NVL("BAE205",0)，SYS_NC00032$對於的是NVL("BAE171",'0')。
3）我用語句查詢了一下，根據索引應該返回的行數。
SQL> SELECT COUNT(*) FROM ab14 WHERE NVL(BAE171, '0') = '1' AND NVL(BAE205, 0) = 0 AND aae140='11';
COUNT(*)
----------
59623
只有不到六萬的資料量，怎麼實際返回了582M的資料。
不過從執行計劃ID=5行可以看出根據"AB14"."BAE204"="AB13"."BAE204"可以過濾掉大量資料，於是修改了之前的函式索引增加了BAE204這個欄位。
create index IND_AB14 on AB14 (NVL(BAE205,0), NVL(BAE171,'0'), AAE140,bae204)
修改索引後，重新執行，只花了2.3秒。
Plan hash value: 1600515523
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 743 |00:00:00.37 | 33700 | 55 |
| 2 | NESTED LOOPS | | 1 | 1 | 3073 |00:00:00.31 | 33700 | 55 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 1 | 9803 |00:00:00.06 | 13282 | 0 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | 1 | 21624 |00:00:00.02 | 108 | 0 |
| 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 9803 | 1 | 3073 |00:00:00.27 | 20418 | 55 |
|* 6 | INDEX RANGE SCAN | IND_AB14_BAE204 | 9803 | 1 | 3073 |00:00:00.25 | 19619 | 55 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AB13"."AAE036" 6 - access("AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1' AND "AAE140"='11' AND
"AB14"."BAE204"="AB13"."BAE204"
CBO評估E-Rows依然非常不準確，重新分析表兩個表(僅提供一個AB14的,AB13的略)：
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'NCSI',
TABNAME => 'AB14',
ESTIMATE_PERCENT => 100,
METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY',
CASCADE => TRUE,
DEGREE => 10);
END;
重新執行檢視執行計劃：
Plan hash value: 1600515523
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 743 |00:00:00.23 | 33709 | 2 |
| 2 | NESTED LOOPS | | 1 | 1 | 3073 |00:00:00.20 | 33709 | 2 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 1 | 9806 |00:00:00.06 | 13285 | 1 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | 1 | 21628 |00:00:00.02 | 108 | 0 |
| 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 9806 | 1 | 3073 |00:00:00.11 | 20424 | 1 |
|* 6 | INDEX RANGE SCAN | IND_AB14_BAE204 | 9806 | 1 | 3073 |00:00:00.09 | 19625 | 0 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AB13"."AAE036" 6 - access("AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1' AND "AAE140"='11' AND
"AB14"."BAE204"="AB13"."BAE204")
幾乎沒任何改善。評估出來的依然很差。
[ 本帖最後由 wei-xh 於 2010-6-24 17:12 編輯 ]
SELECT AB14.BAE204 AB14_BAE204,
AB14.BAE214 AB14_BAE214,
AB14.BAE203 AB14_BAE203,
AB13.BAZ010 AB14_AAZ010,
AB13.BAE265 AB14_BAE165,
AB14.BAE006 AB14_BAE006,
(CASE
WHEN AAE140 IN ('31', '35', '37', '3A', '3B') THEN
'31'
ELSE
AAE140
END) AB14_AAE140,
SUM(NVL(AB14.BAE101, 0)) AB14_BAE101,
MAX(TRUNC(AB14.AAB191, 'DD')) AB14_AAB191,
MAX(AB14.AAE011) AB14_AAE011
FROM AB14, AB13
WHERE AB14.BAE204 = AB13.BAE204
AND NVL(BAE171, '0') = '1'
AND NVL(BAE205, 0) = 0
AND AB13.BAE265 = '1'
AND AB14.AAE140 = '11'
AND AB13.AAE036 >= TO_DATE('2010-06-01', 'YYYY-MM-DD')
AND AB13.AAE036 < TO_DATE('2010-06-24', 'YYYY-MM-DD') + 1
GROUP BY AB14.BAE204,
AB14.BAE214,
AB14.BAE203,
AB13.BAZ010,
AB13.BAE265,
AB14.BAE006,
(CASE
WHEN AAE140 IN ('31', '35', '37', '3A', '3B') THEN
'31'
ELSE
AAE140
END)
;
檢視執行計劃：
執行計劃
----------------------------------------------------------
Plan hash value: 2048427295
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 8 (13)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 99 | 8 (13)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 99 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 22 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 1 | 77 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_AB14 | 2 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "AB13"."AAE036"
"AB14"."BAE204"="AB13"."BAE204")
從執行計劃來看，這個SQL不應該出現效能問題，莫非是CBO評估出現了問題。給SQL增加HINT/*+ gather_plan_statistics */，再次執行。執行了18: 42.75，將近19分鐘。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
Plan hash value: 2048427295
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 730 |00:18:14.73 | 16M| 3 |
| 2 | NESTED LOOPS | | 1 | 1 | 2974 |00:19:03.76 | 16M| 3 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 1 | 9790 |00:00:00.24 | 13301 | 3 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | 1 | 21607 |00:00:00.06 | 125 | 0 |
|* 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 9790 | 1 | 2974 |00:18:02.19 | 16M| 0 |
|* 6 | INDEX RANGE SCAN | IND_AB14 | 9790 | 2 | 582M|00:09:43.72 | 2398K| 0 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AB13"."AAE036"
6 - access("AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1' AND "AAE140"='11')
1）看ID=6的A-Rows列，實際透過索引IND_AB14返回的行數高達582M行。而評估出來的只有兩條。檢視統計資訊都不缺失。
2）IND_AB14是一個函式索引。建立語句如下：create index IND_AB14 on AB14 (NVL(BAE205,0), NVL(BAE171,'0'), AAE140)，但是看執行計劃的謂詞部分（id為6的行），"AB14"."SYS_NC00031$"=0 AND "AB14"."SYS_NC00032$"='1'從何而來啊，索引裡是沒有這兩個列的資訊的。貌似SYS_NC00031$對於的是NVL("BAE205",0)，SYS_NC00032$對於的是NVL("BAE171",'0')。
3）我用語句查詢了一下，根據索引應該返回的行數。
SQL> SELECT COUNT(*) FROM ab14 WHERE NVL(BAE171, '0') = '1' AND NVL(BAE205, 0) = 0 AND aae140='11';
COUNT(*)
----------
59623
只有不到六萬的資料量，怎麼實際返回了582M的資料。
不過從執行計劃ID=5行可以看出根據"AB14"."BAE204"="AB13"."BAE204"可以過濾掉大量資料，於是修改了之前的函式索引增加了BAE204這個欄位。
create index IND_AB14 on AB14 (NVL(BAE205,0), NVL(BAE171,'0'), AAE140,bae204)
修改索引後，重新執行，只花了2.3秒。
Plan hash value: 1600515523
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 743 |00:00:00.37 | 33700 | 55 |
| 2 | NESTED LOOPS | | 1 | 1 | 3073 |00:00:00.31 | 33700 | 55 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 1 | 9803 |00:00:00.06 | 13282 | 0 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | 1 | 21624 |00:00:00.02 | 108 | 0 |
| 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 9803 | 1 | 3073 |00:00:00.27 | 20418 | 55 |
|* 6 | INDEX RANGE SCAN | IND_AB14_BAE204 | 9803 | 1 | 3073 |00:00:00.25 | 19619 | 55 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AB13"."AAE036"
"AB14"."BAE204"="AB13"."BAE204"
CBO評估E-Rows依然非常不準確，重新分析表兩個表(僅提供一個AB14的,AB13的略)：
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'NCSI',
TABNAME => 'AB14',
ESTIMATE_PERCENT => 100,
METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY',
CASCADE => TRUE,
DEGREE => 10);
END;
重新執行檢視執行計劃：
Plan hash value: 1600515523
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 743 |00:00:00.23 | 33709 | 2 |
| 2 | NESTED LOOPS | | 1 | 1 | 3073 |00:00:00.20 | 33709 | 2 |
|* 3 | TABLE ACCESS BY INDEX ROWID| AB13 | 1 | 1 | 9806 |00:00:00.06 | 13285 | 1 |
|* 4 | INDEX RANGE SCAN | IDX_AB13_AAE036 | 1 | 1 | 21628 |00:00:00.02 | 108 | 0 |
| 5 | TABLE ACCESS BY INDEX ROWID| AB14 | 9806 | 1 | 3073 |00:00:00.11 | 20424 | 1 |
|* 6 | INDEX RANGE SCAN | IND_AB14_BAE204 | 9806 | 1 | 3073 |00:00:00.09 | 19625 | 0 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("AB13"."BAE265"='1')
4 - access("AB13"."AAE036">=TO_DATE(' 2010-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AB13"."AAE036"
"AB14"."BAE204"="AB13"."BAE204")
幾乎沒任何改善。評估出來的依然很差。
[ 本帖最後由 wei-xh 於 2010-6-24 17:12 編輯 ]
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-666125/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【sql調優之執行計劃】獲取執行計劃SQL
- oracle筆記整理14——效能調優之oracle執行計劃Oracle筆記
- mysql調優之——執行計劃explainMySqlAI
- Oracle調優之看懂Oracle執行計劃Oracle
- 【sql調優之執行計劃】sort operationsSQL
- 【sql調優之執行計劃】estimator iSQL
- 【sql調優之執行計劃】hash joinSQL
- 【sql調優之執行計劃】in相關的operationSQL
- 【sql調優之執行計劃】query transformerSQLORM
- 【sql調優之執行計劃】temp table transformationSQLORM
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- 【效能優化】執行計劃與直方圖優化直方圖
- MySQL調優篇 | EXPLAIN執行計劃解讀（4）MySqlAI
- 【sql調優之執行計劃】merge sort joinSQL
- 【sql調優之執行計劃】使用hint(五)Hint for parallelSQLParallel
- 【sql調優之執行計劃】使用hint(四)Hints for JoinSQL
- 【優化】Oracle 執行計劃優化Oracle
- 建立索引調整sql的執行計劃索引SQL
- 【sql調優之執行計劃】使用hint(三)Hints for Query TransformationsSQLORM
- 【sql調優之執行計劃】使用hint(二)Hints for Access PathsSQL
- TiDB 查詢優化及調優系列（四）查詢執行計劃的調整及優化原理TiDB優化
- java效能調優記錄（執行緒阻塞）Java執行緒
- 【優化】ORACLE執行計劃分析優化Oracle
- 【sql調優之執行計劃】使用hint(六) append and noappendSQLAPP
- 【sql調優之執行計劃】merge join cartesian and buffer sortSQL
- 【sql調優之執行計劃】使用hint(一)Hints for Optimization Approaches and GoalsSQLAPPGo
- 【sql調優之執行計劃】merge semi join and merge anti joinSQL
- 【sql調優之執行計劃】nested loops join and nested loop join outerSQLOOP
- 執行計劃的偏差導致的效能問題
- Oracle+高效能SQL引擎剖析：SQL優化與調優機制詳解-筆記之執行計劃（一）OracleSQL優化筆記
- Oracle+高效能SQL引擎剖析：SQL優化與調優機制詳解-筆記之執行計劃（二）OracleSQL優化筆記
- 【MySQL】MySQL的執行計劃及索引優化MySql索引優化
- 執行計劃-1：獲取執行計劃
- SQL執行計劃異常引起的效能問題SQL
- SQL執行計劃異常 引起的效能問題SQL
- 完美的執行計劃導致的效能問題
- 對一個執行計劃的疑問
- 效能調優概述，這是一篇最通俗易懂的效能調優總結