案例4　“月底難過”

1.案例說明

某大型電商公司數(shù)據(jù)倉庫系統(tǒng)經(jīng)常出現(xiàn)在月底運行緩慢的情況，但在平時系統(tǒng)運行卻非常正常。這是因為月底往往有月報等大批量作業(yè)運行，而就在這個時間點上，常常會出現(xiàn)緩慢情況，因此業(yè)務(wù)人員一到月底就非常緊張。這也成了一個老大難問題，困擾了很長時間。

DBA介入處理，發(fā)現(xiàn)一個很奇怪的現(xiàn)象：某條主要SQL是造成執(zhí)行緩慢的主因，其執(zhí)行計劃是不確定的，也就是說因為執(zhí)行計劃的改變，導(dǎo)致其運行效率不同。而往往較差的執(zhí)行計劃發(fā)生在月底幾天，且由于月底大批作業(yè)的影響，整體性能比較飽和，更突顯了這個問題。針對某個出現(xiàn)問題的時間段，做了進一步分析，結(jié)果表明是由于統(tǒng)計信息的缺失導(dǎo)致了優(yōu)化器產(chǎn)生了較差的執(zhí)行計劃，并據(jù)此指定了人工策略，徹底解決了這個問題。

（1）具體分析

先來看下面的代碼：

select...
from xxx a join xxx b on a.order_id = b.lyywzdid
left join xxx c on b.gysid = c.gysid
whereb.cdate>= to_date（'2012-03-31'， 'yyyy-mm-dd'） – 3 and ...
a.send_date>= to_date（'2012-03-31'， 'yyyy-mm-dd'） - 1 and a.send_date<to_date（'2012-03-31'， 'yyyy-mm-dd'）；
--------------------------------------------------------------------------------
|Id  | Operation          |Name  |  Rows  |  Bytes  | Cost （%CPU） |Pstart|Pstop|
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |      |      1 |     104 |      9743（1）|      |     |
|  1 |  HASH JOIN OUTER   |      |      1 |     104 |      9743（1）|      |     |
|  2 |   TABLE ACCESS BY LOCAL INDEX ROWID
                          | XXXX |      1 |      22 |         0（0）| 1189 | 1189|
|  3 |    NESTED LOOPS    |      |      1 |      94 |      9739（1）|      |     |
|  4 |     PARTITION RANGE ITERATOR    
                          |      |   1032 |   74304 |      9739（1）|  123 | 518 |
|  5 |      TABLE ACCESS FULL 
                          | XXXX |   1032 |   74304 |      9739（1）|  123 | 518 |
|  6 |     PARTITION RANGE SINGLE
                          |      |      1 |         |         0（0）| 1189 | 1189 |
|  7 |      INDEX RANGE SCAN 
                          | XXXX |      1 |         |         0（0）| 1189 | 1189 |
|  8 |   TABLE ACCESS FULL
                          | XXXX |    183 |    1830 |         3（0）|      |     |
--------------------------------------------------------------------------------

執(zhí)行計劃中，多表關(guān)聯(lián)私用了嵌套循環(huán)，這點對于OLAP系統(tǒng)來說是比較少見的。一般優(yōu)化器更傾向于使用SM和HJ。進一步檢查發(fā)現(xiàn)其成本竟然是0，怪不得優(yōu)化器使用了嵌套循環(huán)。

（2）深入分析

檢查發(fā)現(xiàn)索引數(shù)據(jù)統(tǒng)計信息異常，這是分區(qū)索引，僅兩天的分區(qū)統(tǒng)計信息都是0。導(dǎo)致優(yōu)化器認為嵌套循環(huán)的執(zhí)行效率更高，而不是使用哈希連接。結(jié)合業(yè)務(wù)發(fā)現(xiàn)，月底是業(yè)務(wù)高峰期，對于系統(tǒng)統(tǒng)計信息的作業(yè)收集，在指定的時間窗口內(nèi)無法完成。最后導(dǎo)致統(tǒng)計信息不完整，優(yōu)化器采用了錯誤的執(zhí)行計劃。

（3）解決方法

解決的代碼如下：

exec dbms_stats.gather_index_stats（
       ownname=>'xxx'，
       indname=>'xxx'，
       partname=>'PART_xxx'，
       estimate_percent => 10）；

分析完對象的統(tǒng)計信息即恢復(fù)正常。

2.給我們的啟示

• 統(tǒng)計信息是優(yōu)化器優(yōu)化的重要參考依據(jù)，一個完整、準確的統(tǒng)計信息是必要條件。往往在優(yōu)化過程中，第一步就是查看相關(guān)對象的統(tǒng)計信息。
• 分區(qū)機制是Oracle針對大數(shù)據(jù)的重要解決手段，但其也很容易造成所謂“放大效應(yīng)”。即對于普通表而言，統(tǒng)計信息更新不及時可能不會導(dǎo)致執(zhí)行計劃偏差過大；但對于分區(qū)表、索引來說，很容易出現(xiàn)因更新不及時出現(xiàn)0的情況，進而導(dǎo)致執(zhí)行計劃產(chǎn)生嚴重偏差。