Oracle 11g 統計資訊收集指令碼

在 Oracle 11g，系統自帶的 JOB 裡面有收集統計資訊的任務，但並不一定每天每天都會收集。
在 DBMS_STATS 包中有一個 STALE_PERCENT 引數，這個引數的意義是：當表中的資料量修改超過總資料量的一定比例數，會再次觸發統計資訊收集，預設值為10%。
這樣對於大表來說就有一個問題，收集的時間不會很及時，可能會隔好幾天才會收集一次，這樣會導致表的統計資訊不準確，對資料庫的 SQL 解析產生影響。
大表通常都是分割槽表，下面的收集方案主要是針對分割槽表，也涵蓋了普通表。

--方案一

對於非分割槽表，使用預設的統計資訊採集方法；對於分割槽表，使用增量採集方法，只對資料有變動的分割槽做收集。

BEGIN
  --採集非分割槽表的統計資訊
  FOR i IN (select s.TABLE_NAME, s.num_rows
              from dba_tables s
             where s.OWNER = 'ACCT'
               and not exists
             (select distinct TABLE_NAME
                      from dba_tab_partitions p
                     where table_owner = 'ACCT'
                       and p.table_name = s.TABLE_NAME)) LOOP
    DBMS_STATS.GATHER_TABLE_STATS('ACCT',
                                  i.TABLE_NAME,
                                  DEGREE       => 5,
                                  CASCADE      => TRUE);
  END LOOP;
  --採集分割槽表的統計資訊  
  FOR j IN (select distinct TABLE_NAME
              from dba_tab_partitions p
             where table_owner = 'ACCT') LOOP
    DBMS_STATS.GATHER_TABLE_STATS('ACCT',
                                  tabname          => j.table_name,
                                  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                  GRANULARITY      => 'AUTO',
                                  DEGREE           => 5,
                                  CASCADE          => TRUE);
  END LOOP;
END;

--對於分割槽表，設定 INCREMENTAL 引數只對資料有變動的分割槽做收集

在對分割槽表收集統計資訊的時候，如果想讓 Oracle 只掃描發生資料改變的分割槽，避免全表掃描，需要滿足以下條件：
(1) 指定分割槽表的 INCREMENTAL 引數設定為 TRUE
(2) 指定分割槽表的 PUBLISH 設定為 TRUE
(3) ESTIMATE_PERCENT 引數設為 AUTO_SAMPLE_SIZE，GRANULARITY 引數設為 AUTO

--統計資訊收集設定選項

CASCADE 索引的統計資訊是否和表一起收集。
DEGREE 收集的並行度。
ESTIMATE_PERCENT 決定收集的比例，範圍是[0.000001,100]。可以透過DBMS_STATS.AUTO_SAMPLE_SIZE來讓oracle自動決定收集的比例。這是預設值。
METHOD_OPT 控制列和柱狀圖的收集方法。
GRANULARITY 收集的粒度。
PUBLISH 當統計資訊收集JOB執行完畢後，是否更新新的統計資訊。
INCREMENTAL 決定是否維持分割槽表的全域性統計資訊,而不做全表掃描。當開啟這個引數的時候，Oracle僅需掃描分割槽表中有資料變化的分割槽來更新分割槽表的統計資訊，而無需掃描整個分割槽表。
STALE_PERCENT 當表的資料量修改超過總資料量的一定比例數，再次觸發統計資訊收集，預設值為10%。

--查詢相關引數

select dbms_stats.get_prefs('PUBLISH') from dual;

select dbms_stats.get_prefs('method_opt') from dual;

select dbms_stats.get_prefs('GRANULARITY') from dual;

select dbms_stats.get_prefs('INCREMENTAL') from dual;

--檢視分割槽表INCREMENTAL、PUBLISH的值

select dbms_stats.get_prefs('INCREMENTAL','ACCT','ELMP_TRANS_CUSTOMACCOUNTFLOW') from dual;

select dbms_stats.get_prefs('PUBLISH','ACCT','ELMP_TRANS_CUSTOMACCOUNTFLOW') from dual;

--exec DBMS_STATS.SET_PARAM('INCREMENTAL','TRUE');

--只收集資料變動的分割槽

BEGIN
  FOR j IN (select distinct TABLE_NAME
              from dba_tab_partitions p
             where table_owner = 'ACCT') LOOP
    DBMS_STATS.SET_TABLE_PREFS('ACCT', j.table_name, 'INCREMENTAL', 'TRUE');
  END LOOP;
END;

--方案二

只修改 STALE_PERCENT 引數，不建立 JOB。

STALE_PERCENT 當表的資料量修改超過總資料量的一定比例數，再次觸發統計資訊收集，預設值為10%。

select dbms_stats.get_prefs('STALE_PERCENT') from dual;

BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','5');
END;

--方案三

對於非分割槽表，使用預設的統計資訊採集方法；對於分割槽表，由於現有分割槽每天會產生一個新的分割槽，在保證所有分割槽的統計資訊都收集的基礎上，每天只對新生成的分割槽做收集。

BEGIN
  --採集非分割槽表的統計資訊
  FOR i IN (select s.TABLE_NAME, s.num_rows
              from dba_tables s
             where s.OWNER = 'ACCT'
               and not exists
             (select distinct TABLE_NAME
                      from dba_tab_partitions p
                     where table_owner = 'ACCT'
                       and p.table_name = s.TABLE_NAME)) LOOP
    DBMS_STATS.GATHER_TABLE_STATS('ACCT',
                                  i.TABLE_NAME,
                                  DEGREE       => 5,
                                  CASCADE      => TRUE);
  END LOOP;
  --採集分割槽表的統計資訊  
  FOR j IN (select distinct table_name,
                            last_value(partition_name) over(partition by table_name order by partition_position ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) partition_name
              from dba_tab_partitions p
             where table_owner = 'ACCT') LOOP
    DBMS_STATS.GATHER_TABLE_STATS('ACCT',
                                  tabname          => j.table_name,
                                  partname         => j.partition_name,
                                  estimate_percent => 0.000001,
                                  DEGREE           => 5,
                                  CASCADE          => TRUE);
  END LOOP;
END;