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

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

轉載自     沃趣科技（ID：woqutech） 

作者         劉金龍（譯）

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

導  語

Oracle優化器會為SQL語句產生所有可能的訪問路徑（執行計劃），然後從中選擇一條COST值最低的執行路徑，這個cost值是指oracle估算執行SQL所消耗的資源。為了讓優化器能夠精確計算的每一條執行計劃的COST值，這就需要被執行SQL語句所需訪問的所有物件（表和索引等）和系統有必要的描述資訊。

這些必要的資訊通常被稱為optimizer statistics（優化器統計資訊）。理解和管理優化器統計資訊是優化SQL執行的關鍵。知道何時、如何以及快速的方式收集優化器統計資訊對於維持系統良好效能是至關重要的。本文將詳細討論，在Oracle常見的場景中何時以及如何收集統計資訊，文章大致分如下幾個部分：

• 如何收集統計資訊

• 何時收集統計資訊

• 提高統計資訊質量

• 快速收集統計資訊

• 何時不用收集統計資訊

• 收集其他型別統計資訊

如何收集統計資訊

在Oracle中優選的方式是統計資訊自動收集。如果系統已經有完善的手動收集統計資訊程式，那麼可以優選手動統計資訊收集。無論選擇哪種收集方式，首先需要考慮的是預設的全域性引數設定是否滿足您的需求。

在大多數情況下這些預設引數是能夠滿足的，但是如果我們想根據自己的系統的實際情況作出修改，那麼我們可以通過設定SET_GLOBAL_PREFS.引數值。一旦我們選擇這樣做，我們可以通過使用DBMS_STATS“setpreference”工具覆蓋預設設定。例如，使用SET_TABLE_PREFS引數設定表統計資訊收集時使用incremental方式或者收集直方圖資訊。使用這種方式，我們將會指定哪些指定統計資訊被預設收集，而不需要在收集統計資訊的時候調整引數。我們可以自由的使用預設引數收集表/使用者/資料庫級別的統計資訊，並且確定這些統計資訊收集策略已經被使用。更重要的是，我們可以在自動和手動統計資訊收集之間自由切換。

自動統計資訊收集

oracle資料庫需要收集那些缺少或者已經“stale”過期統計資訊的物件統計資訊。這是在預定義的維護視窗中執行的自動任務完成的。對於 oracle內部優先順序高的物件，這些物件的統計資訊需要最先被收集更新。

自動統計資訊收集job會使用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC過程，該過程使用和DBMS_STATS.GATHER_*_STATS 過程相同的預設引數設定。這些預設設定在大多數場景是足夠的。然而，某些場景下需要更改其中一個或者多個預設引數值，我們可以使用DBMS_STATS.GATHER_*_STATS 過程完成設定。引數值應該在儘可能小的範圍內進行更改，最好是以每個物件為基礎。例如，如果我們想修改指定表的統計資訊過期閾值，我們希望閾值由原來的10%更改為5%，我們可以使用DBMS_STATS.SET_TABLE_PREFS過程改變指定表的STALE_PERCENT屬性。

·execdbms_stats.set_table_prefs(user,'SALES','STALE_PERCENT','5')

在修改完成後我們可以使用DBMS_STATS.GET_PREFS檢視屬性值修改情況。需要三個選項，引數名、使用者名稱、表名：

selectdbms_stats.get_prefs('STALE_PERCENT',user,'SALES') stale_percent from dual;STALE_PERCENT

-------------

5

Setting DBMS_STATS Preferences

如上所述，我們可能需要通過DBMS_STAT過程設定指定物件和表在自動統計資訊收集時候的收集策略。我們可以通過DBMS_STATS.GATHER_*_STATS 過程自定義收集策略，但是oracle還是推薦的方法是使用 DBMS_STATS.SET_*_PREFS過程進行設定。

引數可以在表級別、物件級別、資料庫或者全域性級別被修改（AUTOSTATS_TARGET和 CONCURRENT只能在全域性級別被更改）：

SET_TABLE_PREFS

SET_SCHEMA_PREFS

SET_DATABASE_PREFS

SET_GLOBAL_PREFS

通常情況下，我們最常修改的引數是ESTIMATE_PERCENT（控制取樣百分比）和METHOD_OPT（控制直方圖資訊的建立），但是估算的百分比現在已經比預設的值更好，由於本節後面所述的原因而保留其預設值

對於表的統計資訊收集時，允許DBMS_STATS.GATHER_*_STATS過程修改SET_TABLE_PREFS過程指定的引數的預設值。

在使用DBMS_STATS.GATHER_*_STATS過程收集指定物件所有已存在的表的統計資訊時，我們可以使用SET_SCHEMA_PREFS過程修改預設的引數配置。這個過程實際上呼叫SET_TABLE_PREFS過程來為指定物件的所有表設定預設引數。所以當我們使用該過程設定完成後，使用者新建立的表收集統計資訊使用的引數是依據GLOBAL配置指定的引數。

同樣，SET_DATABASE_PREFS過程可以修改使用DBMS_STATS.GATHER_*_STATS過程收集使用者定義物件統計資訊時候的預設引數。事實上這個過程呼叫的也是SET_TABLE_PREFS過程來為指定物件的所有表設定預設引數。對於預設引數修改完後建立的物件，他會選擇GLOBAL過程指定的預設引數配置。如果設定ADD_SYS引數為TRUE，那麼Oracle自己的使用者（SYS，SYSTEM等）也可以被包括進去。

SET_GLOBAL_PREFS過程可以指定所有沒有設定表優先順序物件的統計資訊收集過程的預設引數，在使用SET_GLOBAL_PREFS過程修改完預設引數後，所有的新建物件都會使用修改完後的預設收集引數，除非使用GATHER_*_STATS過程明確指定了引數或者設定了表的優先順序。

使用DBMS_STATS.GATHER_*_STATS收集統計資訊的時候，以上過程引數設定是分優先順序別。

請點選此處輸入圖片描述

oracle 12CR2引入了新的影響優先順序的引數

REFERENCE_OVERRIDES_PARAMETER.當這個引數被設定成TRUE，那麼優先順序的順序就會發生變化。如下圖所示。

請點選此處輸入圖片描述

ESTIMATE_PERCENT

在收集統計資訊過程中，可以使用ESTIMATE_PERCENT引數控制統計資料行的百分比。當表中的所有行都被統計（即100%取樣），我們將會得到最準確的統計資訊。Oracle資料庫在11g引入了一個新的取樣演算法， hash-based演算法來實現行資訊統計，使用10%的取樣頻率採集到的資訊精確度接近100%取樣頻率。在使用dbms_stats gather_ * _stats過程指定estimate_percent設定auto_sample_size（預設）時新的演算法就會被啟動。在Oracle資料庫11g之前，資料庫管理員往往設定estimate_precent引數為很低的值確保統計資訊能被快速收集。oracle強烈建議在從11g開始保持預設引數auto_sample_size。這一點尤為重要，因為12C開始引入了新的直方圖型別，混合和Top-Frequency，這些直方圖只能在引數保持預設的auto_sample_size才能被收集。

現在很多的系統還保留著舊的統計資訊收集指令碼（手動設定百分比）。所以當資料庫升級到12CR2後，可以考慮使用preference_overrides_parameter引數覆蓋手動統計資訊收集使用的預設引數。或者直接修改統計資訊收集指令碼。

METHOD_OPT

METHOD_OPT引數控制柱狀圖是否在收集過程中被建立。柱狀圖是oracle資料庫中一類特殊型別的列統計資料，使用者提供表中列資料分佈的詳細資訊。預設情況下METHOD_OPT引數是'FOR ALL COLUMNS SIZE AUTO'，這種情況下當表中的列被用在等值或者範圍where條件中比如WHERE col1= 'X'或者WHERE col1 BETWEEN 'A' and 'B'，並且這列資料是傾斜的。那麼oracle就會對這些列進行收集直方圖資訊。優化器知道那些列使用者查詢謂詞因為這些資訊會被儲存在資料字典表SYS.COL_USAGE$中。

一些DBA更傾向於自己控制直方圖的建立。Oracle推薦使用的方式是通過set_table_prefs進行設定。例如，你可以人為指定只為SALES表的其中兩列COL1和COL2建立直方圖。

begin

dbms_stats.set_table_prefs(

user,

'SALES',

'method_opt',

'for all columns size 1 for columns size 254 col1col2');

end;

/

也可以指定列必須有直方圖（COL1和COL2），此外，允許優化器決定是否在其他列上建立額外的直方圖：

begin

dbms_stats.set_table_prefs(

user,

'SALES',

'method_opt',

'for all columns size auto for columns size 254 col1col2');

end;

/

如果將METHOD_OPT屬性設定成'FOR ALL COLUMNS SIZE 1'.那麼直方圖將會被禁止建立。例如，可以修改DBMS_STATS全域性屬性中的METHOD_OPT使直方圖資訊不被建立。

begin

dbms_stats.set_global_prefs(

'method_opt',

'for all columns size 1');

end;

/

我們也可以刪除某些列上不需要的直方圖資訊。使用如下方式，DBMS_STATS.DELETE_COLUMN_STATS然後指定col_stat_type為‘HISTOGRAM’。

手工統計資訊收集

如果已經有一個完善的統計資訊收集過程或者因為某些原因想要對特定使用者方案禁用自動統計資訊收集而只保留收集資料字典的統計資訊.可以使用dbms_stats.set_global_prefs過程來改變autostats_target引數為oracle來替代auto.

execdbms_stats.set_global_prefs('autostats_target','oracle')； 

手動收集統計資訊過程中應該使用dbms_stats包,用它來替找過時的analyze命令.dbms_stats包提供多個dbms_stats.gather_*_stats過程來收集使用者方案物件,資料字典和固定物件的統計資訊.理想情況下，除了模式名稱和物件名之外，應該讓這些過程的所有引數都預設為預設值。在大多數情況下預設和自適應引數設定是足夠的：

exec dbms_stats.gather_table_stats('sh','sales')

正如上面所說，如果必須要修改統計引數預設值，那麼使用DBMS_STATS.SET_*_PREF過程在最小影響範圍下進行修改。 

Pending Statistics

當我們決定修改dbms_stats_gather_*_stats過程的引數預設值時，oracle強烈建議在生產系統中修改之前先驗證這些變更.如果沒有一個完整的測試環境，那麼應該使用pending statistics.使用pending statistics代替常用的資料字典表,統計資訊儲存在pending表中，以便在系統釋出和使用之前能夠以受控的方式進行啟用和測試.為了啟用pending統計資訊的收集需要對希望建立pending統計資訊的物件使用dbms_stats.set_*_prefs過程將引數publish從預設值true改變false.下面的例子中對sh使用者下的sales表啟用pending統計資訊並對sales表收集統計資訊.

execdbms_stats.set_table_prefs('sh','sales','publish','false')

通過將publish設定為false來啟用pending統計資訊。

正常的收集物件統計資訊

exec dbms_stats.gather_table_stats('sh','sales')

對於這些物件收集的統計資訊可以查詢*_tab_pending_stats檢視來顯示:

可以通過一個alter session命令來設定初始化引數optimizer_use_pending_stats為true來使用pending統計資訊.在啟用pending統計資訊之後任何在該會話執行的sql將使用這些新的沒有釋出的統計資訊.對於其他會話中所訪問的表沒有pending統計資訊時優化器將使用標準資料字典表中的當前統計資訊.當驗證這些pending統計資訊之後可以使用

dbms_stats.publish_pending_stats過程來發布.

exec dbms_stats.publish_pending_stats('sh','sales')

何時收集統計資訊

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

自動統計資訊收集任務

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 $ 使用者組, 以確保併發統計資訊的收集不會使用太多的系統資源。

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

不穩定的表

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

如果你依賴於自動收集統計資訊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

---

---

About Me

............................................................................................................................................. ● 本文整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ............................................................................................................................................. ● QQ群號：230161599（滿）、618766405 ● 微信群：可加我微信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友（646634621），註明新增緣由 ● 於 2017-09-01 09:00 ~ 2017-09-30 22:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................................. ● 小麥苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/ ............................................................................................................................................. 使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。    小麥苗的微信公眾號      小麥苗的DBA寶典QQ群1     小麥苗的DBA寶典QQ群2        小麥苗的微店 .............................................................................................................................................

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