Oracle 12c數(shù)據(jù)庫(kù)優(yōu)化器統(tǒng)計(jì)信息收集的最佳實(shí)踐（一）

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

譯者  劉金龍 

導(dǎo)  語(yǔ)

Oracle優(yōu)化器會(huì)為SQL語(yǔ)句產(chǎn)生所有可能的訪問(wèn)路徑（執(zhí)行計(jì)劃），然后從中選擇一條COST值最低的執(zhí)行路徑，這個(gè)cost值是指oracle估算執(zhí)行SQL所消耗的資源。為了讓優(yōu)化器能夠精確計(jì)算的每一條執(zhí)行計(jì)劃的COST值，這就需要被執(zhí)行SQL語(yǔ)句所需訪問(wèn)的所有對(duì)象（表和索引等）和系統(tǒng)有必要的描述信息。

這些必要的信息通常被稱為optimizer statistics（優(yōu)化器統(tǒng)計(jì)信息）。理解和管理優(yōu)化器統(tǒng)計(jì)信息是優(yōu)化SQL執(zhí)行的關(guān)鍵。知道何時(shí)、如何以及快速的方式收集優(yōu)化器統(tǒng)計(jì)信息對(duì)于維持系統(tǒng)良好性能是至關(guān)重要的。本文將詳細(xì)討論，在Oracle常見(jiàn)的場(chǎng)景中何時(shí)以及如何收集統(tǒng)計(jì)信息，文章大致分如下幾個(gè)部分：

• 如何收集統(tǒng)計(jì)信息

• 何時(shí)收集統(tǒng)計(jì)信息

• 提高統(tǒng)計(jì)信息質(zhì)量

• 快速收集統(tǒng)計(jì)信息

• 何時(shí)不用收集統(tǒng)計(jì)信息

• 收集其他類型統(tǒng)計(jì)信息

如何收集統(tǒng)計(jì)信息

在Oracle中優(yōu)選的方式是統(tǒng)計(jì)信息自動(dòng)收集。如果系統(tǒng)已經(jīng)有完善的手動(dòng)收集統(tǒng)計(jì)信息程序，那么可以優(yōu)選手動(dòng)統(tǒng)計(jì)信息收集。無(wú)論選擇哪種收集方式，首先需要考慮的是默認(rèn)的全局參數(shù)設(shè)置是否滿足您的需求。

在大多數(shù)情況下這些默認(rèn)參數(shù)是能夠滿足的，但是如果我們想根據(jù)自己的系統(tǒng)的實(shí)際情況作出修改，那么我們可以通過(guò)設(shè)置SET_GLOBAL_PREFS.參數(shù)值。一旦我們選擇這樣做，我們可以通過(guò)使用DBMS_STATS“setpreference”工具覆蓋默認(rèn)設(shè)置。例如，使用SET_TABLE_PREFS參數(shù)設(shè)置表統(tǒng)計(jì)信息收集時(shí)使用incremental方式或者收集直方圖信息。使用這種方式，我們將會(huì)指定哪些指定統(tǒng)計(jì)信息被默認(rèn)收集，而不需要在收集統(tǒng)計(jì)信息的時(shí)候調(diào)整參數(shù)。我們可以自由的使用默認(rèn)參數(shù)收集表/用戶/數(shù)據(jù)庫(kù)級(jí)別的統(tǒng)計(jì)信息，并且確定這些統(tǒng)計(jì)信息收集策略已經(jīng)被使用。更重要的是，我們可以在自動(dòng)和手動(dòng)統(tǒng)計(jì)信息收集之間自由切換。

自動(dòng)統(tǒng)計(jì)信息收集

oracle數(shù)據(jù)庫(kù)需要收集那些缺少或者已經(jīng)“stale”過(guò)期統(tǒng)計(jì)信息的對(duì)象統(tǒng)計(jì)信息。這是在預(yù)定義的維護(hù)窗口中執(zhí)行的自動(dòng)任務(wù)完成的。對(duì)于 oracle內(nèi)部?jī)?yōu)先級(jí)高的對(duì)象，這些對(duì)象的統(tǒng)計(jì)信息需要最先被收集更新。

自動(dòng)統(tǒng)計(jì)信息收集job會(huì)使用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC過(guò)程，該過(guò)程使用和DBMS_STATS.GATHER_*_STATS 過(guò)程相同的默認(rèn)參數(shù)設(shè)置。這些默認(rèn)設(shè)置在大多數(shù)場(chǎng)景是足夠的。然而，某些場(chǎng)景下需要更改其中一個(gè)或者多個(gè)默認(rèn)參數(shù)值，我們可以使用DBMS_STATS.GATHER_*_STATS 過(guò)程完成設(shè)置。參數(shù)值應(yīng)該在盡可能小的范圍內(nèi)進(jìn)行更改，最好是以每個(gè)對(duì)象為基礎(chǔ)。例如，如果我們想修改指定表的統(tǒng)計(jì)信息過(guò)期閾值，我們希望閾值由原來(lái)的10%更改為5%，我們可以使用DBMS_STATS.SET_TABLE_PREFS過(guò)程改變指定表的STALE_PERCENT屬性。

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

在修改完成后我們可以使用DBMS_STATS.GET_PREFS查看屬性值修改情況。需要三個(gè)選項(xiàng)，參數(shù)名、用戶名、表名：

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

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

5

Setting DBMS_STATS Preferences

如上所述，我們可能需要通過(guò)DBMS_STAT過(guò)程設(shè)置指定對(duì)象和表在自動(dòng)統(tǒng)計(jì)信息收集時(shí)候的收集策略。我們可以通過(guò)DBMS_STATS.GATHER_*_STATS 過(guò)程自定義收集策略，但是oracle還是推薦的方法是使用 DBMS_STATS.SET_*_PREFS過(guò)程進(jìn)行設(shè)置。

參數(shù)可以在表級(jí)別、對(duì)象級(jí)別、數(shù)據(jù)庫(kù)或者全局級(jí)別被修改（AUTOSTATS_TARGET和 CONCURRENT只能在全局級(jí)別被更改）：

SET_TABLE_PREFS

SET_SCHEMA_PREFS

SET_DATABASE_PREFS

SET_GLOBAL_PREFS

通常情況下，我們最常修改的參數(shù)是ESTIMATE_PERCENT（控制采樣百分比）和METHOD_OPT（控制直方圖信息的創(chuàng)建），但是估算的百分比現(xiàn)在已經(jīng)比默認(rèn)的值更好，由于本節(jié)后面所述的原因而保留其缺省值

對(duì)于表的統(tǒng)計(jì)信息收集時(shí)，允許DBMS_STATS.GATHER_*_STATS過(guò)程修改SET_TABLE_PREFS過(guò)程指定的參數(shù)的默認(rèn)值。

在使用DBMS_STATS.GATHER_*_STATS過(guò)程收集指定對(duì)象所有已存在的表的統(tǒng)計(jì)信息時(shí)，我們可以使用SET_SCHEMA_PREFS過(guò)程修改默認(rèn)的參數(shù)配置。這個(gè)過(guò)程實(shí)際上調(diào)用SET_TABLE_PREFS過(guò)程來(lái)為指定對(duì)象的所有表設(shè)置默認(rèn)參數(shù)。所以當(dāng)我們使用該過(guò)程設(shè)置完成后，用戶新創(chuàng)建的表收集統(tǒng)計(jì)信息使用的參數(shù)是依據(jù)GLOBAL配置指定的參數(shù)。

同樣，SET_DATABASE_PREFS過(guò)程可以修改使用DBMS_STATS.GATHER_*_STATS過(guò)程收集用戶定義對(duì)象統(tǒng)計(jì)信息時(shí)候的默認(rèn)參數(shù)。事實(shí)上這個(gè)過(guò)程調(diào)用的也是SET_TABLE_PREFS過(guò)程來(lái)為指定對(duì)象的所有表設(shè)置默認(rèn)參數(shù)。對(duì)于默認(rèn)參數(shù)修改完后創(chuàng)建的對(duì)象，他會(huì)選擇GLOBAL過(guò)程指定的默認(rèn)參數(shù)配置。如果設(shè)置ADD_SYS參數(shù)為TRUE，那么Oracle自己的用戶（SYS，SYSTEM等）也可以被包括進(jìn)去。

SET_GLOBAL_PREFS過(guò)程可以指定所有沒(méi)有設(shè)置表優(yōu)先級(jí)對(duì)象的統(tǒng)計(jì)信息收集過(guò)程的默認(rèn)參數(shù)，在使用SET_GLOBAL_PREFS過(guò)程修改完默認(rèn)參數(shù)后，所有的新建對(duì)象都會(huì)使用修改完后的默認(rèn)收集參數(shù)，除非使用GATHER_*_STATS過(guò)程明確指定了參數(shù)或者設(shè)置了表的優(yōu)先級(jí)。

使用DBMS_STATS.GATHER_*_STATS收集統(tǒng)計(jì)信息的時(shí)候，以上過(guò)程參數(shù)設(shè)置是分優(yōu)先級(jí)別。

oracle 12CR2引入了新的影響優(yōu)先級(jí)的參數(shù)

REFERENCE_OVERRIDES_PARAMETER.當(dāng)這個(gè)參數(shù)被設(shè)置成TRUE，那么優(yōu)先級(jí)的順序就會(huì)發(fā)生變化。如下圖所示。

VPSOXyDaejfFxiaictxSZUoT8g/0?wx_fmt=png">

ESTIMATE_PERCENT

在收集統(tǒng)計(jì)信息過(guò)程中，可以使用ESTIMATE_PERCENT參數(shù)控制統(tǒng)計(jì)數(shù)據(jù)行的百分比。當(dāng)表中的所有行都被統(tǒng)計(jì)（即100%采樣），我們將會(huì)得到最準(zhǔn)確的統(tǒng)計(jì)信息。Oracle數(shù)據(jù)庫(kù)在11g引入了一個(gè)新的采樣算法， hash-based算法來(lái)實(shí)現(xiàn)行信息統(tǒng)計(jì)，使用10%的采樣頻率采集到的信息精確度接近100%采樣頻率。在使用dbms_stats gather_ * _stats過(guò)程指定estimate_percent設(shè)置auto_sample_size（默認(rèn)）時(shí)新的算法就會(huì)被啟動(dòng)。在Oracle數(shù)據(jù)庫(kù)11g之前，數(shù)據(jù)庫(kù)管理員往往設(shè)置estimate_precent參數(shù)為很低的值確保統(tǒng)計(jì)信息能被快速收集。oracle強(qiáng)烈建議在從11g開(kāi)始保持默認(rèn)參數(shù)auto_sample_size。這一點(diǎn)尤為重要，因?yàn)?2C開(kāi)始引入了新的直方圖類型，混合和Top-Frequency，這些直方圖只能在參數(shù)保持默認(rèn)的auto_sample_size才能被收集。

現(xiàn)在很多的系統(tǒng)還保留著舊的統(tǒng)計(jì)信息收集腳本（手動(dòng)設(shè)置百分比）。所以當(dāng)數(shù)據(jù)庫(kù)升級(jí)到12CR2后，可以考慮使用preference_overrides_parameter參數(shù)覆蓋手動(dòng)統(tǒng)計(jì)信息收集使用的默認(rèn)參數(shù)。或者直接修改統(tǒng)計(jì)信息收集腳本。

METHOD_OPT

METHOD_OPT參數(shù)控制柱狀圖是否在收集過(guò)程中被創(chuàng)建。柱狀圖是oracle數(shù)據(jù)庫(kù)中一類特殊類型的列統(tǒng)計(jì)數(shù)據(jù)，用戶提供表中列數(shù)據(jù)分布的詳細(xì)信息。默認(rèn)情況下METHOD_OPT參數(shù)是'FOR ALL COLUMNS SIZE AUTO'，這種情況下當(dāng)表中的列被用在等值或者范圍where條件中比如WHERE col1= 'X'或者WHERE col1 BETWEEN 'A' and 'B'，并且這列數(shù)據(jù)是傾斜的。那么oracle就會(huì)對(duì)這些列進(jìn)行收集直方圖信息。優(yōu)化器知道那些列用戶查詢謂詞因?yàn)檫@些信息會(huì)被存儲(chǔ)在數(shù)據(jù)字典表SYS.COL_USAGE$中。

一些DBA更傾向于自己控制直方圖的創(chuàng)建。Oracle推薦使用的方式是通過(guò)set_table_prefs進(jìn)行設(shè)置。例如，你可以人為指定只為SALES表的其中兩列COL1和COL2創(chuàng)建直方圖。

begin

dbms_stats.set_table_prefs(

user,

'SALES',

'method_opt',

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

end;

/

也可以指定列必須有直方圖（COL1和COL2），此外，允許優(yōu)化器決定是否在其他列上創(chuàng)建額外的直方圖：

begin

dbms_stats.set_table_prefs(

user,

'SALES',

'method_opt',

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

end;

/

如果將METHOD_OPT屬性設(shè)置成'FOR ALL COLUMNS SIZE 1'.那么直方圖將會(huì)被禁止創(chuàng)建。例如，可以修改DBMS_STATS全局屬性中的METHOD_OPT使直方圖信息不被創(chuàng)建。

begin

dbms_stats.set_global_prefs(

'method_opt',

'for all columns size 1');

end;

/

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

手工統(tǒng)計(jì)信息收集

如果已經(jīng)有一個(gè)完善的統(tǒng)計(jì)信息收集過(guò)程或者因?yàn)槟承┰蛳胍獙?duì)特定用戶方案禁用自動(dòng)統(tǒng)計(jì)信息收集而只保留收集數(shù)據(jù)字典的統(tǒng)計(jì)信息.可以使用dbms_stats.set_global_prefs過(guò)程來(lái)改變autostats_target參數(shù)為oracle來(lái)替代auto.

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

手動(dòng)收集統(tǒng)計(jì)信息過(guò)程中應(yīng)該使用dbms_stats包,用它來(lái)替找過(guò)時(shí)的analyze命令.dbms_stats包提供多個(gè)dbms_stats.gather_*_stats過(guò)程來(lái)收集用戶方案對(duì)象,數(shù)據(jù)字典和固定對(duì)象的統(tǒng)計(jì)信息.理想情況下，除了模式名稱和對(duì)象名之外，應(yīng)該讓這些過(guò)程的所有參數(shù)都默認(rèn)為默認(rèn)值。在大多數(shù)情況下默認(rèn)和自適應(yīng)參數(shù)設(shè)置是足夠的：

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

正如上面所說(shuō)，如果必須要修改統(tǒng)計(jì)參數(shù)默認(rèn)值，那么使用DBMS_STATS.SET_*_PREF過(guò)程在最小影響范圍下進(jìn)行修改。 

Pending Statistics

當(dāng)我們決定修改dbms_stats_gather_*_stats過(guò)程的參數(shù)缺省值時(shí)，oracle強(qiáng)烈建議在生產(chǎn)系統(tǒng)中修改之前先驗(yàn)證這些變更.如果沒(méi)有一個(gè)完整的測(cè)試環(huán)境，那么應(yīng)該使用pending statistics.使用pending statistics代替常用的數(shù)據(jù)字典表,統(tǒng)計(jì)信息存儲(chǔ)在pending表中，以便在系統(tǒng)發(fā)布和使用之前能夠以受控的方式進(jìn)行啟用和測(cè)試.為了激活pending統(tǒng)計(jì)信息的收集需要對(duì)希望創(chuàng)建pending統(tǒng)計(jì)信息的對(duì)象使用dbms_stats.set_*_prefs過(guò)程將參數(shù)publish從缺省值true改變false.下面的例子中對(duì)sh用戶下的sales表啟用pending統(tǒng)計(jì)信息并對(duì)sales表收集統(tǒng)計(jì)信息.

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

通過(guò)將publish設(shè)置為false來(lái)啟用pending統(tǒng)計(jì)信息。

正常的收集對(duì)象統(tǒng)計(jì)信息

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

對(duì)于這些對(duì)象收集的統(tǒng)計(jì)信息可以查詢*_tab_pending_stats視圖來(lái)顯示:

可以通過(guò)一個(gè)alter session命令來(lái)設(shè)置初始化參數(shù)optimizer_use_pending_stats為true來(lái)使用pending統(tǒng)計(jì)信息.在啟用pending統(tǒng)計(jì)信息之后任何在該會(huì)話運(yùn)行的sql將使用這些新的沒(méi)有發(fā)布的統(tǒng)計(jì)信息.對(duì)于其他會(huì)話中所訪問(wèn)的表沒(méi)有pending統(tǒng)計(jì)信息時(shí)優(yōu)化器將使用標(biāo)準(zhǔn)數(shù)據(jù)字典表中的當(dāng)前統(tǒng)計(jì)信息.當(dāng)驗(yàn)證這些pending統(tǒng)計(jì)信息之后可以使用

dbms_stats.publish_pending_stats過(guò)程來(lái)發(fā)布.

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