设计Oracle统计信息自动更新的存储过程【DBA企业实战任务】

       在数据库的运维工作中，经常会遇到一些表的数据量倾斜很严重，例如针对某些特定业务，一些大表需要经常做初始化任务操作，这时候若表的统计信息已过期或不够准确，会引起执行计划的数据有误，所以有时候需要手工去分析这些表的统计信息，但这样做比较耗时间与精力，为了解决这个问题，可以针对这些特定表，或数据倾斜严重的表进行自动化分析，这样可以降低DBA的工作量，并且分析比较及时到位，下面是设计这样的存储过程，用于自动分析某些表或更新其统计信息：

CREATE OR REPLACE PROCEDURE proc_manual_gather_stats
AS
    t_count number;
    t_num_rows number;
    t_owner varchar2(50);
    t_tablename varchar2(50);
    t_sql varchar2(200);
   CURSOR c1 IS select * from dba_tables where owner LIKE 'MBS7%';
   --这里针对MBS7所有的表进行游标搜索
BEGIN
   FOR x IN c1
   LOOP
     t_owner:=x.owner;
     t_tablename := x.TABLE_NAME;
     t_num_rows := x.num_rows;
    t_sql :='select count(1) from '||t_owner||'.'||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(t_owner,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;
/

或用一下方式（判断统计信息是否过期）：

CREATE OR REPLACE PROCEDURE proc_manual_gather_STALE_STATS
AS
    t_owner varchar2(50);
    t_tablename varchar2(50);
 
   CURSOR c1 IS select * from dba_tab_statistics where owner like 'MBS7%' and (stale_stats='YES' or last_analyzed is null);
BEGIN
   /**
   过程内容： 判断统计信息是否同步，如不同步，手工收集统计信息
   */
   FOR x IN c1
   LOOP
     t_owner:=x.owner;
     t_tablename := x.TABLE_NAME;
        dbms_stats.gather_table_stats(t_owner,t_tablename);
   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;
/

最后把上述存储过程放在新建JOB里定时执行即可。