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

標籤：

CREATE OR REPLACE PROCEDURE SchameB.PRC_GATHER_STATSAUTHID CURRENT_USER ISBEGIN    SYS.DBMS_STATS.GATHER_TABLE_STATS(‘SchName‘, ‘TableName‘, CASCADE => TRUE);END;/

 

select owner,table_name,last_analyzed,num_rows from dba_tables where owner=‘SYSTEM‘ and table_name=‘AQ$_INTERNET_AGENTS‘

last_analyzed：相關表的資訊最後被統計的時間；

num_rows：最佳化器中存放的 表中的 記錄數（可能與實際情況不符）

 

 

When you EXEC the DBMS_STATS procedure directly, it‘s running as an anonymous block, and those always run with invoker‘s rights - honouring roles.

 

一.  說明

 

在之前的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 可以查看統計資訊的收集情況：

 

[sql] view plain copy 

1. /* Formatted on 2011/11/24 12:03:16 (QP5 v5.185.11230.41888) */  
2. SELECT /*+ UNNEST */  
3.       DISTINCT TABLE_NAME, LAST_ANALYZED, STALE_STATS  
4.   FROM DBA_TAB_STATISTICS  
5.  WHERE LAST_ANALYZED IS NULL OR STALE_STATS = ‘YES‘ AND OWNER = ‘XEZF‘  

 

     一般情況下，當表分析以後，在查看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‘);

http://blog.csdn.net/tianlesoftware/article/details/6445868

 

需要用到dbms_stats這個包的GATHER_TABLE_STATS過程，其中擁有者和表名必須填。

PROCEDURE GATHER_TABLE_STATS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
TABNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN DEFAULT
ESTIMATE_PERCENT NUMBER IN DEFAULT
BLOCK_SAMPLE BOOLEAN IN DEFAULT
METHOD_OPT VARCHAR2 IN DEFAULT
DEGREE NUMBER IN DEFAULT
GRANULARITY VARCHAR2 IN DEFAULT
CASCADE BOOLEAN IN DEFAULT
STATTAB VARCHAR2 IN DEFAULT
STATID VARCHAR2 IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT
STATTYPE VARCHAR2 IN DEFAULT
FORCE BOOLEAN IN DEFAULT

exec dbms_stats.gather_table_stats(‘SCOTT‘,‘D‘);

 

表的統計資訊不是即時取的。所以有時候剛剛delete掉表中資料，並不能即時從user_tables中的num_rows反應出來。這時候收集下表的統計資訊即可。

 

SQL> select table_name ,num_rows from user_tables where table_name=‘D‘;

TABLE_NAME NUM_ROWS
--------------- ----------
D 4

SQL> select * from d;

DEPTNO DNAME
---------- ----------------------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS

SQL> delete from d where deptno=30;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from d;

DEPTNO DNAME
---------- ----------------------------
10 ACCOUNTING
20 RESEARCH
40 OPERATIONS

SQL> select table_name ,num_rows from user_tables where table_name=‘D‘;

TABLE_NAME NUM_ROWS
--------------- ----------
D 4

這時候行數還是4。我們收集下統計資訊。

SQL> exec dbms_stats.gather_table_stats(‘SCOTT‘,‘D‘);//也可以使用call命令

PL/SQL procedure successfully completed.

SQL> select table_name ,num_rows from user_tables where table_name=‘D‘;

TABLE_NAME NUM_ROWS
--------------- ----------
D 3

 

 

==============================================================================================

還原剛才刪掉的資料……

 

SQL> alter session set nls_date_format =‘yyyy-mm-dd hh24:mi:ss‘;

Session altered.

SQL> SQL>
SQL> select sysdate from dual;

SYSDATE
-------------------
2012-02-28 05:01:49

SQL> select * from d as of timestamp to_timestamp(‘2012-02-28 04:50:00‘,‘yyyy-mm-dd hh24:mi:ss‘) where deptno=30;

DEPTNO DNAME
---------- ----------------------------
30 SALES

SQL> insert into d select * from d as of timestamp to_timestamp(‘2012-02-28 04:50:00‘,‘yyyy-mm-dd hh24:mi:ss‘) where deptno=30;

1 row created.

SQL> select * from d;

DEPTNO DNAME
---------- ----------------------------
10 ACCOUNTING
20 RESEARCH
40 OPERATIONS
30 SALES

SQL> commit;

Commit complete.

 

http://www.linuxidc.com/Linux/2012-12/76912.htm

 

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