【DB筆試面試645】在Oracle中，當收集表的統計資訊時應該注意哪些問題？

• 2019 年 10 月 10 日
• 筆記

題目部分

在Oracle中，當收集表的統計資訊時應該注意哪些問題？

♣

答案部分

關於收集統計資訊需要注意以下幾點：

①　對於數據量不大的OLTP類型的系統，建議使用自動收集統計資訊，並對一些特殊的大表寫JOB定時收集統計資訊。如果是數據量很大的OLAP或者DSS系統，那麼建議DBA自己寫JOB腳本來收集統計資訊。

②　在導入大量數據後應及時收集統計資訊後才能進行相關的後續業務處理（包括查詢和修改），否則可能會由於實際數據量和統計資訊里記錄的數據量存在巨大差異而導致CBO選擇錯誤的執行計劃。

③　全局臨時表默認不能收集統計資訊，在生成執行計劃時採用動態取樣比較好。

④　對於某些新上線或新遷移的系統，建議進行全庫收集一次統計資訊。

⑤　建議及時對包含日期型欄位的表收集統計資訊，避免出現謂詞越界現象。

⑥　統計資訊收集作業取樣比例：對於Oracle 11g及其以上的版本收集統計資訊的取樣比例建議採用DBMS_STATS.AUTO_SAMPLE SIZE。如果是Oracle 10g，那麼建議將取樣比例的初始值設為30％，然後根據目標SQL的實際執行情況再做調整。

⑦　系統統計資訊：如果系統的硬體環境發生了變化，那麼建議要額外收集一次系統統計資訊。

⑧　內部對象統計資訊：在明確診斷出系統已有的性能問題是因為X$表的內部對象統計資訊不準引起的，這個時候就應該收集X$表的內部對象統計資訊，其它情形就不要收集了。

⑨　表的大小、是否並行：若表很大，而系統空閑，則可以使用並行來收集統計資訊。

⑩　表是否分區：若是分區表則建議收集全局的統計資訊並且收集數據量有變更的單個分區（加GRANULARITY和參數並設置屬性INCREMENTAL）的統計資訊。

⑪　是否收集索引的統計資訊：一般情況下都應該收集索引的統計資訊。

⑫　是否收集直方圖。對直方圖統計資訊的收集策略是對已經存在直方圖統計資訊的列才收集直方圖統計資訊，而目標列的初次直方圖統計資訊則是由了解系統的DBA手工來收集直方圖。設置METHOD_OPT的值為「FOR ALL COLUMNS SIZE REPEAT」。

⑬　是否可以並發收集統計資訊：若系統有很多小表，則可以考慮並發收集統計資訊。

⑭　系統的負載情況：在手動收集統計資訊的時候需要注意系統的負載情況。

⑮　預估多久可以收集完成：對OLAP系統的大表而言，根據平時收集統計資訊的經驗要預估出收集統計資訊要花費多長的時間。

⑯　基於資料庫、SCHEMA或是表級別：根據情況判斷是否有必要在資料庫或SCHEMA級別來收集統計資訊。

⑰　是否需要收集擴展列的統計資訊。如果表中的數據傾斜度較大，那麼收集直方圖能最大程度的幫助優化器計算出準確的Cardinality，從而避免產生差的執行計劃；再進一步，如果存在傾斜的多個列共同構成了Predicate里的等值連接且這些列間存在較強的列相關性的話，那麼生成帶有直方圖的多列統計資訊是一個上佳的選擇，能夠最大程度的幫助優化器準確預測出Cardinality。

⑱　是否設置NO_INVALIDATE為FALSE。該選項有TRUE、FALSE和DBMS_STATS.AUTO_INVALIDATE這3個值。如果取值為TRUE，那麼表示收集統計資訊後不進行游標失效動作，原有的Shared Cursor保持原有狀態。如果取值為FALSE，那麼表示將統計資訊對象相關的所有Cursor全部失效，目標SQL語句在下次執行時就會使用硬解析。如果設置為AUTO_INVALIDATE，那麼Oracle自己決定Shared Cursor失效動作，當SQL再次執行時間距離上次收集統計資訊的時間超過5小時（隱含參數「_OPTIMIZER_INVALIDATION_PERIOD」決定）則對SQL重新做硬解析。AUTO_INVALIDATE為默認選項。有些DBA在收集統計資訊時，沒有使用NO_INVALIDATE=>FALSE選項，所以，即使收集了統計資訊，執行計劃也不會立即改變。可以在表級別設置讓所有依賴於該表的游標不失效，設置方法為：

EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','NO_INVALIDATE','TRUE');--在收集SH.SALES表上的統計資訊時，讓所有依賴於該表的游標不失效  

⑲　對於OLTP類型的資料庫，需要特別關注DML比較頻繁的以及數據載入比較大的表及分區表。

⑳　檢查是否有臨近統計資訊收集窗口的數據載入工作，如果有，是否能在資料庫統計資訊的窗口時間完成，如果不能在窗口時間完成，那麼應該針對這段時間載入的數據，特別是大量的數據，在相關載入腳本完成之後，加入統計資訊的收集。

21　如果載入數據量比較大，並且是分區表，每個分區的業務數據呈現的是均勻的，在Oracle 11g可以考慮採用DBMS_STATS.COPY_TABLE_STATS先把統計資訊做個快速的設置，然後，再收集該分區的統計資訊。

其實，上述幾點是沒有一個普適性的標準答案的，因為不同的系統其數據量、數據分布情況都不盡相同，甚至可能會有很大的區別，所以適合於某套系統的統計資訊收集策略並不一定能適用於另外一套系統。收集統計資訊總的原則就是量體裁衣，即要找到適合自己系統的統計資訊收集策略，用盡量小的代價收集到能穩定跑出正確執行計劃的統計資訊即可，也就是說收集到的統計資訊不一定要特別准，只要具備代表性，能穩定跑出正確的執行計劃就可以了。

本文選自《Oracle程式設計師面試筆試寶典》，作者：小麥苗