Oracle 12c資料庫優化器統計資訊收集的最佳實踐（二）

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

譯者  沃趣科技 楊禹航

何時收集統計資訊

為了選擇最佳執行計劃，優化器必須可以獲得有代表性的統計資訊。有代表性的統計資料不必是最新的，而是一組能夠幫助優化器確定執行計劃中每個操作所能返回的行數。

自動統計資訊收集任務

Oracle會在預定義維護視窗期間 (工作日10pm 到2am 和週末6am 到2am )收集資料庫中所有缺失統計資訊或者統計資訊過期物件的統計資訊，您可以在Oracle企業管理器或使用DBMS_SCHEDULER和DBMS_AUTO_TASK_ADMIN軟體包來更改維護視窗。

圖3: 更改自動收集統計資訊作業執行時間的維護視窗

如果你已經有一個行之有效的統計收集程式，或者您想要禁用自動收集統計資訊任務，您可以使用如下命令:

begin

dbms_auto_task_admin.disable(

client_name=>'auto optimizer stats collection',

operation=>null,

window_name=>null);

end;

/

手工統計收集

如果您打算手動維護優化器的統計資訊，則需要確定何時去收集統計資訊。基於過期的資訊您可以決定什麼時候統計資訊應該被收集，您可以根據統計資訊的失效性確定何時收集統計資訊，就像自動作業一樣，或者基於您的環境中新資料載入的時間。如果基本的資料沒有明顯變化，則不建議重新收集統計資訊，因為這將不必要地浪費系統資源。

如果資料僅在預先定義的 ETL 或 ELT 作業期間載入到您的環境中, 則可以將統計資訊收集操作安排為此過程的一部分。

線上收集統計資訊

在Oracle Database 12 c中，線上收集統計資訊"piggybacks"作為直接路徑資料載入操作的一部分進行收集, 例如, 像使用CTAS的方式建立表，以及IAS方式插入資料。收集統計資料作為資料載入操作的一部分，這意味著不需要額外的全表掃描，就可以在載入資料後立即提供統計資訊。

圖4:線上收集統計資訊為新建立的sales2表提供表和列的統計資訊收集

線上收集統計資訊並不會收集直方圖和索引統計資訊，因為這些種類的統計資訊需要額外的資料掃描，這可能在資料載入時對效能產生較大影響。

如果要收集直方圖或者索引的統計資訊，而不重新收集列的基本統計資訊， 請使用 DBMS_STATS.GATHER_TABLE_STATS 中新的選擇”OPTIONS”引數設定成GATHER AUTO。

請注意，出於效能原因，GATHER AUTO在生成直方圖時，使用行中的樣本資料而不是表中的所有資料。

 圖5 : 設定選項為GATHER AUTO來建立 SALES2 表上的直方圖, 而不涉及基本統計資訊

注意列“HISTOGRAM_ONLY”指示在不重新收集列基本統計資訊的情況下收集了直方圖。有兩種方法確認線上收集統計資訊是否發生: 一種方式是檢查執行計劃，檢視執行計劃中是否出現”OPTIMIZER STATISTICS GATHERING”，另外一種方式是檢視USER_TAB_COL_STATISTICS表中notes欄位的狀態是否為stats_on_load。

                         圖6:線上統計資訊收集操作的執行計劃

線上統計資訊收集為了減少對直接路徑載入時效能上的影響，只有在被載入的物件為空時線上統計資訊收集才會被觸發。要確保在載入現有表的新分割槽時進行線上收集統計資訊，請使用擴充套件語法明確指定分割槽。在這種情況下, 將建立分割槽級別統計資訊, 但不會更新全域性級別 (表級別) 統計資訊。如果在分割槽表上啟用增量統計資訊，則會在資料載入操作中建立”synopsis”。

可以使用HINT: NO_GATHER_OPTIMIZER_STATISTICS來禁用單條SQL語句去使用線上收集統計資訊。

增量統計和分割槽交換資料載入

對分割槽表的統計資訊收集包括表級別(global)和(sub)分割槽級別的統計資訊。如果分割槽表的”incremental”首選項設定為true，則DBMS_STATS.GATHER_*_STATS引數中GRANULARITY包含global，ESTIMATE_PERCENT設定成AUTO_SAMPLE_SIZE，Oracle將只掃描那些新增或修改的分割槽，而不是整個表，從而準確的得出所有全域性級別的統計資訊。增量全域性統計資訊通過儲存表中每個分割槽的“synopsis”來工作，“synopsis”是用於該分割槽和分割槽中的列的統計後設資料，從而消除了掃描整個表的需要。將分割槽級別統計資訊和每個分割槽的“synopses”聚合，將準確地生成全域性統計資訊，從而無需掃描整個表。

當新分割槽新增到表中時，您只需要收集新分割槽的統計資訊，使用新的分割槽“synopsis”和現有分割槽的“synopses”, 將自動、準確地計算表級別的統計資訊。

請注意，當啟用增量統計資訊時，分割槽統計資訊不會從子分割槽統計資訊進行聚合計算。

如果您正使用分割槽交換並希望利用增量統計資訊，則需要在非分割槽表上設定DBMS_STATS首選項INCREMENTAL_LEVEL，以確定它將在分割槽交換過程中使用。設定INCREMENTAL_LEVEL為TABLE，當在其上收集統計資訊，Oracle會自動建立“synopsis”，此表級的“synopsis”在分割槽交換後會變成分割槽級別的“synopsis”。

但是，如果一天當中在您的環境存在很多插入少量資料的線上事務，您需要確定統計資料何時過期，然後觸發自動統計資料收集任務。

如果您計劃依賴user _ tab _ statistics中的stale_stats列來確定統計資訊是否過期，則應注意此資訊僅按每日更新。

如果您需要及時地瞭解在您的表上發生了哪些DML, 您需要檢視USER_TAB_MODIFICATIONS 表, 其中列出了在每個表上發生的INSERT、UPDATE和DELETE的數量, 該表是否已被TRUNCATED (TRUNCATE column) 並自行計算是否過期。

再次，您應該注意到此資訊是自動從記憶體中更新而來，如果需要最新的資訊，則需要使用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO來手動重新整理資訊。

防止“超出範圍”條件

無論您使用自動統計資訊收集任務還是手動收集統計資訊，如果終端使用者在收集統計資訊之前開始查詢新插入的資料，即使表中不到10%的行被更改，也可能會由於陳舊的統計資訊而獲得次優執行計劃，其中最常見的情況之一發生在當 where 子句謂詞中提供的值超出 [最小、最大] 列統計的值域時，就會發生這種情況。這通常被稱為“範圍外”錯誤。在這種情況下，優化器根據謂詞值之間的距離和最大值(假設值高於最大值)對選擇性進行按比例分配，即，最大值或最小值，從而降低選擇性。

此場景與範圍分割槽表非常常見。一個新的分割槽被新增到一個現有的範圍分割槽表中，行被插入到這個分割槽中。在此新分割槽收集統計資訊之前使用者開始查詢此新資料。對於分割槽表，可以使用DBMS_STATS.COPY_TABLE_STATS(從Oracle資料庫10.2.0.4開始)，以防止“超出範圍”條件。此過程將具有代表性的源 [sub] 分割槽的統計資訊複製到新建立的和空的目標 [子] 分割槽中。

它還會拷貝相關物件的統計資訊:列、本地(分割槽)索引等。並將最高界限值作為分割槽列的最大值和前一個分割槽的最高界限值作為該分割槽列的最小值。

拷貝的統計資訊應該僅視為臨時解決方案，直到可以收集分割槽的準確統計資訊。不應該將拷貝的統計資訊用作實際收集統計資訊的替代方法。

預設情況下，DBMS_STATS.COPY_TABLE_STATS僅用來調整分割槽統計資訊，而不是全域性或表級別統計資訊。如果希望將分割槽列的全域性級別統計資訊作為副本的一部分進行更新, 則需要將 DBMS_STATS.COPY_TABLE_STATS 的引數設定為8。

對於非分割槽表，可以使用DBMS_STATS.SET_COLUMN_STATS手動設定列的最大值。但是 一般不建議使用這種方法來代替實際收集統計資訊。

提高統計資訊質量

良好質量的統計資訊對生成最佳的SQL執行計劃是至關重要的，但是有時統計資訊的質量很差，而這種情況可能會被忽視。例如，“通過繼承得到的”系統可能使用被資料庫管理員所不能理解的指令碼，或者可以理解但是卻不願意更改它們。但是，由於Oracle不斷提高統計資料收集功能，因此可能忽略最佳做法和建議。出於這些原因，Oracle資料庫12cR2包含了一個新的優化統計顧問，來幫助您提高資料庫中的統計資訊的質量。該優化統計顧問分析資料字典中的資訊，評估統計資料的質量，並發現如何收集統計資訊。它將報告質量較差和缺失的統計數字，並提出解決這些問題的建議。

其操作的原則是應用最佳實踐規則來發現潛在的問題。這些問題作為一系列結果被報告，反過來又可以產生具體的建議。這些建議可以通過操作自動實現(立即或通過自動生成的指令碼由資料庫管理員執行)。

圖7: 優化統計顧問

優化統計顧問任務是在維護視窗中自動執行，但也可以按需執行。可以隨時檢視由優化統計顧問生成的html或文字報告，並且可以隨時執行進行操作。圖8舉例說明了一個特定規則的示例，它引出了查詢、建議和解決問題的操作：

圖8:規則、查詢、建議和操作的示例。

優化統計顧問收集並將資訊儲存在資料字典中。它的效能開銷較低，因為它對收集的統計資訊 (已經儲存在資料字典中) 進行分析，並且不對已經儲存在應用 Schema中物件的統計資訊進行二次分析。

圖9:讀取資料字典，通過過濾器執行任務並儲存結果

任務完成後，可以用html或文字格式生成報告，也可以建立操作( SQL)指令碼。

圖10:報告advisor任務和生成操作SQL指令碼。

檢視自動化任務生成的報表非常簡單：

select dbms_stats.report_advisor_task('auto_stats_advisor_task') as report from dual;

或者，具有使用顧問許可權的使用者可以手動執行任務，並使用以下三個步驟報告結果:

DECLARE

tname VARCHAR2(32767) := 'demo'; -- task name

BEGIN

tname := dbms_stats.create_advisor_task(tname);

END;

/

DECLARE

tname VARCHAR2(32767) := 'demo'; -- task name

ename VARCHAR2(32767) := NULL; -- execute name

BEGIN

ename := dbms_stats.execute_advisor_task(tname);

END;

/

SELECT dbms_stats.report_advisor_task('demo') AS report

FROM dual;

優化統計顧問生成的操作可以立即實施:

DECLARE

tname VARCHAR2 (32767) := 'demo'; -- task name

impl_result CLOB; -- report of

implementation

BEGIN

impl_result := dbms_stats.implement_advisor_task(tname);

END;

/

此外, Oracle12c Real Application Testing還包括實用的效能保證功能，如 SQL 效能顧問快速檢查。見 Oracle 白皮書，《Database 12c Real Application Testing Overview》有關更多詳細資訊 (請參見21頁上的參考資料)。

快速收集統計資訊

隨著資料的增長和維護視窗縮減，及時收集統計資訊比以往任何時候都更重要。Oracle提供了各種加速統計資料收集的方法，從並行化統計資訊收集到生成統計資訊而不是收集統計資訊。

使用並行法

可以通過幾種方式利用並行性來進行統計收集

? 使用DEGREE引數

? 併發統計收集

? 結合DEGREE和併發收集

使用並行引數

DBMS_STATS中的”DEGREE”引數用於控制收集統計資訊時並行執行程式的數量。預設情況下，Oracle使用與資料字典中表的屬性(並行度)指定的相同數量的並行伺服器程式。Oracle資料庫中的所有具有此屬性的表都預設設定為1，為了加快統計資訊的收集，可以在對大表進行收集統計資訊時顯示指定該引數，或者你可以設定degree為auto_degree；Oracle將根據物件的大小自動確定應該用於收集統計資訊的適當的並行伺服器程式數量。該值可以介於 1 (序列執行)(小物件) 到 DEFAULT_DEGREE (PARALLEL_THREADS_PER_CPU XCPU_COUNT) (較大的物件)。

圖11: 通過DEGREE引數使用並行性

您應該注意，設定分割槽表的DEGREE意味著多個並行伺服器程式將用於收集每個分割槽的統計資訊，但不會在不同分割槽上同時收集統計資訊。統計資訊將在每個分割槽上收集一次。 

併發統計收集

併發統計資訊收集功能可以在 Schema (或Database) 中的多個表上和多個 (子) 分割槽中併發的進行統計資訊的收集。通過讓Oracle充分利用多處理器環境，同時去收集多個表和(sub)分割槽的統計資訊可以減少收集統計資料所需的總體時間。

併發統計資訊收集是由全域性選項CONCURRENT控制，可以設定MANUAL,AUTOMATIC, ALL, OFF，預設為OFF。當CONCURRENT被啟用時，Oracle使用作業排程器和高階佇列元件來建立和管理多個統計資訊收集作業併發的執行。

呼叫DBMS_STATS.GATHER_TABLE_STATS在分割槽表上, 當 CONCURRENT 設定為MANUAL時，Oracle會為表中的每個(sub)分割槽建立單獨的統計資訊收集作業。其中有多少個作業併發執行，多少作業在佇列中排隊，是根據可用作業佇列的程式數量 (JOB_QUEUE_PROCESSES 初始化引數、RAC 環境中的每個節點)和可用的系統資源來決定的。隨著正在執行的作業完成，更多的作業將出現並執行，直到所有（子）分割槽都收集了其統計資訊。

如果您使用DBMS_STATS.GATHER_DATABASE_STATS、DBMS_STATS.GATHER_SCHEMA_STATS或者DBMS_STATS.GATHER_DICTIONARY_STATS收集統計資訊，那麼Oracle將為每個非分割槽表以及分割槽表的每個（子）分割槽建立一個單獨的統計資訊收集作業。每個分割槽表還將有一個協調作業，用於管理其(sub)分割槽作業。然後，資料庫將執行儘可能多的併發作業，並對其餘作業進行排隊，直到作業執行完成。但是，為了防止可能的死鎖情況，不能同時處理多個分割槽表。因此, 如果已為已分割槽表執行了某些作業, 則Schema (或資料庫或字典) 中的其他分割槽表將排入佇列, 直到當前作業完成。對非分割槽表則沒有這種限制。

圖12顯示了DBMS_STATS.GATHER_SCHEMA_STATS在不同級別建立作業。在Schema:sh上Oracle將為每個非分割槽表建立一個統計收集作業(圖12中的級別1)；      

? CHANNELS

? COUNTRIES

? TIMES

Oracle將為每個分割槽表建立一個協調作業: SALES和COSTS，然後分別為SALES和COSTS表中的每個分割槽建立一個統計資料收集作業(圖12中的級別2)。  

圖12:在sh上的併發統計資訊收集時的作業列表

如果指定了DEGREE引數，每個單獨的統計資料收集作業也可以利用並行執行。如果表、分割槽表或子分割槽表非常小或為空，則Oracle可以自動將物件與其他小物件合成到一個單獨作業中，以減少作業維護的開銷。

配置併發統計資料收集

預設情況下，統計資料收集的併發設定關閉。它可以按照如下操作開啟:

exec dbms_stats.set_global_prefs('concurrent', 'all')

您還需要一些額外的特權和收集統計資訊所需的常規許可權。使用者必須具有以下Job Scheduler和AQ許可權:

? CREATE JOB

? MANAGE SCHEDULER

? MANAGE ANY QUEUE

sysaux表空間應該處於線上狀態，因為作業程式在sysaux表空間中儲存其內部表和檢視。最後，JOB_QUEUE_PROCESSES引數應該設定為充分利用可用於統計收集過程的所有系統資源。如果您不計劃使用並行執行，則應將job _ queue _process設定為2*CPU核心總數(在RAC環境中為每個節點)。請確保在系統級別設定此引數 ( alter system...或在init.ora檔案中)而不是在會話級別上( alter session)設定。

如果要將並行執行作為併發統計資訊收集的一部分，則應該禁用並行自適應多使用者:

ALTER SYSTEM SET parallel_adaptive_multi_user=false;

資源管理器也必須被啟用，例如:

ALTER SYSTEM SET resource_manager_plan = 'DEFAULT_PLAN';

還建議啟用並行語句佇列。這需要啟用資源管理器，並建立臨時資源計劃，並且其中的消費者組“others_groups”已啟用。

預設情況下，資源管理器僅在維護視窗期間啟用。以下指令碼說明了建立臨時資源計劃( pqq _ test)的一種方法，並使資源管理器能夠執行此計劃。

-- connect as a user with dba privileges

begin

dbms_resource_manager.create_pending_area();

dbms_resource_manager.create_plan('pqq_test', 'pqq_test');

dbms_resource_manager.create_plan_directive(

'pqq_test',

'OTHER_GROUPS',

'OTHER_GROUPS directive for pqq',

parallel_target_percentage => 90);

dbms_resource_manager.submit_pending_area();

end;

/

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'pqq_test' SID='*';

如果希望自動統計資料收集任務利用併發性，請將CONCURRENT設定為AUTOMATIC或ALL。在維護視窗中使用的資源管理器計劃中新增了一個新的 AUTOTASK $ 使用者組, 以確保併發統計資訊的收集不會使用太多的系統資源。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2144401/，如需轉載，請註明出處，否則將追究法律責任。