Oracle 12c資料庫最佳化器統計資訊收集的最佳實踐（三）｜何時不需要收集統計資訊

原文連結 

譯者  沃趣科技 胡紅偉

雖然最佳化器需要準確的統計資訊來選擇最優的執行計劃，但是有些場景下，收集統計資訊比較困難，或消耗資源較高，或收集統計資訊不能及時完成，那麼就需要另一種備選策略。

不穩定的表

不穩定的表即隨著時間的變化，資料會發生巨大變化的表。例如，一個訂單佇列表，一天的開始它是空的，隨著時間推移，訂單會填滿這個表，一旦某一訂單被處理又會從這個訂單表中刪除，一天的結尾表又會變為空。

如果你依賴於自動收集統計資訊job來維護這類表的統計資訊，那麼統計資訊會經常顯示此表為空。因為晚上此表是空的，而收集統計資訊的job也正是晚上才開始執行。然而，在白天的過程中，這個表可能有成百上千條記錄。 

在這種情況下，最好在表被填充時收集一組有的代表性的統計資訊，並鎖住。鎖住統計資訊會阻止自動收集的統計資訊覆蓋他們。另外，你可以依賴於動態取樣來收集這些表的統計資訊。最佳化器在最佳化一個語句之前編譯sql語句的時候會使用動態取樣來收集表的基本統計資訊。儘管動態取樣收集的統計資訊沒有完全由DBMS_STATS包收集的統計資訊質量高，但在大多數情況下他們已經足夠好了。

全域性臨時表

在應用程式上下文中，全域性臨時表經常被用於儲存中間結果。全域性臨時表在系統級別與具有適當許可權的所有使用者共享其定義，但裡面的資料內容在會話之間是相互獨立和私有的。針對此表，直到有資料插入時才會分配物理儲存。

一個全域性臨時表可以是事務特定的（提交時刪除行記錄），也可以是會話特定的（提交時保留行記錄）。收集事務特定表的統計資訊會導致此表被清空。相反，收集一個全域性臨時表的統計資訊是可能的（會保留行記錄），但是在之前的資料庫版本這不是一個好方案，因為使用全域性臨時表的所有會話不得不共用同一組統計資訊，以致於很多系統依賴於動態取樣的統計資訊。

然而，在oracle 12c版本，現在可以實現每個使用全域性臨時表的會話擁有自己獨立的統計資訊。全域性臨時表上的統計資訊是否共享取決於DBMS_STATS包的一個新選項GLOBAL_TEMP_TABLE_STATS。預設情況此選項設定為會話，即每個使用全域性臨時表的會話都有自己獨立的統計資訊。最佳化器會首先使用會話的統計資訊，如果會話統計資訊不存在，才會使用共享的統計資訊。

圖13：改變預設方式：從全域性臨時表不共享統計資訊到共享統計資訊 

如果你是從11g升級到12c,但資料庫應用沒有被修改去利用全域性臨時表的會話統計資訊，你可能需要保持全域性臨時表預設的方式與升級之前一致，透過設定DBMS_STATS的GLOBAL_TEMP_TABLE_STATS選項為共享模式（或者至少等到應用被升級）。

當使用直接路徑的方式填充一個全域性臨時表（提交時保留行記錄）時，線上統計資訊收集會自動建立會話級別的統計資訊，這將減少執行額外統計資訊收集的必要性，也不會影響其他會話的統計資訊。

圖14：使用直接路徑方式填充一個全域性臨時表會導致會話級別的統計資訊被自動收集

中間表

中間表通常被看做一個ETL程式或一個複雜事務的一部分。這些表只被寫一次，讀一次，然後被清空或刪除。在這種情況下收集統計資訊的成本大於好處，因為統計資訊只被使用一次。反倒是動態取樣應該用於這些場景。建議你鎖住這些中間表上的統計資訊以防止自動統計資訊收集任務再次對他們收集統計資訊。

收集其他型別的統計資訊

自從基於成本的最佳化器是現在唯一被支援的最佳化器，資料庫中所有的表需要有統計資訊，包括所有的字典表（owner是sys、system等等，且位於system、sysaux表空間中的表），以及動態效能檢視使用的x$表。

資料字典統計資訊

資料字典表上的統計資訊是透過執行在夜間維護視窗上的自動統計資訊收集任務來維護的。強烈建議你允許自動統計資訊收集任務來維護資料字典統計資訊，即使你關掉主要應用賬戶上的自動統計資訊收集job。你可以使用DBMS_STATS.SET_GLOBAL_PREFS儲存過程修改AUTOSTATS_TARGET的值為ORACLE，以代替AUTO，來這樣做。

exec dbms_stats.set_global_prefs('autostats_target','oracle')

內部物件統計資訊

從oracle資料庫12c開始，內部物件統計資訊如果之前沒有被收集過，那麼它就會被自動統計資訊收集任務收集。在此版本之前，資料庫是不會收集內部物件統計資訊的。不像其他的資料庫表，當統計資訊缺失時動態取樣不會自動應用於包含x$表的sql語句，此時最佳化器會使用預定義的統計資訊預設值。這些預設值可能沒有代表性，可能會導致非最優的執行計劃，這可能會導致嚴重的效能問題，正是因為這個原因，我們強烈建議你手動收集內部物件統計資訊。

你可以使用DBMS_STATS.GATHER_FIXED_OBJECTS_STATS儲存過程收集內部物件統計資訊。因為x$表的瞬態性質，重要的是在系統有一定代表性負載時收集內部物件統計資訊。在大型系統中，這並不總是可行的，因為收集統計資訊需要佔用額外的資源。你不能在系統高峰負荷時收集內部物件統計資訊，你應該在系統預熱之後，三種重要型別的內部物件表被填充時收集內部物件統計資訊。

• 結構資料 例如，涵蓋資料檔案，控制檔案內容的檢視等

• 基於會話的資料 例如，v$session, v$access 等

• 工作負載資料 例如，v$sql, v$sql_plan 等

如果你做了一個重大的資料庫或應用的升級，或實現一個新的模組，或改變資料庫的配置，強烈建議你重新收集內部物件統計資訊。例如，你增加了SGA的大小，包含buffer cache和shared pool資訊的所有x$表可能變化很大。例如用於v$buffer_pool或v$shared_pool_advice的x$表。

系統統計資訊

系統統計資訊使得最佳化器能更準確的計算執行計劃中每一步操作的成本，透過使用實際硬體系統執行sql的資訊，例如CPU的速度和IO的效能。

系統統計資訊是預設開啟的，會以預設值自動初始化，這些值對於大多數系統是有代表性的。

總  結

為了使oracle最佳化器準確地確定執行計劃的成本，那麼sql語句中涉及到的全部物件（表和索引）必須有準確的統計資訊，且必須有準確的系統統計資訊。這兩部分白皮書系列詳細地解釋了什麼統計資訊是必要的，以及這些統計資訊怎麼被使用，以及不同的統計資訊收集方法。

透過自動統計資訊收集任務和此白皮書中描述的其他技術手段的組合使用，一個DBA可以為他們的環境維護一組準確的統計資訊，以確保最佳化器得到必要的資訊去選擇一個最優的執行計劃。一旦一個統計資訊收集策略被實施，如果要改變策略，必須要在一個可控的方式下進行，並利用關鍵的特性例如待定統計資訊以確保對應用的效能沒有不良的影響。

參考文獻

Oracle白皮書：Understanding Optimizer Statistics with Oracle Database 12c Release 2

Oracle白皮書：Optimizer with Oracle Database 12c Release 2

Oracle白皮書：Database 12c Real Application Testing Overview