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

每个行业都有自己的特点，比如金融行业，每天晚上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              0PL/SQL Release 12.1.0.2.0 - Production                                                    0CORE    12.1.0.2.0      Production                                                        0TNS for Linux: Version 12.1.0.2.0 - Production                                            0NLSRTL Version 12.1.0.2.0 - Production                                                    0SQL> 

--默认值如下set lines 200col WINDOW_NAME for a20col REPEAT_INTERVAL for a60 col DURATION  for a20SELECT 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 200SQL> col WINDOW_NAME for a20SQL> col REPEAT_INTERVAL for a60 SQL> col DURATION  for a20SQL> 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        TRUEWEDNESDAY_WINDOW     freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00        TRUETHURSDAY_WINDOW      freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00        TRUEFRIDAY_WINDOW        FREQ=daily;BYDAY=FRI;BYHOUR=22;BYMINUTE=30;BYSECOND=0        +000 04:00:00        TRUESATURDAY_WINDOW      freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00        TRUESUNDAY_WINDOW        freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00        TRUE6 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 200SQL> col WINDOW_NAME for a20SQL> col REPEAT_INTERVAL for a60 SQL> col DURATION  for a20SQL> 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        TRUETUESDAY_WINDOW       FREQ=daily;BYDAY=TUE;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUEWEDNESDAY_WINDOW     FREQ=daily;BYDAY=WED;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUETHURSDAY_WINDOW      FREQ=daily;BYDAY=THU;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUEFRIDAY_WINDOW        FREQ=daily;BYDAY=FRI;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUESATURDAY_WINDOW      FREQ=daily;BYDAY=SAT;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUESUNDAY_WINDOW        FREQ=daily;BYDAY=SUN;BYHOUR=1;BYMINUTE=0;BYSECOND=0          +000 05:00:00        TRUE7 rows selected.SQL> 

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