採用直方圖改善SQL執行效能

今天下午朋友QQ給我說他遇到一個SQL相關的效能問題，執行查詢相當…的慢，發SQL發過來之後，發現跟上次讓我看的是一樣的，上次調整過一次，似乎效果不是很理想…，進入正題。

select * from
(select * from U_MSG
where MS='DELIVRD'
and SA = '1066898829'
and ICP_ID='91188'
and SCTS>'2011-02-01'
and SIGN is null ) C
where rownum=1;

告訴朋友如何收集執行計劃，發過來的結果為：

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=217)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'U_MSG' (Cost=4 Card=1 Bytes=217)
3 2 INDEX (RANGE SCAN) OF 'INDEX_NEW_SCTS' (NON-UNIQUE) (Cost=3 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
128746 consistent gets
28245 physical reads
0 redo size
2451 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)

1 rows processed

從autotrace的結果可以看出，在SQL執行時，走的索引是INDEX_NEW_SCTS，且有相當嚴重的邏輯讀和物理讀，我們也知道，大量的物理讀勢必會影響到系統的效能。

透過觀察SQL語句和讓他發過來的表結構明細，瞭解到列MS、SA、ICP_ID、SCTS均是varchar2型別。然後看了下MS列的選擇性：

MS COUNT(*)
------------ ----------
DB:0101 791
DB:0102 721
DB:0107 33
DB:0115 9309
DB:0116 9
DB:0182 35
DELIVRD 268197
EXPIRED 3532
MB:1042 4
MC:0055 5
MI:0000 202
MI:0057 4
MK:0000 151
MK:0029 2
MS_MO 213517
MS_MT 298401
REJECTD 47723
UNDELIV 153652
可以看出在整張表中，MS的distinct值不是很多，但每個值的重複度不一樣，甚至差距較大，這種情況下，可以考慮採用直方圖，步驟如下：

在MS列建立索引：
SQL>create index GJPT.U_MSG_1IX on GJPT.U_MSG(ms) tablespace idx_tbs;

收集列MS上的直方圖統計資訊：
SQL>exec dbms_stats.gather_table_stats('GJPT','U_MSG',cascade=>true,method_opt => 'for columns MS size 10');

做完以上兩步之後，再次看執行計劃時發現執行計劃沒有任何變化，汗…

思考一會之後，決定把整張表重新analyze下，方法如下：
SQL> begin
2 DBMS_STATS.GATHER_TABLE_STATS(ownname=>'GJPT',
3 tabname=>'U_MSG',
4 estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
5 cascade=>true);
6 end;
7 /
PL/SQL 過程已成功完成。

分析完之後，再此看執行計劃：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=36.58368284467 Card=1
ytes=2280)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'U_MSG' (Cost=36.583682 84467 Card=10 Bytes=2280)
3 2 BITMAP CONVERSION (TO ROWIDS)
4 3 BITMAP AND
5 4 BITMAP CONVERSION (FROM ROWIDS)
6 5 INDEX (RANGE SCAN) OF 'INDEX_NEW_SA' (NON-UNIQUE) Cost=6 Card=828)
7 4 BITMAP CONVERSION (FROM ROWIDS)
8 7 INDEX (RANGE SCAN) OF 'U_MSG_1IX' (NON-UNIQUE)
Cost=230 Card=828)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10253 consistent gets
0 physical reads
0 redo size
2457 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

從這個執行計劃可以看出，physical reads降至0，consistent gets從之前的128746降至10253，其實10253這個值也不算小，實際環境中OS的記憶體和DB_CACHE_SIZE設定的也都比較小，如果加大sga，還可以更好的改善效能，更重要的是執行時間從原來的十餘分鐘降至0.2毫秒左右。

--- END ---

[@more@]