Oracle 12c數據庫優化器統計信息收集的最佳實踐（三）｜何時不需要收集統計信息

原文鏈接 http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf

譯者  沃趣科技 胡紅偉

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

不穩定的表

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

如果你依賴于自動收集統計信息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