收集统计信息方案

总体设想：尽量把分析表可能消耗的资源和对系统的影响降到最低
方案：对需要分析的表执行监控，然后定期分析oracle认为需要分析的表

主要用到DBMS_JOB+SP，重点在于既保证分析结果的准确性，又不会太影响业务的运转。

实现过程很简单……

总体设想：尽量把分析表可能消耗的资源和对系统的影响降到最低
方案：对需要分析的表执行监控，然后定期分析oracle认为需要分析的表

一、对没有监控的表添加监控
注意：只需要监控需要的用户下的表，千万不能对系统表进行监控

需要注意地方：
对monitoring状态下的表，对表进行分析后，该表就会从user_tab_modifications视图中删除，知道对该表有新的DML操作。
user_tab_modifications会有几分钟的延迟时间，如果需要即时得到已经被修改，但仍未刷新到user_tab_modifications的记录数，则需要执行过程：DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

二、分析没有统计信息的表

三、重新分析修改量超过10%的表

四、定义作业
每周日凌晨定时执行：
var v_job number;
begin
dbms_job.submit(:v_job,'p_monitor_and_stat(''suk'');',trunc(sysdate,'DY'),'trunc(sysdate,''DY'')+7');
end;

存储过程如下：
create or replace procedure p_monitor_and_stat(sname Varchar2) Is
/*
作用：对某个用户下的表进行监测、分析
作者：suk

*/
l_monitor_cmd varchar2(500);--sql for table monitoring
l_stat_empty Varchar2(500);--sql for gather empty-statistics table
l_stat_stale Varchar2(500);--sql for gather stale-statistics table
cursor c1 Is select 'alter table '||owner||'.'||table_name||' monitoring' col1
from all_tables
where
owner in (upper(sname))
and monitoring='NO'
and table_name not like 'MLOG$_%'
and table_name not like 'RUPD$%'
and temporary='N';--get the table list that need to be analyzed
begin
for c1_rec in c1 loop
l_monitor_cmd:=c1_rec.col1;

begin
execute immediate l_monitor_cmd;
exception when others Then
dbms_output.put_line('ERROR => '||sqlerrm||' '||sqlcode||' for statement :'||l_monitor_cmd);
end;
/* 循环收集各表的统计信息，其中一个表出错不会影响其他表的收集*/
end loop;
dbms_output.put_line('monitoring table complete');
/*刷新已经在sga中被修改，但没有刷新到user_tab_modifications的记录数到user_tab_modifications的记录数中，
避免在执行本过程前一小段时间内发生大量DML操作，但不能及时更新到user_tab_modifications的情况
*/
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
dbms_output.put_line('flush complete');
/*收集没有分析过的表的统计信息*/
l_stat_empty:='begin dbms_stats.gather_schema_stats(ownname=>'''||sname||''',options=>''gather empty''); end;';
Execute Immediate l_stat_empty;
dbms_output.put_line('gather empty compelete');
/*重新分析修改量超过10%的表（这些修改包括插入、更新和删除）*/
l_stat_stale:='begin dbms_stats.gather_schema_stats(ownname=>'''||sname||''',options=>''gather stale'');end;';
Execute Immediate l_stat_stale;
dbms_output.put_line('gather stale compelete');

end p_monitor_and_stat;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/231499/viewspace-63696/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/231499/viewspace-63696/