oracle 8i 以後加處的功能，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 willonly 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專家的工作。

在使用DBMS_STATS分析表的時候，我們經常要儲存之前的分析，以防分析後導致系統效能低下然後進行快速恢復。
首先建立一個分析表，該表是用來儲存之前的分析值。
SQL> begin
2 dbms_stats.create_stat_table(ownname => 'TEST',stattab => 'STAT_TABLE');
3 end;
4 /
PL/SQL 過程已成功完成。
分析表資訊
SQL> BEGIN
2 --DBMS_STATS.delete_table_stats(ownname => 'TEST',tabname => 'A');
3 DBMS_STATS.gather_table_stats(ownname => 'TEST',tabname => 'A');
4 END;
5 /
PL/SQL 過程已成功完成。
匯出表分析信息到stat_table中。
SQL> BEGIN
2 dbms_stats.export_table_stats(ownname => 'TEST',tabname => 'A',stattab => 'STAT_TABLE');
3 END;
4 /
PL/SQL 過程已成功完成。
SQL>

同理也有
EXPORT_COLUMN_STATS：匯出列的分析資訊
EXPORT_INDEX_STATS：匯出索引分析資訊
EXPORT_SYSTEM_STATS：匯出系統分析資訊
EXPORT_TABLE_STATS：匯出表分析資訊
EXPORT_SCHEMA_STATS：匯出方案分析資訊
EXPORT_DATABASE_STATS：匯出資料庫分析資訊
IMPORT_COLUMN_STATS：匯入列分析資訊
IMPORT_INDEX_STATS：匯入索引分析資訊
IMPORT_SYSTEM_STATS：匯入系統分析資訊
IMPORT_TABLE_STATS：匯入表分析資訊
IMPORT_SCHEMA_STATS：匯入方案分析資訊
IMPORT_DATABASE_STATS：匯入資料庫分析資訊
GATHER_INDEX_STATS：分析索引資訊
GATHER_TABLE_STATS：分析表資訊，當cascade為 true時，分析表、列（索引）資訊
GATHER_SCHEMA_STATS：分析方案資訊
GATHER_DATABASE_STATS：分析資料庫資訊
GATHER_SYSTEM_STATS：分析系統資訊
SQL> select count(*) from stat_table;
COUNT(*)
----------
1
刪除分析資訊
SQL> BEGIN
2 DBMS_STATS.delete_table_stats(ownname => 'TEST',tabname => 'A');
3 END;
4 /
PL/SQL 過程已成功完成。
匯入分析資訊
SQL> BEGIN
2 DBMS_STATS.import_table_stats(ownname => 'TEST',tabname => 'A',stattab => 'STAT_TABLE');
3 END;
4 /
PL/SQL 過程已成功完成。

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/liuya1985liuya/archive/2008/03/02/2140257.aspx

DBMS_STATS分析表

作用:DBMS_STATS.GATHER_TABLE_STATS統計表,列,索引的統計資訊.

DBMS_STATS.GATHER_TABLE_STATS的語法如下:

DBMS_STATS.GATHER_TABLE_STATS (ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, force BOOLEAN);

引數說明:

ownname:要分析表的擁有者

tabname:要分析的表名.

partname:分割槽的名字,只對分割槽表或分割槽索引有用.

estimate_percent:取樣行的百分比,取值範圍[0.000001,100],null為全部分析,不取樣. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是預設值,由oracle決定最佳取取樣值.

block_sapmple:是否用塊取樣代替行取樣.

method_opt:決定histograms資訊是怎樣被統計的.method_opt的取值如下:

for all columns:統計所有列的histograms.

for all indexed columns:統計所有indexed列的histograms.

for all hidden columns:統計你看不到列的histograms

for columns SIZE | REPEAT | AUTO | SKEWONLY:統計指定列的histograms.N的取值範圍[1,254]; REPEAT上次統計過的histograms;AUTO由oracle決定N的大小;SKEWONLY multiple end-points with the same value which is what we define by "there is skew in the data

degree:決定並行度.預設值為null.

granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.

cascace:是收集索引的資訊.預設為falase.

stattab指定要儲存統計資訊的表,statid如果多個表的統計資訊儲存在同一個stattab中用於進行區分.statown儲存統計資訊表的擁有者.以上三個引數若不指定,統計資訊會直接更新到資料字典.

no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.

force:即使表鎖住了也收集統計資訊.

例子:

execute dbms_stats.gather_table_stats(ownname => 'owner',tabname => 'table_name' ,estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true);

例如：

在使用DBMS_STATS分析表的時候，我們經常要儲存之前的分析，以防分析後導致系統效能低下然後進行快速恢復。

1、首先建立一個分析表，該表是用來儲存之前的分析值：

SQL> begin
2 dbms_stats.create_stat_table(ownname=>'TEST',stattab=>'STAT_TABLE');
3 end;
4 /

PL/SQL 過程已成功完成。

SQL> begin
2 dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'T1');
3 end;
4 /

PL/SQL 過程已成功完成。

2、匯出表分析資訊到stat_table中

SQL> begin
2 dbms_stats.export_table_stats(ownname=>'TEST',tabname=>'T1',stattab=>'STAT_TABLE');
3 end;
4 /

PL/SQL 過程已成功完成。

SQL> select count(*) from TEST.STAT_TABLE;

COUNT(*)
----------
4

EXPORT_COLUMN_STATS：匯出列的分析資訊
EXPORT_INDEX_STATS：匯出索引分析資訊
EXPORT_SYSTEM_STATS：匯出系統分析資訊
EXPORT_TABLE_STATS：匯出表分析資訊
EXPORT_SCHEMA_STATS：匯出方案分析資訊
EXPORT_DATABASE_STATS：匯出資料庫分析資訊
IMPORT_COLUMN_STATS：匯入列分析資訊
IMPORT_INDEX_STATS：匯入索引分析資訊
IMPORT_SYSTEM_STATS：匯入系統分析資訊
IMPORT_TABLE_STATS：匯入表分析資訊
IMPORT_SCHEMA_STATS：匯入方案分析資訊
IMPORT_DATABASE_STATS：匯入資料庫分析資訊
GATHER_INDEX_STATS：分析索引資訊
GATHER_TABLE_STATS：分析表資訊，當cascade為true時，分析表、列（索引）資訊
GATHER_SCHEMA_STATS：分析方案資訊
GATHER_DATABASE_STATS：分析資料庫資訊
GATHER_SYSTEM_STATS：分析系統資訊

4、刪除分析資訊

SQL> begin
2 dbms_stats.delete_table_stats(ownname=>'TEST',tabname=>'T1');
3 end;
4 /

PL/SQL 過程已成功完成。

SQL> SELECT num_rows,blocks,empty_blocks as empty, avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE wner = 'TEST'
AND table_name = 'T1';

NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------

沒有查到分析資料

5、匯入分析資訊

SQL> begin
2 dbms_stats.import_table_stats(ownname=>'TEST',tabname=>'T1',stattab=>'STAT_TABLE');
3 end;
4 /

PL/SQL 過程已成功完成。

SQL> SELECT num_rows,blocks,empty_blocks as empty, avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE wner = 'TEST'
AND table_name = 'T1';

NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
1000 5 0 0 0 16
可以查到分析資料

oracle dbms_stats 包

相關文章