ORACLE 收集統計資訊

今天網上看到一篇關於收集統計資訊的文章，還不錯，特轉載下來。

1.     理解什麼是統計資訊
最佳化器統計資訊就是一個更加詳細描述資料庫和資料庫物件的集合，這些統計資訊被用於查詢最佳化器，讓其為每條SQL語句選擇最佳的執行計劃。最佳化器統計資訊包括:

·         表的統計資訊
o   行數
o   Block數

o   行平均長度
·         列的統計資訊
o   列中不同值的數量
o   列中null的數量

o   資料分佈(柱狀圖/直方圖)

·         索引的統計資訊
o   葉子塊的數量
o   索引的高度
o   聚簇因子(clustering factor)

·         系統的統計資訊
o   I/O效能和利用

o   CPU效能和利用

最佳化器統計資訊儲存在下列資料字典中
·         DBA_TABLES

·         DBA_OBJECT_TABLES

·         DBA_TAB_STATISTICS

·         DBA_TAB_COL_STATISTICS

·         DBA_TAB_HISTOGRAMS

·         DBA_INDEXES

·         DBA_IND_STATISTICS

·         DBA_CLUSTERS

·         DBA_TAB_PARTITIONS

·         DBA_TAB_SUBPARTITIONS

·         DBA_IND_PARTITIONS

·         DBA_IND_SUBPARTITIONS

·         DBA_PART_COL_STATISTICS

·         DBA_PART_HISTOGRAMS

·         DBA_SUBPART_COL_STATISTICS

·         DBA_SUBPART_HISTOGRAMS

·         INDEX_STATS              儲存ANALYZE ..VALIDATE STRUCTURE統計資訊

·         AUX_STATS$               儲存CPU統計資訊

·         X$KCFIO                  儲存I/O統計資訊

因為資料庫中的物件會經常的變化，所以統計資訊必須有規律的更新以便更加準確的描述這些資料庫物件。統計資訊預設是由ORACLE自動維護的，不過我們也可以用DBMS_STATS包手動收集統計資訊。DBMS_STATS包同樣提供了過程來維護統計資訊。關於DBMS_STATS包更詳細的描述請參閱官方文件PL/SQL Packages and Types Reference部分。
2. 自動收集統計資訊
Oracle10g中，在安裝Oracle的時候，就預設建立了一個名為GATHER_STATS_JOB的job來自動收集最佳化器統計資訊。這個job收集資料庫中所有物件的統計資訊。預設的情況下這個job是週一到週五每天晚上10點到第二天早上6點以及整個週末來收集統計資訊。

可以檢視DBA_SCHEDULER_JOBS， DBA_SCHEDULER_PROGRAMS,DBA_SCHEDULER_WINDOWS，DBA_SCHEDULER_JOB_RUN_DETAILS等檢視來檢視JOB設定以及執行資訊。

自動收集過期的統計資訊依賴於表監控特徵，在Oracle10g中表監控預設是開啟的，同時它也依賴STATISTICS_LEVEL引數的值，10g中預設為typical,只有將STATISTICS_LEVEL引數設定為ALL或者TYPICAL才能讓ORACLE識別過期的統計資訊。

3. 關閉自動收集統計資訊
在某些情況下，我們想關閉自動收集統計資訊那麼我們可以利用如下方法:

   BEGIN

      DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

   END;

   /

4. 何時該手動收集統計資訊
有時候自動收集統計並不合適，因為自動收集統計資訊是在午夜執行的，然而由於物件是在白天被修改了，導致導致的統計資訊變得陳舊，這裡有2種這類物件:

·         白天經常被delete,或者truncated之後又rebuild的表(經常變化的表)

·         批次操作之後有10%或者以上的資料被更改的表(批次處理的表)

·         對於經常變化的表，可以將其統計資訊設定為null，當ORACLE遇到一個表沒有統計資訊，ORACLE會動態取樣以便為查詢最佳化器收集必要的統計資訊。動態取樣這個特徵受到引數optimizer_dynamic_sampling的控制，它的預設值為2，同時呢optimizer_mode也能控制動態取樣，可將其設定為all.

以SCOTT使用者下的DEPT表為例，將一個表的統計資訊設定為null的方法如下：

BEGIN  DBMS_STATS.DELETE_TABLE_STATS('SCOTT','DEPT');  DBMS_STATS.LOCK_TABLE_STATS('SCOTT','DEPT');END;/我們也可以在表具有典型的，代表性的時候收集統計資訊，並且鎖住其統計資訊，因為在夜晚自動收集的統計資訊未必適用於白天的負載，而典型的統計資訊具有代表意義，所以這個時候採取lock其典型的統計資訊更能讓CBO選擇更優的執行計劃。

至於上面的兩種方法用哪種，這個還需要根據業務，實際情況分析之。
·         對於批次處理的表 ，應該在批次處理完成的時候立即對其收集統計資訊，可以將收集統計資訊的指令碼繫結到批次處理的指令碼中。
·         對於外部表，只能透過gather_table_stats過程來收集統計資訊，並且外部表不支援取樣，所以需要把gather_table_stats中的estimate_percent設定為null。

·         系統的統計資訊也需要手動收集，因為這些資訊是不會自動收集的。
·         對於固定物件，比如說動態效能表，需要手動的執行gather_fixed_objects_stats過程來收集。固定的物件反映了當前資料庫的活動。當資料庫活動處於具有代表性的時候，就應該收集這類統計資訊。

 

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，最佳化器會自動的根據新的統計資訊選擇一條新的執行計劃。對於分散式的資料庫，不會作廢。

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

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

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

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

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

·         我們利用上面的過程收集統計資訊的時候有幾個需要關心的引數
o   取樣
o   並行
o   分割槽
o   列統計以及直方圖/柱狀圖
o   過期的統計
o   自定義統計
Ø  在收集統計資訊的操作過程中我們可以使用取樣來評估統計資訊。取樣對於收集統計資訊來說是一項很重要的技術。如果在收集統計資訊的時候不使用取樣，那麼就需要對錶進行全表掃描，以及排序整個表。透過取樣可以降低收集必要的統計資訊所花費的資源。
控制取樣的引數是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過程來收集統計資訊。

·         對於經常批次操作的表，那麼表的統計資訊就必須在批次操作之後對其收集統計資訊。
·         對於分割槽表，通常只有一個分割槽被修改，這種情況下可以只收集單獨分割槽的統計資訊，不過收集整個表的統計資訊還是非常有必要的。
·         最後我會給出兩個指令碼，判斷該表是否需要收集統計資訊。
8. 收集統計資訊的一些例子
例子1對錶收集統計資訊

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
                                 tabname => 'DEPT',
                                 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                 method_opt => 'for all columns size repeat',
                                 degree => DBMS_STATS.AUTO_DEGREE,
                                 cascade=>TRUE
                                 );
END;
/

上面的例子收集SCOTT.DEPT表的統計資訊。這裡面值得關注的一個引數就是method_opt。這個引數控制是否收集列的直方圖資訊。通常情況下，是不會收集直方圖的，關於直方圖不是三言兩語可以說明白的。它的四個選項method_opt=>'for all columns size skewonly' 

ORACLE會根據資料分佈收集直方圖
method_opt=>'for all columns size repeat'

只有以前收集過直方圖，才會收集直方圖資訊，所以一般我們會設定method_opt 為repeat
method_opt=>'for all columns size auto' 

ORACLE會根據資料分佈以及列的workload來確定是否收集直方圖
method_opt=>'for all columns size interger'

我們自己指定一個bucket值
例子2對某一個schma收集統計資訊
BEGIN
   DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT',
                                  estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
                                  ptions => 'gather auto',
                                  degree  => DBMS_STATS.AUTO_DEGREE,
                                  method_opt => 'for all columns size repeat',
                                  cascade => TRUE
                                 );
END;                                
/

上面的例子收集SCOTT模式下所有物件的統計資訊。裡面值得注意的一個引數就是options。前面已經講到過，他與表監控有關。它有四個選項
Options =>’gather’       收集所有物件的統計資訊
Options =>’gather empty’ 只收集還沒被統計的表
Options =>’gather stale’ 只收集修改量超過10%的表
Options =>’gather auto’  相當於empty+stale ，所以我們一般設定為AUTO。
例子3 對一個分割槽表收集統計資訊
BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',
                                 tabname => 'P_TEST',
                                 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                 method_opt => 'for all columns size repeat',
                                 degree => DBMS_STATS.AUTO_DEGREE,

                                 granularity => 'ALL',
                                 cascade=>TRUE
                                 );
END;
/

上面的例子收集ROBINSON.P_TEST表的統計資訊。裡面值得注意的一個引數就是granularity，他有7個選項。
granularity => 'ALL'  收集分割槽，子分割槽，全域性的統計資訊
granularity => 'AUTO' 這個是預設的設定，ORACLE會根據分割槽型別來決定用ALL,GLOBAL AND PARTITION ,還是其他的
granularity => 'DEFAULT' 這個是過期了的
granularity => 'GLOBAL' 收集全域性統計資訊
granularity => 'GLOBAL AND PARTITION' 收集全域性，分割槽統計資訊，但是不收集子分割槽統計資訊
granularity => 'PARTITION' 收集分割槽統計資訊
granularity => 'SUBPARTITION' 收集子分割槽統計資訊
當然我們可以指定partname，自己控制對哪個分割槽收集統計資訊
9. 列出表需要收集統計資訊的指令碼
普通表
set serveroutput on

declare

   -----select OVER THE Change RATE TABLES---------------

   cursor overchangerate is

 select a.table_owner, a.table_name, a.inserts,a.updates,a.deletes ,b.num_rows

                from dba_tab_modifications a, dba_tables b

               where a.table_name = b.table_name

                 and table_owner not in

                     ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

                      'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',

                      'OUTLN', 'TSMSYS', 'MDSYS')

                 and inserts > 0 and partitioned='NO' and a.inserts/decode(b.num_rows,0,1,b.num_rows)>=0.1

                 or a.table_name = b.table_name

                 and table_owner not in

                     ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

                      'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',

                      'OUTLN', 'TSMSYS', 'MDSYS')

                 and updates > 0 and partitioned='NO' and a.updates/decode(b.num_rows,0,1,b.num_rows)>=0.1 or

                 a.table_name = b.table_name

                 and table_owner not in

                     ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

                      'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS',

                      'OUTLN', 'TSMSYS', 'MDSYS')

                 and deletes > 0 and partitioned='NO' and a.deletes/decode(b.num_rows,0,1,b.num_rows)>=0.1 ;

    ----select the unanalyzed table---------------

    cursor nullmonitor is

      select owner, table_name

        from dba_tables

       where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS',

              'XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP',

              'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS')

         and last_analyzed is null;

  begin

    dbms_output.enable(1000000);

    ----flush the monitorring information into the dba_tab_modifications

    DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

    ----display the unanalyzed table--------------

    dbms_output.put_line('- - - - - - - - - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');

  

    dbms_output.put_line('Unalalyzed tables:');

    for v_null in nullmonitor loop

      dbms_output.put_line(v_null.owner || '.' || v_null.table_name ||

                           ' has not been analyzed, consider gathering statistics');

    end loop;

    ----display the  information-------------------

    dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - -  - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' );

    dbms_output.put_line('Over the Change_Rate 10%:');

    for v_topinsert in overchangerate loop

      dbms_output.put_line(v_topinsert.table_owner || '.' || v_topinsert.table_name || ' once has ' || v_topinsert.num_rows || ' rows, ' ||

                           'till now inserted ' || v_topinsert.inserts || ' rows, updated  ' || v_topinsert.updates || ' rows, deleted ' || v_topinsert.deletes ||   

                           ' rows. consider gathering statistics');

    end loop;

     dbms_output.put_line('- - - - - - - - - - - - - - - - - - -  - - - - - - - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');

  end;

/

 

下面的是分割槽表
set serveroutput on

declare

   -----select OVER THE Change RATE TABLES---------------

   cursor overchangerate is

select a.table_owner,a.table_name,a.partition_name,sum(a.inserts) inserts,sum(a.updates) updates,sum(a.deletes) deletes,sum(b.num_rows) num_rows

from dba_tab_modifications a,dba_tab_partitions b where a.table_owner =b.table_owner and a.table_name=b.table_name

and a.partition_name=b.partition_name and   a.table_owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS', 'XDB',

'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS','OUTLN', 'TSMSYS', 'MDSYS')

group by a.table_owner,a.table_name,a.partition_name

having (sum(a.inserts)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1

or

(sum(a.updates)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1

or

(sum(a.deletes)/decode(sum(b.num_rows),0,1,sum(b.num_rows)))>=0.1

order by a.table_name;

  begin

    dbms_output.enable(1000000);

    ----flush the monitorring information into the dba_tab_modifications

    DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

    ----display the top_n_insert information-------------------

    dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - -  - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' );

    dbms_output.put_line('Over the Change_Rate 10%:');

    for v_topinsert in overchangerate loop

      dbms_output.put_line(v_topinsert.table_owner || '.' || v_topinsert.table_name || ' partition ' || v_topinsert.partition_name  || ' once has ' || v_topinsert.num_rows || ' rows, ' ||

                           'till now inserted ' || v_topinsert.inserts || ' rows, updated  ' || v_topinsert.updates || ' rows, deleted ' || v_topinsert.deletes ||   

                           ' rows. consider gathering statistics');

    end loop;

    dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - -  - - - - -  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -' );

  end;

/

 

在此特別宣告一點，在oracle11.2版本中有一個相關的BUG

 

順便貼上10個level的動態取樣介紹

Level 0: Do not use dynamic sampling.

Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).

Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.

Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.

Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.

Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.

Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.