[Oracle] 統計資訊和dbms_stats包

1、統計資訊的作用

Oracle基於CBO的最佳化器在產生執行計畫時，很大程度上依賴於統計資訊，你可以把CBO理解為一個複雜的數學模型，而統計資訊是它最主要的輸入，執行計畫是輸出，如果輸入都不準確，輸出還可能準確嗎？所有，統計資訊是否及時有效對執行計畫的好壞有著關鍵的影響。

2、dbms_stats包

Oracle裡採用dbms_stats包分析統計資訊(Analyze命令已淘汰，不建議使用），該包的使用方法，官方文檔有詳細說明（http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_stats.htm#i1036461），這裡挑幾個最常用的說說。

1）gather_table_stats

DBMS_STATS.GATHER_TABLE_STATS (
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT NULL,
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE
1',
degree NUMBER DEFAULT NULL,
granularity VARCHAR2 DEFAULT 'DEFAULT',
cascade BOOLEAN DEFAULT FALSE,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT FALSE);

這個包最常用，它是對錶收集統計資訊，文法如上所示，它的關鍵參數如下：

• method_opt (長條圖histogram選項）

先說說什麼是長條圖，長條圖是對列上的資料分布進行統計，讓最佳化器知道資料在各個列上的分布情況，如果資料在某列上的分布很傾斜，則最好對該列收集長條圖資訊。

method_opt有如下選項：

 

1. for all columns -> 統計該表所有列的長條圖
2. for all indexed columns -> 統計該表上定義索引列的長條圖
3. for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:
   
   • N：長條圖桶數，取值範圍[1,254]，1相當於不收集長條圖
   • REPEAT：只收集原本有長條圖資訊的列;
   • AUTO：由Oracle自行決定N的大小
   • SKEWONLY： 只收集非均勻分布列的長條圖，系統自動決定桶數(bucket )

樣本1：在表t上收集統計資訊，但不收集長條圖：exec dbms_stats.gather_table_stats(user,'t',method_opt=>'for all columns size 1');
樣本2：以最大桶數收集長條圖：exec dbms_stats.gather_table_stats(user, 't', method_opt=>'for all columns size 254');

• granularity (統計資訊收集的粒度，針對分區表）

該參數主要針對分區表，分區表有以下三種類型的統計資訊

1. global -> 全域統計資訊
2. partition -> 分區統計資訊
3. sub-partition -> 子分區統計資訊

• cascade (是否同時收集索引的統計資訊）

該值為true等同於在該表上所有的索引上執行gather_index_stats。

1 2 下一頁