Oracle 判断 并 手动收集 统计信息 脚本

CREATE OR REPLACE PROCEDURE SchameB.PRC_GATHER_STATS
AUTHID CURRENT_USER IS
BEGIN
    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 可以查看统计信息的收集情况：

     一般情况下，当表分析以后，在查看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