温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

行业客户 12c 何时自动收集统计信息?

发布时间:2020-07-27 03:06:52 来源:网络 阅读:1128 作者:roidba 栏目:关系型数据库

每个行业都有自己的特点,比如金融行业,每天晚上10点左右会跑批,而自动收集统计信息默认周一到周五晚上10点,持续4个小时收集统计信息,周六周日每天6点,持续20个小时收集统计信息。显然我们应该设计一个更加合适的时间点来收集统计信息。

这里演示每天凌晨1点,持续5个小时收集统计信息的方法。

1.查看版本
SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL> 
--默认值如下
set lines 200
col WINDOW_NAME for a20
col REPEAT_INTERVAL for a60 
col DURATION  for a20
SELECT w.window_name,
               w.repeat_interval,
               w.duration,
               w.enabled
          FROM dba_autotask_window_clients c, dba_scheduler_windows w
         WHERE c.window_name = w.window_name AND c.optimizer_stats = 'ENABLED';

SQL> set lines 200
SQL> col WINDOW_NAME for a20
SQL> col REPEAT_INTERVAL for a60 
SQL> col DURATION  for a20
SQL> SELECT w.window_name,
  2                 w.repeat_interval,
  3                 w.duration,
  4                 w.enabled
  5            FROM dba_autotask_window_clients c, dba_scheduler_windows w
  6           WHERE c.window_name = w.window_name AND c.optimizer_stats = 'ENABLED';

WINDOW_NAME          REPEAT_INTERVAL                                              DURATION             ENABL
-------------------- ------------------------------------------------------------ -------------------- -----
TUESDAY_WINDOW       freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00        TRUE
WEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00        TRUE
THURSDAY_WINDOW      freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00        TRUE
FRIDAY_WINDOW        FREQ=daily;BYDAY=FRI;BYHOUR=22;BYMINUTE=30;BYSECOND=0        +000 04:00:00        TRUE
SATURDAY_WINDOW      freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00        TRUE
SUNDAY_WINDOW        freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00        TRUE

6 rows selected.
--修改自动收集统计信息每天凌晨一点,持续5个小时。
    --MONDAY
    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
            name        => '"SYS"."MONDAY_WINDOW"',
            attribute   => 'REPEAT_INTERVAL',
            VALUE       => 'FREQ=daily;BYDAY=MON;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
        END;
    /

    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."MONDAY_WINDOW"',
        attribute => 'DURATION',
        value => numtodsinterval(300,'minute'));
     END;  
     /
    --TUESDAY
    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
            name        => '"SYS"."TUESDAY_WINDOW"',
            attribute   => 'REPEAT_INTERVAL',
            VALUE       => 'FREQ=daily;BYDAY=TUE;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
        END;
    /

    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."TUESDAY_WINDOW"',
        attribute => 'DURATION',
        value => numtodsinterval(300,'minute'));
     END;  
     /

     --WEDNESDAY
     BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
            name        => '"SYS"."WEDNESDAY_WINDOW"',
            attribute   => 'REPEAT_INTERVAL',
            VALUE       => 'FREQ=daily;BYDAY=WED;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
        END;
    /

    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."WEDNESDAY_WINDOW"',
        attribute => 'DURATION',
        value => numtodsinterval(300,'minute'));
     END;  
     /

     --THURSDAY
      BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
            name        => '"SYS"."THURSDAY_WINDOW"',
            attribute   => 'REPEAT_INTERVAL',
            VALUE       => 'FREQ=daily;BYDAY=THU;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
        END;
    /

    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."THURSDAY_WINDOW"',
        attribute => 'DURATION',
        value => numtodsinterval(300,'minute'));
     END;  
     /

    --FRIDAY
     BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
            name        => '"SYS"."FRIDAY_WINDOW"',
            attribute   => 'REPEAT_INTERVAL',
            VALUE       => 'FREQ=daily;BYDAY=FRI;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
        END;
    /

    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."FRIDAY_WINDOW"',
        attribute => 'DURATION',
        value => numtodsinterval(300,'minute'));
     END;  
     /
    --SATURDAY
    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
            name        => '"SYS"."SATURDAY_WINDOW"',
            attribute   => 'REPEAT_INTERVAL',
            VALUE       => 'FREQ=daily;BYDAY=SAT;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
        END;
    /

    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."SATURDAY_WINDOW"',
        attribute => 'DURATION',
        value => numtodsinterval(300,'minute'));
     END;  
     /

    --SUNDAY
    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE (
            name        => '"SYS"."SUNDAY_WINDOW"',
            attribute   => 'REPEAT_INTERVAL',
            VALUE       => 'FREQ=daily;BYDAY=SUN;BYHOUR=1;BYMINUTE=0;BYSECOND=0');
        END;
    /

    BEGIN
        DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => '"SYS"."SUNDAY_WINDOW"',
        attribute => 'DURATION',
        value => numtodsinterval(300,'minute'));
     END;  
     /
SQL> --验证是否变更
SQL> set lines 200
SQL> col WINDOW_NAME for a20
SQL> col REPEAT_INTERVAL for a60 
SQL> col DURATION  for a20
SQL> SELECT w.window_name,
  2                 w.repeat_interval,
  3                 w.duration,
  4                 w.enabled
  5            FROM dba_autotask_window_clients c, dba_scheduler_windows w
  6           WHERE c.window_name = w.window_name AND c.optimizer_stats = 'ENABLED';

WINDOW_NAME          REPEAT_INTERVAL                                              DURATION             ENABL
-------------------- ------------------------------------------------------------ -------------------- -----
MONDAY_WINDOW        FREQ=daily;BYDAY=MON;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUE
TUESDAY_WINDOW       FREQ=daily;BYDAY=TUE;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUE
WEDNESDAY_WINDOW     FREQ=daily;BYDAY=WED;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUE
THURSDAY_WINDOW      FREQ=daily;BYDAY=THU;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUE
FRIDAY_WINDOW        FREQ=daily;BYDAY=FRI;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUE
SATURDAY_WINDOW      FREQ=daily;BYDAY=SAT;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUE
SUNDAY_WINDOW        FREQ=daily;BYDAY=SUN;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUE

7 rows selected.

SQL> 

看到太多的客户都是默认值,当然对非7*24小时的系统也没有太大影响。但是,某些7X24小时的业务系统,我们需要做更多的优化调整,然后持续跟踪,不断优化。保证业务的连续性。不管大家有没有设置,都需要大家知道这里可能会引起性能问题的一个点,我们必须考虑到。

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI