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，那么優先級的順序就會發生變化。如下圖所示。

VPSOXyDaejfFxiaictxSZUoT8g/0?wx_fmt=png">

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')