签到成功

知道了

CNDBA社区CNDBA社区

Oracle 判断 并 手动收集 统计信息 脚本

2016-11-25 16:02 2415 0 原创 Oracle 性能优化
作者: Dave

 

.  说明

 

在之前的blog

            Oracle Statistic 统计信息 小结

https://www.cndba.cn/dave/article/1558

            http://blog.csdn.net/tianlesoftware/archive/2009/10/15/4668723.aspx

 

            里对统计信息的收集有说明, Oracle Automatic Statistics Gathering 是通过Scheduler 来实现收集和维护的。 Job 名称是GATHER_STATS_JOB, Job收集数据库所有对象的2种统计信息:

            1Missing statistics(统计信息缺失)

            2Stale statistics(统计信息陈旧)

            Job 是在数据库创建的时候自动创建,并由Scheduler来管理。Scheduler maintenance windows open时运行gather job 默认情况下,job 会在每天晚上10到早上6点和周末全天开启。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级,再开始进行统计信息。

 https://www.cndba.cn/dave/article/1558

           Scheduler Job stop_on_window_close 属性控制GATHER_STATS_JOB 是否继续。该属性默认值为True. 如果该值设置为False,那么GATHER_STATS_JOB 会中断, 而没有收集完的对象将在下次启动时继续收集。

 

            Gather_stats_job 调用dbms_stats.gather_database_stats_job_proc过程来收集statistics 的信息。 该过程收集对象statistics的条件如下:

            1)对象的统计信息之前没有收集过。

            2当对象有超过10%rows 被修改,此时对象的统计信息也称为stale statistics

 

Oracle 10g中,在查询表时,如果没有统计分析,那么会采用动态采样。

            Oracle 分析及动态采样

            http://blog.csdn.net/tianlesoftware/archive/2010/08/28/5845028.aspxhttps://www.cndba.cn/dave/article/1558

 https://www.cndba.cn/dave/article/1558

 

            以上说明,都是讲Oracle 自动收集这块,但有时候,自动收集也不太靠谱,因为默认情况下只在晚上10点到早上6点。 如果在其他时间表的更新很频繁,这样数据的信息也不准确。 产生的执行计划可能与实际的也就可能有出入。

 

 

. 手工收集统计信息脚本


通过如下SQL 可以查看统计信息的收集情况:

/* Formatted on 2011/11/24 12:03:16 (QP5 v5.185.11230.41888) */SELECT /*+ UNNEST */      DISTINCT TABLE_NAME, LAST_ANALYZED, STALE_STATS  FROM DBA_TAB_STATISTICS WHERE LAST_ANALYZED IS NULL OR STALE_STATS = 'YES' AND OWNER = 'XEZF'


     一般情况下,当表分析以后,在查看dba_tables 表的num_rows 列时会显示表中记录数,我们可以拿这个数值与count(*) 的结果进行比较,如果2者相差较大,就说明,该表的统计信息陈旧,需要去收集统计信息。

`


https://www.cndba.cn/dave/article/1558

 

2.1 存储过程

CREATE OR REPLACE PROCEDURE proc_manual_gather_stats

AS

    t_count number; 

    t_num_rows number;

    t_tablename varchar2(50);

    t_sql varchar2(200);

   

   CURSOR c1  IS  select * from dba_tables where owner='DAVE';

     

BEGIN

   /**

   过程内容: 判断统计信息是否同步,如不同步,手工收集统计信息

   作者: Tianlesoftware

   时间:2011-5-25

   */

  

   FOR x IN c1

   LOOP

     t_tablename := x.TABLE_NAME;

     t_num_rows := x.num_rows;https://www.cndba.cn/dave/article/1558

    t_sql :='select count(*)  from '||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('DAVE',t_tablename);

     end if;  

   END LOOP;

  

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

   DBMS_OUTPUT.PUT_LINE ('NO_DATA_FOUND');

      RETURN;

   WHEN OTHERS

   THENhttps://www.cndba.cn/dave/article/1558

   DBMS_OUTPUT.PUT_LINE ('OTHERS');

      RETURN;

END;

/

 

 

2.2 使用Scheduler Job 部署

Oracle 10g Scheduler 特性

http://blog.csdn.net/tianlesoftware/archive/2009/10/22/4715218.aspx

 

2.2.1 创建Job

 

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name => 'JOB_MANUAL_GATHER_STATS',

job_type => 'STORED_PROCEDURE',

job_action => 'PROC_MANUAL_GATHER_STATS',  --调用的过程名称

start_date => sysdate,

repeat_interval => ''FREQ=HOURLY;INTERVAL=1');

            -- 每个一小时执行一次

https://www.cndba.cn/dave/article/1558

END;

/

            这个时间要根据自己的业务来判断,因为分析本身就会消耗CPU 资源。 所以尽量避免业务高峰期来执行,最好是在业务低的时候执行。

 

 注意:

            JOB 虽然成功创建了,但却并未执行.因为ENABLED 参数当不显式指定时,该参数的默认值为false

 https://www.cndba.cn/dave/article/1558

2.2.2 启用Job

exec dbms_scheduler.enable('JOB_MANUAL_GATHER_STATS');

 

2.2.3 停止Job

exec dbms_scheduler.disable('JOB_MANUAL_GATHER_STATS');

 

 

 

 

 

-------------------------------------------------------------------------------------------------------

Blog http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群:62697716();   DBA2 群:62697977()   DBA3 群:62697850()  

DBA 超级群:63306533();  DBA4 群: 83829929  DBA5群: 142216823    https://www.cndba.cn/dave/article/1558

DBA6 群:158654907  聊天 群:40132017   聊天2群:69087192

--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
Dave

Dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 1902
    原创
  • 2
    翻译
  • 456
    转载
  • 131
    评论
  • 访问:3641884次
  • 积分:3137
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by ZhiXinTech · 皖ICP备19020168号·

        QQ交流群