设计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里定时执行即可。

©版权声明:本文为天凯DBS的原创文章,转载请附上原文出处链接及本声明,否则将追究法律责任。
原文链接:https://dbs-service.cn/index.php/a/204.html