DM-设置定期收集统计信息作业

       当你要根据表的变动情况进行统计信息的收集，可以设置定时作业进行收集。  

1.创建临时表
--记录最大trxid
create table trxid_jilu (OWNER VARCHAR2(20),TABLE_NAME VARCHAR2(20),trx_id int,table_cnt int,riqi date);
--记录数据变化
create table gather_tab (OWNER VARCHAR2(20),TABLE_NAME VARCHAR2(20),table_cnt_bh int,table_cnt int,flag_gather int default 0);

2.插入数据，需要保留前一天的max(trxid),获取当天的max（trxid），当天的全表数据量，可以在当天晚上进行定时收集，将以下脚本放到定时作业里
begin
  for tb in
        (
                select
                        owner,
                        table_name
                from
                        dba_tables
               where  
                      owner not like 'SYS%'
                   and owner<>'CTISYS'
                  and table_name not like 'GATHER_TAB%'
        )
        loop
                execute immediate 'insert into trxid_jilu(OWNER,TABLE_NAME,trx_id,table_cnt,riqi) 
                select '''||tb.OWNER||''','''||tb.TABLE_NAME||''',
                (select max(trxid) from "'||tb.owner||'"."'||tb.table_name||'"),
                (select count(*) from "'||tb.owner||'"."'||tb.table_name||'"),SYSDATE from dual';
                commit;
                --对于前天收集的数据清理，保留当前的max（trxid）
                execute immediate 'delete from trxid_jilu where riqi=to_char(sysdate-2,''yyyy-mm-dd'')';
                commit;
      end loop;
end;

3.收集统计信息，创建一个存储过程

create or replace procedure pro_gather() as
begin 
   --清空临时表数据
    execute immediate 'truncate table gather_tab';
        for tb in
        (
                select * from trxid_jilu where riqi=to_char(sysdate-1,'yyyy-mm-dd') 
        )
        loop
                --更新gather_tab的table_cnt_bh
               execute immediate 'insert into gather_tab(OWNER,TABLE_NAME,table_cnt_bh,table_cnt) 
                select '''||tb.OWNER||''','''||tb.TABLE_NAME||''',
                (select count(*) from "'||tb.owner||'"."'||tb.table_name||'" where trxid >'||tb.trx_id||'),
                (select count(*) from "'||tb.owner||'"."'||tb.table_name||'") from dual';
                commit;
       --表的数据量不为0，防止除0报错
        if tb.table_cnt<>0 then
         for tb1 in
        (
                select * from gather_tab 
        )
        loop
        --表数据变化超过20%,收集统计信息
              if (tb1.table_cnt_bh/tb1.table_cnt)*100>20 then
                       DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>tb1.owner, TABNAME=>tb1.table_name, ESTIMATE_PERCENT=>100);
                     --更新gather_tab的flag_gather作为收集统计信息的标识，以便后续人工校验
                       execute immediate 'update gather_tab set flag_gather=1 where owner='''||tb1.owner||''' and table_name='''||tb1.table_name||'''';
                       commit;
              end if;
        end loop;
        else 
       exit;
        end if;
         end loop;
end;
定时作业里调用这个存储过程。

4.半夜收集统计信息是最佳事情，到了白天业务高峰期，需要停掉收集统计信息作业，这个脚本也可以设置一个定时作业进行执行。
declare
    v_flag int;
begin
   select count(*) into v_flag from v$sessions where state='ACTIVE' and sql_text like '%PRO_GATHER%';
   if v_flag<>0 then
      for tb in
  (  
      select sess_id from v$sessions where state='ACTIVE' and sql_text like '%PRO_GATHER%'
  ) 
   loop
       call sp_close_session(tb.sess_id);
    end loop;
    end if;
end;