记录表信息

创建一张表记录业务表，表数据，收集统计信息时间，统计信息收集所用时间，以及报错信息。

drop table if exists sysdba.GATHER_TABLE;
create table sysdba.gather_table (tabid bigint,tab_owner varchar2(200),table_name varchar2(200),
tab_cnt bigint,col_id int,column_name varchar2(200),
gather_time timestamp,gather_flag int default 0,err_flag int default 0,err_message varchar2(1000),gather_start_time timestamp,gather_end_time timestamp,gather_used_time bigint);

该存储过程用来统计表信息和数据量

create or replace procedure sysdba.save_gather_tab(tab_owner varchar2(500))as
declare 
 v_cnt bigint;
begin
execute immediate 'truncate table sysdba.gather_table;';--可以清除表数据
--记录表名，列名,不包含大字段和字段长度大于2000
execute immediate 
'merge into sysdba.gather_table AA
using 
(select B.ID,A.OWNER,A.TABLE_NAME,A.COLUMN_ID,A.COLUMN_NAME,c.last_gathered 
  from SYS.DBA_TAB_COLS A,SYSOBJECTS B1, 
       SYSOBJECTS B,SYSSTATS C 
 where A.TABLE_NAME=B.NAME AND B.ID=C.ID(+) AND A.COLUMN_ID=C.COLID(+)
   AND B.SCHID=B1.ID
   AND A.OWNER=B1.NAME
   AND B.TYPE$=''SCHOBJ''
   AND B.SUBTYPE$=''UTAB'' 
   AND A.OWNER not in (''SYS'', 
                       ''CTISYS'', 
                       ''SYSSSO'', 
                       ''SYSAUDITOR'',''SCHEDULER'',''SYSDBA'',''SYSJOB'') 
   AND A.OWNER in ('''||tab_owner||''')
   AND B.SCHID in (SELECT ID from SYSOBJECTS where name in ('''||tab_owner||''') AND TYPE$=''SCH'')
   AND A.TABLE_NAME not like ''%BK%'' AND A.TABLE_NAME not like ''%BAK%''
   AND A.TABLE_NAME not like ''DMHS%''
   AND A.DATA_length<2000 AND A.data_type not in (''CLOB'',''BLOB'',''TEXT'')
 ) BB on AA.tabid=BB.ID AND AA.TAB_OWNER=BB.owner AND AA.TABLE_NAME=BB.TABLE_NAME
 when MATCHED then update set AA.col_id=BB.column_id,AA.column_name=BB.column_name,AA.gather_time=BB.last_gathered
 when not matched then insert (AA.tabid,AA.tab_owner,AA.table_name,AA.col_id,AA.column_name,AA.gather_time)
 values(BB.id,BB.owner,BB.table_name,BB.column_id,BB.column_name,BB.last_gathered);';
 commit;
 -- 记录表数据
 for i in (select distinct tab_owner,table_name from sysdba.gather_table)
 loop
 execute immediate  'select count(1) from "'||i.tab_owner||'"."'||i.table_name||'";' into v_cnt;
 execute immediate 'update sysdba.gather_table set tab_cnt='||v_cnt||' where tab_owner='''||i.tab_owner||''' and table_name='''||i.table_name||''';';
 commit;
 end loop;
 end;

-- 示例：注意中间'',''分开
 call sysdba.save_gather_tab('TEST'',''TEST2'',''TEST3'); --注意格式

收集统计信息脚本制定

首先制定一个自治事务用来记录报错信息

CREATE OR REPLACE
PROCEDURE SYSDBA.ERR_LOG
        (
                V_ERROR_OWNER VARCHAR2(200),
                V_ERROR_TABLE VARCHAR2(200),
                V_ERROR_COLUMN VARCHAR2(200),
                V_ERROR_MESSAGE VARCHAR2(1000)
                )
IS
        pragma autonomous_transaction; -- 自治事务
BEGIN
       update SYSDBA.GATHER_TABLE set err_flag=1,err_message=V_ERROR_MESSAGE where tab_owner=V_ERROR_OWNER and table_name=V_ERROR_TABLE and column_name=V_ERROR_COLUMN;--更新表信息
        commit;
end;

下面的存储过程收集原则如下：
小于1千万数据的表全表按列收集，大于1千万收集索引列信息。收集一个月没有收集统计信息的表
一般收集统计信息可能遇到报错回滚段过旧的错误，因此自治事务用于记录错误信息。

create or replace
procedure sysdba.p_gather
        (
        )
as
v_start_time timestamp;
v_ss bigint;
v_cnt bigint;--判断是否存在dba_ind_column
BEGIN
       for i in
        (
                select 
                        tab_owner ,
                        table_name,
                        column_name,
                        tab_cnt
                from
                        sysdba.gather_table
                where
                        (
                                gather_time  <trunc(sysdate-30) --一个月前未收集统计信息的
                             or gather_time is null
                        )
                    and gather_flag=0
                    and tab_cnt    >0
                    and ERR_FLAG   =0
                order by
                        tab_cnt asc
        )
        LOOP
                if i.tab_cnt<10000000--判断数据量
                        THEN
                        begin
                                --全表按列收集
                                select sysdate into v_start_time from dual;--开始收集时间
                                execute immediate 'stat 100 on '||i.tab_owner||'.'||i.table_name||'('||i.column_name||');';
                         EXCEPTION
                        WHEN OTHERS THEN
                                --报错继续执行，标记报错的表
                                call sysdba.err_log(i.tab_owner,i.table_name,i.column_name,substr(dbms_utility.format_error_backtrace, 1, 1000));
                                continue;
                        end;
                        execute immediate 'select datediff(ss, '''||v_start_time||''', sysdate)  from dual;' into v_ss;
                         execute immediate 'update sysdba.gather_table set gather_flag=1,gather_time=sysdate,gather_start_time='''||v_start_time||''', gather_end_time=sysdate,gather_used_time='||v_ss||' where table_name='''||i.table_name||''' and tab_owner='''||i.tab_owner||'''and column_name='''||i.column_name||''';';
                        commit;
                       elsif i.tab_cnt >=10000000 then
                              select count(1) into v_cnt from dba_ind_columns where table_owner=i.tab_owner and table_name=i.table_name and column_name=i.column_name;
                              if v_cnt>0 then
                                begin--收集索引列
                                                 select sysdate into v_start_time from dual;
                                                execute immediate 'stat 100 on '||i.tab_owner||'.'||i.table_name||'('||i.column_name||');';
                                                --报错时继续执行
                                        EXCEPTION
                                        WHEN OTHERS THEN
                                                --标记报错err_flag=1
                                               call sysdba.err_log(i.tab_owner,i.table_name,i.column_name,substr(dbms_utility.format_error_backtrace, 1, 1000));
                                         
                                             --   execute immediate  'update sysdba.gather_table set err_flag=1 where table_name='''||i.table_name||''' and tab_owner='''||i.tab_owner||''' and column_name='''||i.column_name||''';'; 
                                               continue;
                                        END;
                                     execute immediate 'select datediff(ss, '''||v_start_time||''', sysdate)  from dual;' into v_ss;
                                     execute immediate 'update sysdba.gather_table set gather_flag=1,gather_time=sysdate,gather_start_time='''||v_start_time||''', gather_end_time=sysdate,gather_used_time='||v_ss||' where table_name='''||i.table_name||''' and tab_owner='''||i.tab_owner||'''and column_name='''||i.column_name||''';';
                                      commit;
                                  end if;
                                  
                              
                              
                end if;
        end loop;
end;


示例：
call sysdba.p_gather;

最后根据实际使用情况设置定时任务进行收集。

总结

设定脚本收集统计信息，便于运维，把一些机械性的工作用一个存储过程进行实现，也便于更新自定义需求。