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

一.说明

在之前的blog：

Oracle Statistic统计信息小结

http://blog.youkuaiyun.com/xujinyang/article/details/6881672

里对统计信息的收集有说明，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.youkuaiyun.com/xujinyang/article/details/7090737

以上说明，都是讲Oracle自动收集这块，但有时候，自动收集也不太靠谱，因为默认情况下只在晚上10点到早上6点。如果在其他时间表的更新很频繁，这样数据的信息也不准确。产生的执行计划可能与实际的也就可能有出入。

二.手工收集统计信息脚本

通过如下SQL 可以查看统计信息的收集情况：

view plain

1. /*Formattedon2011/11/2412:03:16(QP5v5.185.11230.41888)*/
2. SELECT/*+UNNEST*/
3. DISTINCTTABLE_NAME,LAST_ANALYZED,STALE_STATS
4. FROMDBA_TAB_STATISTICS
5. WHERELAST_ANALYZEDISNULLORSTALE_STATS='YES'ANDOWNER='XEZF'

一般情况下，当表分析以后，在查看dba_tables表的num_rows列时会显示表中记录数，我们可以拿这个数值与count(*)的结果进行比较，如果2者相差较大，就说明，该表的统计信息陈旧，需要去收集统计信息。

`

2.1存储过程

CREATEORREPLACEPROCEDUREproc_manual_gather_stats

AS

t_countnumber;

t_num_rowsnumber;

t_tablenamevarchar2(50);

t_sqlvarchar2(200);

CURSORc1ISselect*fromdba_tableswhereowner='DAVE';

BEGIN

/**

过程内容：判断统计信息是否同步，如不同步，手工收集统计信息

作者：Tianlesoftware

时间：2011-5-25

*/

FORxINc1

LOOP

t_tablename:=x.TABLE_NAME;

t_num_rows:=x.num_rows;

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

Executeimmediatet_sqlintot_count;

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

ifabs(t_count-t_num_rows)>=10000then

--当统计信息中的记录数与表中实际的记录数差距超过10000时，就分析该表

dbms_stats.gather_table_stats('DAVE',t_tablename);

endif;

ENDLOOP;

EXCEPTION

WHENNO_DATA_FOUND

THEN

DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');

RETURN;

WHENOTHERS

THEN

DBMS_OUTPUT.PUT_LINE('OTHERS');

RETURN;

END;

/

2.2使用Scheduler Job部署

Oracle 10g Scheduler特性

http://blog.youkuaiyun.com/xujinyang/article/details/6830222

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');