oracle統計資訊(二)

 

5. 鎖住/解鎖統計資訊

·         LOCK_SCHEMA_STATS

·         LOCK_TABLE_STATS

·         UNLOCK_SCHEMA_STATS

·         UNLOCK_TABLE_STATS

6. 手動收集統計資訊

如果你選擇手動收集統計資訊，那麼你需要手動的收集所有使用者的統計資訊，包括系統使用者。如果你資料庫中的資料是有規律的變化的，那麼你可以有規律的收集統計資訊，以便統計資訊能夠準確的反映資料庫中的物件的特徵。

可以利用DBMS_STATS包，來收集表，索引，列，以及分割槽表的統計資訊，DBMS_STATS不能收集CLUSTER 的統計資訊，不過可以收集單個表來代替收集整個CLUSTER的統計資訊。

當你收集表，列，索引的統計資訊的時候，如果ORACLE在資料字典中發現這個物件已經收集了統計資訊，那麼ORACLE會更新已經存在的統計資訊，舊的統計資訊會被儲存下來，如果你願意還能還原舊的統計資訊。

你可以使用DBMS_STATS.GATHER_DICTIONARY_STATS來收集系統使用者的統計資訊，這個過程收集所有的系統使用者的統計資訊，包括SYS和SYSTEM，以及其他使用者，比如CTXSYS,DRSYS。

當資料庫物件的統計資訊被更新之後，ORACLE會使已經解析的SQL語句作廢，當再次執行該SQL語句的時候，ORACLE會重新解析該SQL，優化器會自動的根據新的統計資訊選擇一條新的執行計劃。對於分散式的資料庫，不會作廢。

 

收集統計資訊的過程
GATHER_INDEX_STATS      --收集索引統計資訊

GATHER_TABLE_STATS      --收集表，列，索引統計資訊

GATHER_SCHEMA_STATS     --收集schema所有物件統計資訊

GATHER_DICTIONARY_STATS –-收集所有系統使用者的統計資訊

GATHER_DATABASE_STATS   --收集資料庫所有物件統計資訊

我們利用上面的過程收集統計資訊的時候有幾個需要關心的引數

取樣

並行

分割槽

列統計以及直方圖/柱狀圖

過期的統計

自定義統計

 

在收集統計資訊的操作過程中我們可以使用取樣來評估統計資訊。取樣對於收集統計資訊來說是一項很重要的技術。如果在收集統計資訊的時候不使用取樣，那麼就需要對錶進行全表掃描，以及排序整個表。通過取樣可以降低收集必要的統計資訊所花費的資源。
控制取樣的引數是ESTIMATE_PERCENT，取樣的引數可以設定任意值(當然要在範圍內)，不過ORACLE公司推薦設定ESTIMATE_PERCENT為DBMS_STATS.AUTO_SAMPLE_SIZE。

AUTO_SAMPLE_SILE可以讓ORACLE自己決定最好的取樣值，因為不同型別(table,index,column)的統計資訊有不同的需求。

取樣的例子：

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SCOTT’,DBMS_STATS.AUTO_SAMPLE_SIZE);

/

當ESTIMATE_PERCENT引數是手動指定的，如果手動指定的引數過小，不能收集到足夠的資訊，那麼DBMS_STATS可能會自動增長ESTIMATE_PERCENT的值，這樣就能確保收集到足夠的統計資訊。

 

我們既可以序列的收集統計資訊，也可以並行的收集統計資訊。引數DEGREE控制DBMS_STATS是否使用並行特徵。ORACLE公司推薦將DEGREE引數設定為DBMS_STATS.AUTO_DEGREE。這樣設定過後，ORACLE就能夠根據OBJECT的SIZE，以及與並行有關的init引數來決定一個恰當的並行度，收集統計資訊。注意:cluster index,domain index,bitmap join index不能使用並行特徵。

 

對於分割槽表和分割槽索引，DBMS_STATS既可以單獨的收集分割槽統計資訊，也可以收集整個表/索引的統計資訊。對於組合分割槽，DBMS_STATS也能夠收集子分割槽，分割槽，以及整個表/索引的統計資訊。引數GRANULARITY控制分割槽統計資訊的收集。因為分割槽統計資訊，全域性統計資訊對於大多數系統來說都是非常重要的，所以ORACLE公司推薦將其設定為AUTO來收集分割槽，以及全域性的統計資訊。

 

當對錶收集統計資訊的時候，DBMS_STATS會收集列的資料分佈資訊。資料分佈最基本的統計資訊就是這個列的最大值與最小值。如果這一列是傾斜的，那麼優化器僅僅根據列最大值與最小值是無法制定出準確的執行計劃的。對於傾斜的資料分佈，我們可以收集列的直方圖/柱狀圖統計資訊，這樣可以讓優化器制定出更加準確的執行計劃。

引數METHOD_OPT控制柱狀圖的收集。ORACLE公司推薦設定METHOD_OPT為FOR ALL COLUMNS SIZE AUTO。這樣設定過後ORACLE會自動的判斷哪一列需要收集柱狀圖，並且自動的設定柱狀圖的bucket。你同樣可以手動的設定哪一列需要收集柱狀圖，以及柱狀圖的bucket。

 

為了知道統計資訊是否過期，ORACLE提供了表監控功能。將init引數STATISTICS_LEVEL設定為ALL或者TYPICAL(預設)，就開啟了表監控的功能(10g已經不需要alter table monitor了)。表監控功能跟蹤表的insert,update,delete，truncate，操作，並且記錄在DBA_TAB_MODIFICATIONS檢視裡面。我們在查詢DBA_TAB_MODIFICATIONS檢視的時候有可能查詢不到結果，或者查詢的結果不準確，這個時候需要用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO過程將記憶體中的資訊重新整理到

該檢視中。OPTIONS引數設定為GATHER STALE或者GATHER AUTO，就會讓DBMS_STATS判斷表的統計資訊是否過期(注意GATHER_TABLE_STATS中沒有這個引數，只有GATHER_DATABASE_STATS,GATHER_SCHEMA_STATS過程中有這個引數)。判斷表的統計資訊是否過期的依據是是否有10%以上的資料被修改過，如果被修改過了，那麼ORACLE就認為之前的統計資訊過期了，ORACLE會重新收集統計資訊。

 

在我們建立了函式索引之後，我們要為列收集統計資訊，這個時候我們需要設定引數METHOD_OPT為FOR ALL HIDDEN COLUMNS。

7. 收集統計資訊的策略

通常情況下，我們會將ORACLE自動收集統計資訊功能給關閉，我們會採用手動的方式給資料庫收集統計資訊。至於收集統計資訊的策略需要根據系統來確定。下面說說幾種常見的情況：

如果你係統中的表的資料是增量(有規律)的增加，也就是說你幾乎不做任何的批量處理操作，比如批量刪除，批量載入操作。對於這樣的表收集統計資訊是非常簡單的。你可以通過檢視DBA_TAB_MODIFICATIONS檢視來觀察表的變化情況，觀察表中資料量的變化是否超過了10%,並且記錄下天數。這樣你就可以每隔這樣的時間間隔對其收集一次統計資訊。你可以用CRONTAB,或者JOB呼叫GATHER_SCHEMA_STATS或者GATHER_TABLE_STATS過程來收集統計資訊。

對於經常批量操作的表，那麼表的統計資訊就必須在批量操作之後對其收集統計資訊。

對於分割槽表，通常只有一個分割槽被修改，這種情況下可以只收集單獨分割槽的統計資訊，不過收集整個表的統計資訊還是非常有必要的。

最後我會給出兩個指令碼，判斷該表是否需要收集統計資訊。