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

原文連結 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')

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