適當採用Histogram 讓Oracle達成最優Execution Plan

某Production DB, 主要是OLTP和一部分Report。最近由於盤點經常要出一個Report，這個Report會用對List裡的COA_ITEM的每個值去撈取資料。但這個Report經常要跑很長時間。

[@more@]

其中一個重要相關SQL如下：

SELECT count(*)

FROM mo_d d, coa_sn_d s

WHERE s.flag = 'Z'

AND d.isn = s.isn

AND d.grp <> 'YYY'

AND s.coa_item = 'XXXXXXXX'

AND s.companyid = 'FFFPC';

Report的Developer在搞出這個Report後發現執行時間大大超出預期，有時要1小時才能出全部結果，但檢查一些List裡的COA_ITEM的值又發現都執行很快。

然後這個問題交到我手上。

在跟Developer瞭解更多執行情況後，焦點集中到某些COA_ITEM的值上，在執行這些值的時侯，這個SQL通常要10~30分鐘才能跑完。

檢查了下2個Table的Analyze日期，都比較近。 而既然有些快有些慢，那問題就很可能出在Data Skew上。

2個Table的資料：

COA_SN_D: 650 0000 rows non-partitioned table

MO_D: rows > 100 000 000rows partitioned table

該SQL一般的執行計劃：

SQL> SELECT COUNT(*)

2 FROM mo_d d, coa_sn_d s

3 WHERE s.flag = 'C'

4 AND d.isn = s.isn

5 AND d.grp <> 'H01'

6 AND s.coa_item = '15L1-000A0AS'

7 AND s.companyid = 'FFFPC';

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1434 Card=1 Bytes=58

)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MO_D' (Cost=4 C

ard=1 Bytes=21)

3 2 NESTED LOOPS (Cost=1432 Card=1841 Bytes=106778)

4 3 TABLE ACCESS (BY INDEX ROWID) OF 'COA_SN_D' (Cost=11

0 Card=1841 Bytes=68117)

5 4 INDEX (RANGE SCAN) OF 'COA_SN_D4' (NON-UNIQUE) (Co

st=13 Card=2040)

6 3 INDEX (RANGE SCAN) OF 'MO_D1' (UNIQUE) (Cost=3 Card=

1)

Execution Plan顯示Join Plan是Nested Loop.

NL在Driving Row sets小的情況下有很好的效能，但在Driving Row sets大的情況下效能卻很糟糕。很顯然，在某些COA_ITEM的值下，Driving Row sets的實際結果要比CBO預估的要大很多。

在單獨對某些COA_ITEM值產生的row sets 進行count(*)的結果表明，數量都是150W以上。

接下來又問了下Developer，最後執行結果多大，得到結果是幾千到1萬多些行。

面對這樣的JOIN情形，最值得采用的無疑就是HASH JOIN了。

強制採用Hash_join:

SQL> SELECT /*+use_hash(s,d)*/COUNT(*)

2 FROM mo_d d, coa_sn_d s

3 WHERE s.flag = 'C'

4 AND d.isn = s.isn

5 AND d.grp <> 'H01'

6 AND s.coa_item = '15G29L100300'

7 AND s.companyid = 'FFFPC';

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=90462 Card=1 Bytes=5

8)

1 0 SORT (AGGREGATE)

2 1 HASH JOIN (Cost=90462 Card=463 Bytes=26854)

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'COA_SN_D' (Cost=31 C

ard=463 Bytes=17131)

4 3 INDEX (RANGE SCAN) OF 'COA_SN_D4' (NON-UNIQUE) (Cost

=6 Card=513)

5 2 PARTITION RANGE (ALL)

6 5 PARTITION HASH (ALL)

7 6 TABLE ACCESS (FULL) OF 'MO_D' (Cost=89982 Card=259

54787 Bytes=545050527)

COST 9W多。

實際執行時間3分鐘不到，Consistent Gets遠小於NL的方案。

Execution Plan的正確路線有了，下面的問題是怎樣讓Oracle自己適時的拿來用---給出最正確的Statistics。

找OLTP那邊的程式負責人瞭解到他們的Package/Procedure/Function等等都沒有用到COA_SN_D4這個Index上的Column, 那就放心的蒐集Histogram了。

Gather完該Index上3Columns的Histogram( size skewonly)後，Oracle果然在特定的COA_ITEM值上選擇了Hash_join.

整個Report的執行時間得以大大縮短。