Oracle 12c數(shù)據(jù)庫優(yōu)化器統(tǒng)計信息收集的最佳實踐（三）｜何時不需要收集統(tǒng)計信息

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

譯者  沃趣科技 胡紅偉

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

不穩(wěn)定的表

不穩(wěn)定的表即隨著時間的變化，數(shù)據(jù)會發(fā)生巨大變化的表。例如，一個訂單隊列表，一天的開始它是空的，隨著時間推移，訂單會填滿這個表，一旦某一訂單被處理又會從這個訂單表中刪除，一天的結(jié)尾表又會變?yōu)榭铡?

如果你依賴于自動收集統(tǒng)計信息job來維護這類表的統(tǒng)計信息，那么統(tǒng)計信息會經(jīng)常顯示此表為空。因為晚上此表是空的，而收集統(tǒng)計信息的job也正是晚上才開始執(zhí)行。然而，在白天的過程中，這個表可能有成百上千條記錄。 

在這種情況下，最好在表被填充時收集一組有的代表性的統(tǒng)計信息，并鎖住。鎖住統(tǒng)計信息會阻止自動收集的統(tǒng)計信息覆蓋他們。另外，你可以依賴于動態(tài)采樣來收集這些表的統(tǒng)計信息。優(yōu)化器在優(yōu)化一個語句之前編譯sql語句的時候會使用動態(tài)采樣來收集表的基本統(tǒng)計信息。盡管動態(tài)采樣收集的統(tǒng)計信息沒有完全由DBMS_STATS包收集的統(tǒng)計信息質(zhì)量高，但在大多數(shù)情況下他們已經(jīng)足夠好了。

全局臨時表

在應(yīng)用程序上下文中，全局臨時表經(jīng)常被用于存儲中間結(jié)果。全局臨時表在系統(tǒng)級別與具有適當權(quán)限的所有用戶共享其定義，但里面的數(shù)據(jù)內(nèi)容在會話之間是相互獨立和私有的。針對此表，直到有數(shù)據(jù)插入時才會分配物理存儲。

一個全局臨時表可以是事務(wù)特定的（提交時刪除行記錄），也可以是會話特定的（提交時保留行記錄）。收集事務(wù)特定表的統(tǒng)計信息會導(dǎo)致此表被清空。相反，收集一個全局臨時表的統(tǒng)計信息是可能的（會保留行記錄），但是在之前的數(shù)據(jù)庫版本這不是一個好方案，因為使用全局臨時表的所有會話不得不共用同一組統(tǒng)計信息，以致于很多系統(tǒng)依賴于動態(tài)采樣的統(tǒng)計信息。

然而，在oracle 12c版本，現(xiàn)在可以實現(xiàn)每個使用全局臨時表的會話擁有自己獨立的統(tǒng)計信息。全局臨時表上的統(tǒng)計信息是否共享取決于DBMS_STATS包的一個新選項GLOBAL_TEMP_TABLE_STATS。默認情況此選項設(shè)置為會話，即每個使用全局臨時表的會話都有自己獨立的統(tǒng)計信息。優(yōu)化器會首先使用會話的統(tǒng)計信息，如果會話統(tǒng)計信息不存在，才會使用共享的統(tǒng)計信息。

圖13：改變默認方式：從全局臨時表不共享統(tǒng)計信息到共享統(tǒng)計信息 

如果你是從11g升級到12c,但數(shù)據(jù)庫應(yīng)用沒有被修改去利用全局臨時表的會話統(tǒng)計信息，你可能需要保持全局臨時表默認的方式與升級之前一致，通過設(shè)置DBMS_STATS的GLOBAL_TEMP_TABLE_STATS選項為共享模式（或者至少等到應(yīng)用被升級）。

當使用直接路徑的方式填充一個全局臨時表（提交時保留行記錄）時，在線統(tǒng)計信息收集會自動創(chuàng)建會話級別的統(tǒng)計信息，這將減少運行額外統(tǒng)計信息收集的必要性，也不會影響其他會話的統(tǒng)計信息。

圖14：使用直接路徑方式填充一個全局臨時表會導(dǎo)致會話級別的統(tǒng)計信息被自動收集

中間表

中間表通常被看做一個ETL進程或一個復(fù)雜事務(wù)的一部分。這些表只被寫一次，讀一次，然后被清空或刪除。在這種情況下收集統(tǒng)計信息的成本大于好處，因為統(tǒng)計信息只被使用一次。反倒是動態(tài)采樣應(yīng)該用于這些場景。建議你鎖住這些中間表上的統(tǒng)計信息以防止自動統(tǒng)計信息收集任務(wù)再次對他們收集統(tǒng)計信息。

收集其他類型的統(tǒng)計信息

自從基于成本的優(yōu)化器是現(xiàn)在唯一被支持的優(yōu)化器，數(shù)據(jù)庫中所有的表需要有統(tǒng)計信息，包括所有的字典表（owner是sys、system等等，且位于system、sysaux表空間中的表），以及動態(tài)性能視圖使用的x$表。

數(shù)據(jù)字典統(tǒng)計信息

數(shù)據(jù)字典表上的統(tǒng)計信息是通過運行在夜間維護窗口上的自動統(tǒng)計信息收集任務(wù)來維護的。強烈建議你允許自動統(tǒng)計信息收集任務(wù)來維護數(shù)據(jù)字典統(tǒng)計信息，即使你關(guān)掉主要應(yīng)用賬戶上的自動統(tǒng)計信息收集job。你可以使用DBMS_STATS.SET_GLOBAL_PREFS存儲過程修改AUTOSTATS_TARGET的值為ORACLE，以代替AUTO，來這樣做。

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

內(nèi)部對象統(tǒng)計信息

從oracle數(shù)據(jù)庫12c開始，內(nèi)部對象統(tǒng)計信息如果之前沒有被收集過，那么它就會被自動統(tǒng)計信息收集任務(wù)收集。在此版本之前，數(shù)據(jù)庫是不會收集內(nèi)部對象統(tǒng)計信息的。不像其他的數(shù)據(jù)庫表，當統(tǒng)計信息缺失時動態(tài)采樣不會自動應(yīng)用于包含x$表的sql語句，此時優(yōu)化器會使用預(yù)定義的統(tǒng)計信息默認值。這些默認值可能沒有代表性，可能會導(dǎo)致非最優(yōu)的執(zhí)行計劃，這可能會導(dǎo)致嚴重的性能問題，正是因為這個原因，我們強烈建議你手動收集內(nèi)部對象統(tǒng)計信息。

你可以使用DBMS_STATS.GATHER_FIXED_OBJECTS_STATS存儲過程收集內(nèi)部對象統(tǒng)計信息。因為x$表的瞬態(tài)性質(zhì)，重要的是在系統(tǒng)有一定代表性負載時收集內(nèi)部對象統(tǒng)計信息。在大型系統(tǒng)中，這并不總是可行的，因為收集統(tǒng)計信息需要占用額外的資源。你不能在系統(tǒng)高峰負荷時收集內(nèi)部對象統(tǒng)計信息，你應(yīng)該在系統(tǒng)預(yù)熱之后，三種重要類型的內(nèi)部對象表被填充時收集內(nèi)部對象統(tǒng)計信息。

• 結(jié)構(gòu)數(shù)據(jù) 例如，涵蓋數(shù)據(jù)文件，控制文件內(nèi)容的視圖等

• 基于會話的數(shù)據(jù) 例如，v$session, v$access 等

• 工作負載數(shù)據(jù) 例如，v$sql, v$sql_plan 等

如果你做了一個重大的數(shù)據(jù)庫或應(yīng)用的升級，或?qū)崿F(xiàn)一個新的模塊，或改變數(shù)據(jù)庫的配置，強烈建議你重新收集內(nèi)部對象統(tǒng)計信息。例如，你增加了SGA的大小，包含buffer cache和shared pool信息的所有x$表可能變化很大。例如用于v$buffer_pool或v$shared_pool_advice的x$表。

系統(tǒng)統(tǒng)計信息

系統(tǒng)統(tǒng)計信息使得優(yōu)化器能更準確的計算執(zhí)行計劃中每一步操作的成本，通過使用實際硬件系統(tǒng)執(zhí)行sql的信息，例如CPU的速度和IO的性能。

系統(tǒng)統(tǒng)計信息是默認開啟的，會以默認值自動初始化，這些值對于大多數(shù)系統(tǒng)是有代表性的。

總  結(jié)

為了使oracle優(yōu)化器準確地確定執(zhí)行計劃的成本，那么sql語句中涉及到的全部對象（表和索引）必須有準確的統(tǒng)計信息，且必須有準確的系統(tǒng)統(tǒng)計信息。這兩部分白皮書系列詳細地解釋了什么統(tǒng)計信息是必要的，以及這些統(tǒng)計信息怎么被使用，以及不同的統(tǒng)計信息收集方法。

通過自動統(tǒng)計信息收集任務(wù)和此白皮書中描述的其他技術(shù)手段的組合使用，一個DBA可以為他們的環(huán)境維護一組準確的統(tǒng)計信息，以確保優(yōu)化器得到必要的信息去選擇一個最優(yōu)的執(zhí)行計劃。一旦一個統(tǒng)計信息收集策略被實施，如果要改變策略，必須要在一個可控的方式下進行，并利用關(guān)鍵的特性例如待定統(tǒng)計信息以確保對應(yīng)用的性能沒有不良的影響。

參考文獻

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