收集統計資訊方案

總體設想：儘量把分析表可能消耗的資源和對系統的影響降到最低
方案：對需要分析的表執行監控，然後定期分析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;