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');
-------------------------------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
Email: dvd.dba@gmail.com
DBA1 群:62697716(满); DBA2 群:62697977(满) DBA3 群:62697850(满)
DBA 超级群:63306533(满); DBA4 群: 83829929 DBA5群: 142216823
DBA6 群:158654907 聊天 群:40132017 聊天2群:69087192
--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请