Oracle 判斷 並 手動收集 統計資訊 指令碼

 在之前的blog：

            Oracle Statistic 統計資訊 小結

            http://blog.csdn.net/tianlesoftware/archive/2009/10/15/4668723.aspx

 

            裡對統計資訊的收集有說明， Oracle 的Automatic Statistics Gathering 是通過Scheduler 來實現收集和維護的。 Job 名稱是GATHER_STATS_JOB, 該Job收集資料庫所有物件的2種統計資訊：

            （1）Missing statistics（統計資訊缺失）

            （2）Stale statistics（統計資訊陳舊）

            該Job 是在資料庫建立的時候自動建立，並由Scheduler來管理。Scheduler 在maintenance windows open時執行gather job。 預設情況下，job 會在每天晚上10到早上6點和週末全天開啟。該過程首先檢測統計資訊缺失和陳舊的物件。然後確定優先順序，再開始進行統計資訊。

 

           Scheduler Job的 stop_on_window_close 屬性控制GATHER_STATS_JOB 是否繼續。該屬性預設值為True. 如果該值設定為False，那麼GATHER_STATS_JOB 會中斷， 而沒有收集完的物件將在下次啟動時繼續收集。

 

            Gather_stats_job 呼叫dbms_stats.gather_database_stats_job_proc過程來收集statistics 的資訊。 該過程收集物件statistics的條件如下：

            （1）物件的統計資訊之前沒有收集過。

            （2）當物件有超過10%的rows 被修改，此時物件的統計資訊也稱為stale statistics。

 

在Oracle 10g中，在查詢表時，如果沒有統計分析，那麼會採用動態取樣。

            Oracle 分析及動態取樣

            http://blog.csdn.net/tianlesoftware/archive/2010/08/28/5845028.aspx

 

 

            以上說明，都是講Oracle 自動收集這塊，但有時候，自動收集也不太靠譜，因為預設情況下只在晚上10點到早上6點。 如果在其他時間表的更新很頻繁，這樣資料的資訊也不準確。 產生的執行計劃可能與實際的也就可能有出入。

 

 

二. 手工收集統計資訊指令碼

通過如下SQL 可以檢視統計資訊的收集情況：

     一般情況下，當表分析以後，在檢視dba_tables 表的num_rows 列時會顯示錶中記錄數，我們可以拿這個數值與count(*) 的結果進行比較，如果2者相差較大，就說明，該表的統計資訊陳舊，需要去收集統計資訊。

`

 

2.1 儲存過程

CREATE OR REPLACE PROCEDURE proc_manual_gather_stats

AS

    t_count number; 

    t_num_rows number;

    t_tablename varchar2(50);

    t_sql varchar2(200);

   

   CURSOR c1  IS  select * from dba_tables where owner='DAVE';

     

BEGIN

   /**

   過程內容： 判斷統計資訊是否同步，如不同步，手工收集統計資訊

   作者： Tianlesoftware

   時間：2011-5-25

   */

  

   FOR x IN c1

   LOOP

     t_tablename := x.TABLE_NAME;

     t_num_rows := x.num_rows;

    t_sql :='select count(*)  from '||t_tablename;

    Execute immediate t_sql into  t_count ;

           --DBMS_OUTPUT.PUT_LINE( 't_tablename:--'|| t_tablename ||'--'||' t_num_rows is:t_count :-- '||   t_num_rows ||': ' ||  t_count );

  

     if  abs(t_count - t_num_rows) >=10000 then

-- 當統計資訊中的記錄數與表中實際的記錄數差距超過10000時，就分析該表

        dbms_stats.gather_table_stats('DAVE',t_tablename);

     end if;  

   END LOOP;

  

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

   DBMS_OUTPUT.PUT_LINE ('NO_DATA_FOUND');

      RETURN;

   WHEN OTHERS

   THEN

   DBMS_OUTPUT.PUT_LINE ('OTHERS');

      RETURN;

END;

/

 

 

2.2 使用Scheduler Job 部署

Oracle 10g Scheduler 特性

http://blog.csdn.net/tianlesoftware/archive/2009/10/22/4715218.aspx

 

2.2.1 建立Job

 

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'JOB_MANUAL_GATHER_STATS',

job_type => 'STORED_PROCEDURE',

job_action => 'PROC_MANUAL_GATHER_STATS',  --呼叫的過程名稱

start_date => sysdate,

repeat_interval => ''FREQ=HOURLY;INTERVAL=1');

            -- 每個一小時執行一次

END;

/

            這個時間要根據自己的業務來判斷，因為分析本身就會消耗CPU 資源。 所以儘量避免業務高峰期來執行，最好是在業務低的時候執行。

 

 注意：

            JOB 雖然成功建立了，但卻並未執行.因為ENABLED 引數當不顯式指定時，該引數的預設值為false。

 

2.2.2 啟用Job

exec dbms_scheduler.enable('JOB_MANUAL_GATHER_STATS');

 

2.2.3 停止Job

exec dbms_scheduler.disable('JOB_MANUAL_GATHER_STATS');