記一個SQL優化案例
某省電信在做批扣（批銷）時，出現嚴重的效能問題，發現下面這一條SQL效能非常低下：
SELECT A.ACCT_BALANCE_ID,
A.BALANCE_TYPE_ID,
A.ACCT_ID,
NVL(A.SERV_ID, -1) SERV_ID,
NVL(A.ITEM_GROUP_ID, -1) ITEM_GROUP_ID,
A.OBJECT_TYPE_ID,
F.PRIORITY,
A.BALANCE,
NVL(A.CYCLE_UPPER, -1) CYCLE_UPPER,
NVL(A.CYCLE_LOWER, -1) CYCLE_LOWER,
NVL(A.CYCLE_UPPER_TYPE, ’ ’) CYCLE_UPPER_TYPE,
NVL(A.CYCLE_LOWER_TYPE, ’ ’) CYCLE_LOWER_TYPE,
B.ADJUST_FLAG ADJUST_FLAG,
B.ALLOW_TRANS ALLOW_TRANS,
B.CORPUS_FLAG,
NVL(TO_CHAR(A.EFF_DATE, ’YYYYMMDDHH24MISS’), ’ ’) EFF_DATE,
NVL(TO_CHAR(A.EXP_DATE, ’YYYYMMDDHH24MISS’), ’ ’) EXP_DATE,
A.STATE,
TO_CHAR(A.STATE_DATE, ’YYYYMMDDHH24MISS’) STATE_DATE,
B.BALANCE_TYPE_NAME,
NVL(C.ACCT_NAME, ’ ’) ACCT_NAME,
NVL(D.ACC_NBR, ’ ’) SERV_NAME,
NVL(E.ITEM_GROUP_NAME, ’ ’) ITEM_GROUP_NAME
FROM (SELECT ACCT_BALANCE_ID,
BALANCE_TYPE_ID,
ACCT_ID,
NVL(SERV_ID, -1) SERV_ID,
NVL(ITEM_GROUP_ID, -1) ITEM_GROUP_ID,
OBJECT_TYPE_ID,
BALANCE,
NVL(CYCLE_UPPER, -1) CYCLE_UPPER,
NVL(CYCLE_LOWER, -1) CYCLE_LOWER,
NVL(CYCLE_UPPER_TYPE, ’ ’) CYCLE_UPPER_TYPE,
NVL(CYCLE_LOWER_TYPE, ’ ’) CYCLE_LOWER_TYPE,
EFF_DATE,
EXP_DATE,
STATE,
STATE_DATE,
0 SHARE_RULE_PRIORITY
FROM ACCT_BALANCE
WHERE ACCT_ID = :LACCTID
AND BALANCE > 0
AND BALANCE_TYPE_ID != 1
AND STATE = ’10A’
UNION
SELECT A1.ACCT_BALANCE_ID,
A1.BALANCE_TYPE_ID,
B1.ACCT_ID,
NVL(B1.SERV_ID, -1) SERV_ID,
NVL(B1.ITEM_GROUP_ID, -1) ITEM_GROUP_ID,
A1.OBJECT_TYPE_ID,
A1.BALANCE,
NVL(B1.UPPER_AMOUNT, -1) CYCLE_UPPER,
NVL(B1.LOWER_AMOUNT, -1) CYCLE_LOWER,
NVL(A1.CYCLE_UPPER_TYPE, ’ ’) CYCLE_UPPER_TYPE,
NVL(A1.CYCLE_LOWER_TYPE, ’ ’) CYCLE_LOWER_TYPE,
A1.EFF_DATE,
A1.EXP_DATE,
A1.STATE,
A1.STATE_DATE,
B1.PRIORITY SHARE_RULE_PRIORITY
FROM ACCT_BALANCE A1, BALANCE_SHARE_RULE B1
WHERE A1.ACCT_BALANCE_ID = B1.ACCT_BALANCE_ID
AND B1.ACCT_ID = :LACCTID
AND A1.BALANCE > 0
AND A1.BALANCE_TYPE_ID != 1
AND A1.STATE = ’10A’
AND NVL(B1.EFF_DATE, SYSDATE) < = SYSDATE
AND NVL(B1.EXP_DATE, SYSDATE) >= SYSDATE) A,
BALANCE_TYPE B,
ACCT C,
SERV D,
A_BALANCE_ITEM_GROUP E,
A_BALANCE_OBJECT_TYPE F
WHERE A.BALANCE_TYPE_ID = B.BALANCE_TYPE_ID
AND A.OBJECT_TYPE_ID = F.OBJECT_TYPE_ID
AND A.ACCT_ID = C.ACCT_ID
AND A.SERV_ID = D.SERV_ID(+)
AND A.ITEM_GROUP_ID = E.ITEM_GROUP_ID(+)
ORDER BY F.PRIORITY,
B.PRIORITY,
A.SHARE_RULE_PRIORITY ASC,
A.EXP_DATE ASC,
A.EFF_DATE ASC,
A.BALANCE ASC
檢視執行計劃：
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2723G| 696T| | 7776M| | |
| 1 | SORT ORDER BY | | 2723G| 696T| 1503T| 7776M| | |
|* 2 | HASH JOIN | | 2723G| 696T| 59M| 39355 | | |
|* 3 | HASH JOIN | | 228K| 56M| | 23918 | | |
| 4 | TABLE ACCESS FULL | BALANCE_TYPE | 8 | 184 | | 7 | | |
|* 5 | HASH JOIN | | 228K| 51M| | 23907 | | |
| 6 | TABLE ACCESS FULL | A_BALANCE_OBJECT_TYPE | 4 | 16 | | 7 | | |
|* 7 | HASH JOIN OUTER | | 228K| 50M| 43M| 23896 | | |
|* 8 | HASH JOIN OUTER | | 228K| 40M| 38M| 23199 | | |
| 9 | VIEW | | 228K| 36M| | 2043 | | |
| 10 | SORT UNIQUE | | 228K| 11M| 38M| 2043 | | |
| 11 | UNION-ALL | | | | | | | |
|* 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | ACCT_BALANCE | 228K| 11M| | 50 | ROWID | ROW L |
|* 13 | INDEX RANGE SCAN | IDX_ACCT_BALANCE_ACCT_ID42 | 121K| | | 3 | | |
| 14 | NESTED LOOPS | | 1 | 146 | | 4 | | |
|* 15 | TABLE ACCESS FULL | BALANCE_SHARE_RULE | 1 | 109 | | 2 | | |
|* 16 | TABLE ACCESS BY GLOBAL INDEX ROWID| ACCT_BALANCE | 1 | 37 | | 2 | ROWID | ROW L |
|* 17 | INDEX UNIQUE SCAN | PK_P_ACCT_BALANCE2 | 1 | | | 1 | | |
| 18 | PARTITION RANGE ALL | | | | | | 1 | 63 |
| 19 | TABLE ACCESS FULL | SERV | 12M| 258M| | 14070 | 1 | 63 |
| 20 | TABLE ACCESS FULL | A_BALANCE_ITEM_GROUP | 244 | 11224 | | 7 | | |
| 21 | PARTITION RANGE ALL | | | | | | 1 | 63 |
| 22 | TABLE ACCESS FULL | ACCT | 11M| 239M| | 8505 | 1 | 63 |
------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access(”A”.”ACCT_ID”=”C”.”ACCT_ID”)
3 - access(”A”.”BALANCE_TYPE_ID”=”B”.”BALANCE_TYPE_ID”)
5 - access(”A”.”OBJECT_TYPE_ID”=”F”.”OBJECT_TYPE_ID”)
7 - access(”A”.”ITEM_GROUP_ID”=”E”.”ITEM_GROUP_ID”(+))
8 - access(”A”.”SERV_ID”=”D”.”SERV_ID”(+))
12 - filter(”ACCT_BALANCE”.”BALANCE”>0 AND “ACCT_BALANCE”.”BALANCE_TYPE_ID”<>1 AND “ACCT_BALANCE”.”STATE”=’10A’)
13 - access(”ACCT_BALANCE”.”ACCT_ID”=TO_NUMBER(:Z))
15 - filter(”B1″.”ACCT_ID”=TO_NUMBER(:Z) AND NVL(”B1″.”EFF_DATE”,SYSDATE@!)< =SYSDATE@! AND
NVL("B1"."EXP_DATE",SYSDATE@!)>=SYSDATE@!)
16 - filter(”A1″.”BALANCE”>0 AND “A1″.”BALANCE_TYPE_ID”<>1 AND “A1″.”STATE”=’10A’)
17 - access(”A1″.”ACCT_BALANCE_ID”=”B1″.”ACCT_BALANCE_ID”)
從執行計劃來看，Oracle評估出來的COST是相當的驚人（7776M），而返回的行數是2723G。看起來應該是統計資訊不準確導致了選擇了錯誤的執行計劃。
對於一個SQL，如果其執行計劃錯誤地採用於full table scan和hash join，則一般是由於評估表訪問返回的資料行數過多，使其高估了成本。反之，如果是錯誤地採用了索引掃描，則一般是由於評估表訪問返回的資料行數太少，使其低估了成本。
我們來分析這個執行計劃。首先檢視訪問表時，Oracle評估返回的行的數目是否有偏大的情況。我們發現看以下的幾行，返回的結果集行數很高：
|* 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | ACCT_BALANCE | 228K| 11M| | 50 | ROWID | ROW L |
|* 13 | INDEX RANGE SCAN | IDX_ACCT_BALANCE_ACCT_ID42 | 121K| | | 3 | | |
| 19 | TABLE ACCESS FULL | SERV | 12M| 258M| | 14070 | 1 | 63 |
| 22 | TABLE ACCESS FULL | ACCT | 11M| 239M| | 8505 | 1 | 63 |
最值得懷疑的首先是第12行和第13行。第13行是一個索引範圍掃描，返回的結果集行數居然有121K行，而這個訪問條件只是 “access(”ACCT_BALANCE”.”ACCT_ID”=TO_NUMBER(:Z))”，從業務上來看，看起來這條SQL是查詢某個帳戶的 餘額及其他資訊的。輸入的條件為ACCT_ID，而一般來說，帳戶餘額表中，一個ACCT_ID的對應的行，只有很少的行（10行以下），而不會返回這麼 多行。
IDX_ACCT_BALANCE_ACCT_ID42這個索引，是一個在ACCT_BALANCE表（這是個分割槽表）的ACCT_ID上的全域性普 通索引(GLOBAL NORMAL INDEX)。檢查統計資訊，這個表是當天才收集的，ACCT_ID這個欄位沒有任何統計資訊（已經被刪除）。同時索引中的統計資訊如下：
Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster
Name Unique Level Blks Keys of Rows Per Key Per Key Factor
--------------- --------- ----- ---- -------------- -------------- ----------- ----------- ------------
IDX_ACCT_BALANC NONUNIQUE 2 #### 580,079 30,742,660 1 47 27,447,720
E_ACCT_ID42
看起來索引的Distinct Keys值太小，才580,079。索引的總行數為30,742,660。重新分析該索引後，Distinct Keys的值才是正確的值：10,540,493。
再看看ACCT_BALANCE表中的ACCT_ID欄位，由於沒有統計資訊，那麼Oracle會使用一個預設的選擇率（對於表的單個欄位條件過濾，一般 是1%）。對於這樣一個3000多萬行的表來說，預設的選擇率造成評估的返回行數過高。由於正是出帳期間，重新分析這樣一個表，影響是非常大的，而這個表 之前剛分析過。我們這裡可以使用DBMS_STATS.SET_COLUMN_STATS過程手工設定統計資訊：
SQL> exec dbms_stats.set_column_stats(ownname=>user,tabname=>’ACCT_BALANCE’,colname=>’ACCT_ID’,
NULLCNT=>0,DENSITY=>1/10540393,DISTCNT=>10540393);
這裡的統計資訊來自於ACCT_ID欄位上的索引（ACCT_ID欄位定義為NOT NULL，所以比較方便地手工計算出這些統計資訊）。
我們再次檢視執行計劃：
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35M| 9603M| | 1472K| | |
| 1 | SORT ORDER BY | | 35M| 9603M| 20G| 1472K| | |
| 2 | NESTED LOOPS | | 35M| 9603M| | 62 | | |
| 3 | NESTED LOOPS OUTER | | 3 | 780 | | 56 | | |
| 4 | NESTED LOOPS OUTER | | 3 | 717 | | 50 | | |
| 5 | NESTED LOOPS | | 3 | 579 | | 47 | | |
| 6 | NESTED LOOPS | | 3 | 510 | | 44 | | |
| 7 | VIEW | | 3 | 498 | | 42 | | |
| 8 | SORT UNIQUE | | 3 | 248 | | 42 | | |
| 9 | UNION-ALL | | | | | | | |
|* 10 | TABLE ACCESS BY GLOBAL INDEX ROWID | ACCT_BALANCE | 2 | 102 | | 6 | ROWID | ROW L |
|* 11 | INDEX RANGE SCAN | IDX_ACCT_BALANCE_ACCT_ID42 | 3 | | | 3 | | |
| 12 | NESTED LOOPS | | 1 | 146 | | 4 | | |
|* 13 | TABLE ACCESS FULL | BALANCE_SHARE_RULE | 1 | 109 | | 2 | | |
|* 14 | TABLE ACCESS BY GLOBAL INDEX ROWID| ACCT_BALANCE | 1 | 37 | | 2 | ROWID | ROW L |
|* 15 | INDEX UNIQUE SCAN | PK_P_ACCT_BALANCE2 | 1 | | | 1 | | |
| 16 | TABLE ACCESS BY INDEX ROWID | A_BALANCE_OBJECT_TYPE | 1 | 4 | | 1 | | |
|* 17 | INDEX UNIQUE SCAN | PK_A_BALANCE_OBJECT_TYPE | 1 | | | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | BALANCE_TYPE | 1 | 23 | | 1 | | |
|* 19 | INDEX UNIQUE SCAN | PK_BALANCE_TYPE | 1 | | | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | A_BALANCE_ITEM_GROUP | 1 | 46 | | 1 | | |
|* 21 | INDEX UNIQUE SCAN | PK_ITEM_GROUP_ID_HN | 1 | | | | | |
| 22 | TABLE ACCESS BY GLOBAL INDEX ROWID | SERV | 1 | 21 | | 2 | ROWID | ROW L |
|* 23 | INDEX UNIQUE SCAN | PK_SERV | 1 | | | 1 | | |
| 24 | TABLE ACCESS BY GLOBAL INDEX ROWID | ACCT | 11M| 239M| | 2 | ROWID | ROW L |
|* 25 | INDEX UNIQUE SCAN | PK_ACCT | 1 | | | 1 | | |
------------------------------------------------------------------------------------------------------------------------------------
現在可以看到，執行計劃已經是改善了很多，只有唯一的一個全表掃描--BALANCE_SHARE_RULE，不過那個表的行數為0。
這個時候，客戶反映系統正常了。雖然Oracle在ACCT表上評估返回的行數仍然很高，但是從系統故障處理這個角度上講目的已經達到了。接下來就是處理這個ACCT表，類似的問題，類似的過程，這裡不再細述。
總結：
此次效能問題的處理，從接到客戶電話，到撥號連線，到最終處理完成，不到1小時。個人認為，這個處理速度還是比較快速的。做一個如下的總結：
- 瞭解SQL是幹什麼的（也就是理解SQL的業務），非常有助於調優。
- 調優需要善於與客戶和開發溝通。
- 很多時候，現在ORACLE的優化器已經非常智慧和完善，SQL效能問題是由於優化統計資訊不準確。使用hint那個是治標不治 本。只有在非常複雜的SQL（比如有非常多的內聯檢視，子查詢等等），才應該考慮HINT。這只是個人觀點。當然還會有其他需要使用HINT才能解決的地 方。
- 善用指令碼，這個案例中，我再次使用了sosi.sql這個指令碼來檢視錶的優化統計資訊，能夠大大節約時間，同時檢視的優化統計資訊非常完整。
- 善用DBMS_STATS，在這個案例中，使用set_column_stats，大大節省了收集優化統計資訊的時間。
相關文章
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- MySQL SQL優化案例(一)MySql優化
- 效能優化案例-SQL優化優化SQL
- 一個SQL優化SQL優化
- 一個效能優化的案例優化
- Oracle 某行系統SQL優化案例(一)OracleSQL優化
- 一個sql的優化SQL優化
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- SQL效能優化案例分析SQL優化
- 【MySQL】NOT EXISTS優化的一個案例MySql優化
- 記一次sql優化SQL優化
- 使用SQL Profile進行SQL優化案例SQL優化
- SQL優化案例-union代替or（九）SQL優化
- greenplum 簡單sql優化案例SQL優化
- SQL優化案例-使用with as優化Subquery Unnesting（七）SQL優化
- 一個複合索引的優化案例索引優化
- 一個MySQL優化案例的初步思路MySql優化
- SQL優化之多表關聯查詢-案例一SQL優化
- 一次sql優化小記SQL優化
- 一個SQL語句的優化SQL優化
- 記一次前端效能優化的案例前端優化
- sql語句的優化案例分析SQL優化
- SQL優化筆記SQL優化筆記
- SQL Server一次SQL調優案例SQLServer
- SQL優化（一）SQL優化
- SQL優化案例一則--複合索引沒有被使用SQL優化索引
- sql優化案例一:使用了表示式不會使用索引SQL優化索引
- IO優化案例一則優化
- Oracle某行系統SQL優化案例(二)OracleSQL優化
- Oracle某行系統SQL優化案例(三)OracleSQL優化
- Oracle某行系統SQL優化(案例五)OracleSQL優化
- 記一個效能優化問題優化
- SQL優化案例-單表分頁語句的優化（八）SQL優化
- 【案例】MySQL count操作優化案例一則MySql優化
- Oracle Sql優化筆記OracleSQL優化筆記
- 一個SQL效能問題的優化探索SQL優化