dbms_stats 詳解

dbms_stats包問世以後，Oracle專家可透過一種簡單的方式來為CBO收集統計資料。目前，已經不再推薦你使用老式的分析表和dbms_utility方法來生成CBO統計資料。那些古老的方式甚至有可能危及SQL的效能，因為它們並非總是能夠捕捉到有關表和索引的高質量資訊。CBO使用物件統計，為所有SQL語句選擇最佳的執行計劃。

dbms_stats能良好地估計統計資料（尤其是針對較大的分割槽表），並能獲得更好的統計結果，最終制定出速度更快的SQL執行計劃。

下邊給出了dbms_stats的一次示範執行情況，其中使用了options子句。

execdbms_stats.gather_schema_stats( -
ownname => 'SCOTT', -
options => 'GATHER AUTO', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt => 'for all columns size repeat', -
degree => 15 -
)

為了充分認識dbms_stats的好處，你需要仔細體會每一條主要的預編譯指令（directive）。下面讓我們研究每一條指令，並體會如何用它為基於代價的SQL最佳化器收集最高質量的統計資料。

options引數

使用4個預設的方法之一，這個選項能控制Oracle統計的重新整理方式：

• gather——重新分析整個架構（Schema）。
• gather empty——只分析目前還沒有統計的表。
• gather stale——只重新分析修改量超過10%的表（這些修改包括插入、更新和刪除）。
• gather auto——重新分析當前沒有統計的物件，以及統計資料過期（變髒）的物件。注意，使用gather auto類似於組合使用gather stale和gather empty。

注意，無論gather stale還是gather auto，都要求進行監視。如果你執行一個alter table xxx monitoring命令，Oracle會用dba_tab_modifications檢視來跟蹤發生變動的表。這樣一來，你就確切地知道，自從上一次分析統計資料以來，發生了多少次插入、更新和刪除操作。

 

estimate_percent選項

以下estimate_percent引數是一種比較新的設計，它允許Oracle的dbms_stats在收集統計資料時，自動估計要取樣的一個segment的最佳百分比：
estimate_percent => dbms_stats.auto_sample_size

要驗證自動統計取樣的準確性，你可檢視dba_tables sample_size列。一個有趣的地方是，在使用自動取樣時，Oracle會為一個樣本尺寸選擇5到20的百分比。記住，統計資料質量越好，CBO做出的決定越好。

method_opt選項

dbms_stats的method_opt引數尤其適合在表和索引資料發生變化時重新整理統計資料。method_opt引數也適合用於判斷哪些列需要直方圖（histograms）。

某些情況下，索引內的各個值的分佈會影響CBO是使用一個索引還是執行一次全表掃描的決策。例如，假如在where子句中指定的值的數量不對稱，全表掃描就顯得比索引訪問更經濟。

如果你有一個高度傾斜的索引（某些值的行數不對稱），就可建立Oracle直方圖統計。但在現實世界中，出現這種情況的機率相當小。使用CBO時，最常見的錯誤之一就是在CBO統計中不必要地引入直方圖。根據經驗，只有在列值要求必須修改執行計劃時，才應使用直方圖。

為了智慧地生成直方圖，Oracle為dbms_stats準備了method_opt引數。在method_opt子句中，還有一些重要的新選項，包括skewonly，repeat和auto：method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'

skewonly選項會耗費大量處理時間，因為它要檢查每個索引中的每個列的值的分佈情況。

假如dbms_stat發現一個索引的各個列分佈得不均勻，就會為那個索引建立直方圖，幫助基於代價的SQL最佳化器決定是進行索引訪問，還是進行全表掃描訪問。例如，在一個索引中，假定有一個列在50%的行中，如清單B所示，那麼為了檢索這些行，全表掃描的速度會快於索引掃描。--*************************************************************
-- SKEWONLY option—Detailed analysis
--
-- Use this method for a first-time analysis for skewed indexes
-- This runs a long time because all indexes are examined
--*************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size skewonly',
degree => 7
);
end;

重新分析統計資料時，使用repeat選項，重新分析任務所消耗的資源就會少一些。使用repeat選項（清單C）時，只會為現有的直方圖重新分析索引，不再搜尋其他直方圖機會。定期重新分析統計資料時，你應該採取這種方式。--**************************************************************
-- REPEAT OPTION - Only reanalyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
-- job will use the “repeat” option. The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only reanalyze histograms for
-- indexes that have histograms.
--**************************************************************begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree => 7
);
end;

使用alter table xxx monitoring;命令來實現Oracle表監視時，需要使用dbms_stats中的auto選項。如清單D所示，auto選項根據資料分佈以及應用程式訪問列的方式（例如透過監視而確定的一個列的工作量）來建立直方圖。使用method_opt=>’auto’類似於在dbms_stats的option引數中使用gather auto。begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
degree => 7
);
end;

並行收集

Oracle允許以並行方式來收集CBO統計資料，這就顯著提高了收集統計資料的速度。但是，要想並行收集統計資料，你需要一臺安裝了多個CPU的SMP伺服器。

更快的執行速度

dbms_stats是提高SQL執行速度的一種出色機制。透過使用dbms_stats來收集最高質量的統計資料，CBO能夠正確判斷執行任何SQL查詢時的最快途徑。dbms_stats還在不斷地改進。目前，它的一些令人激動的新特性（自動樣本大小和自動直方圖生成）已經顯著簡化了Oracle專家的工作。

 

 

 

One exciting feature of dbms_stats is the ability to automatically look for columns that should have histograms, and create the histograms. Multi-bucket histograms add a huge parsing overhead to SQL statements, and histograms should ONLY be used when the SQL will choose a different execution plan based upon the column value.

To aid in intelligent histogram generation, Oracle uses the method_opt parameter of dbms_stats. There are also important new options within the method_opt clause, namely skewonly, repeat and auto.

method_opt=>'for all columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size auto'
In practice, there is a specific order to use the different options of dbms_stats. See this article for details. Let’s take a close look at each method option.



The method_opt=’SKEWONLY’ dbms_stats Option

The first is the “skewonly” option which very time-intensive because it examines the distribution of values for every column within every index. If dbms_stats discovers an index whose columns are unevenly distributed, it will create histograms for that index to aid the cost-based SQL optimizer in making a decision about index vs. full-table scan access. For example, if an index has one column that is in 50% of the rows, a full-table scan is faster than and index scan to retrieve these rows. 

Histograms are also used with SQL that has bind variables and SQL with cursor_sharing enabled. In these cases, the CBO determines if the column value could affect the execution plan, and if so, replaced the bind variable with a literal and performs a hard parse.



--*************************************************************
-- SKEWONLY option – Detailed analysis
--
-- Use this method for a first-time analysis for skewed indexes
-- This runs a long time because all indexes are examined
--*************************************************************

begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size, 
method_opt => 'for all columns size skewonly',
degree => 7
);
end;
/


The method_opt=’REPEAT’ dbms_stats Option

Following the one-time detailed analysis, the re-analyze task will be less resource intensive with the REPEAT option. Using the repeat option will only re-analyze indexes with existing histograms, and will not search for other histograms opportunities. This is the way that you will re-analyze you statistics on a regular basis.



--**************************************************************
-- REPEAT OPTION - Only re-analyze histograms for indexes
-- that have histograms
--
-- Following the initial analysis, the weekly analysis
-- job will use the “repeat” option. The repeat option
-- tells dbms_stats that no indexes have changed, and
-- it will only re-analyze histograms for
-- indexes that have histograms.
--**************************************************************
begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size, 
method_opt => 'for all columns size repeat',
degree => 7
);
end;
/


The method_opt=’AUTO’ dbms_stats Option

The auto option is used when monitoring is implemented and creates histograms based upon data distribution and the manner in which the column is accessed by the application (e.g. the workload on the column as determined by monitoring, especially foreign keys to determine the cardinality of table join result sets). Using method_opt=>’auto’ is similar to using the gather auto in the option parameter of dbms_stats. 

begin
dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => dbms_stats.auto_sample_size, 
method_opt => 'for all columns size auto',
degree => 7
);
end;
/

Remember, analyzing for histograms is time-consuming, and histograms are used under two conditions:
Table join order – The CBO must know the size of the intermediate result sets (cardinality) to properly determine the correct join order the multi-table joins.

Table access method – The CBO needs to know about columns in SQL where clauses, where the column value is skewed such that a full-table scan might be faster than an index range scan. Oracle uses this skew information in conjunction with the clustering_factor columns of the dba_indexes view.

Hence, this is the proper order for using the dbms_stats package to locate proper columns for histograms:

1. Skewonly option - You want to use skewonly to do histograms for skewed columns, for cases where the value will make a difference between a full-table scan and an index scan.

2. Monitor - Next, turn-on monitoring. Issue an “alter table xx monitoring” and “alter index yyy monitoring” command for all segments in your schema. This will monitor workload against

3. Auto option - Once monitoring is in-place, you need to re-analyze with the "auto" option to create histograms for join columns within tables. This is critical for the CBO to determine the proper join order for finding the driving table in multi-table joins.

4. Repeat option - Finally, use the "repeat" option to re-analyze only the existing histograms.

Periodically you will want to re-run the skewonly and auto option to identify any new columns that require histograms. Once located, the repeat option will ensure that they are refreshed with current values.


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

If you like DBA internal tricks, check-out my new book Creating a Self-tuning Oracle Database by Rampant TechPress. This book is now available at this link:



Regards,