适当采用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的执行时间得以大大缩短。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10856805/viewspace-1021131/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10856805/viewspace-1021131/