11G中自动收集统计信息

时间：2022-10-17 23:37:38
在11G中，引入了一个名为 gather_stats_prog 的自动运行任务专用于自动收集统计信息。其对应的客户端名称为"auto optimizer stats collection"：

SQL> select client_name,task_name,operation_name,status from dba_autotask_task;

CLIENT_NAME                              TASK_NAME                      OPERATION_NAME                 STATUS

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

sql tuning advisor                       AUTO_SQL_TUNING_PROG           automatic sql tuning task      ENABLED

auto optimizer stats collection          gather_stats_prog              auto optimizer stats job       ENABLED

auto space advisor                       auto_space_advisor_prog        auto space advisor job         ENABLED

SQL> select program_action from dba_scheduler_programs where program_name='GATHER_STATS_PROG';

PROGRAM_ACTION

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

dbms_stats.gather_database_stats_job_proc

SQL>

11G中，自动统计信息收集作业可配置的维护窗口增加到7个，分别为：

SQL> select window_name,autotask_status from dba_autotask_window_clients;

WINDOW_NAME                    AUTOTASK

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

MONDAY_WINDOW                  ENABLED

TUESDAY_WINDOW                 ENABLED

WEDNESDAY_WINDOW               ENABLED

THURSDAY_WINDOW                ENABLED

FRIDAY_WINDOW                  ENABLED

SATURDAY_WINDOW                ENABLED

SUNDAY_WINDOW                  ENABLED

7 rows selected.

SQL> select window_name,repeat_interval,duration from dba_scheduler_windows where enabled='TRUE';

WINDOW_NAME                    REPEAT_INTERVAL                                         DURATION

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

MONDAY_WINDOW                  freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0   +000 04:00:00

TUESDAY_WINDOW                 freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0   +000 04:00:00

WEDNESDAY_WINDOW               freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0   +000 04:00:00

THURSDAY_WINDOW                freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0   +000 04:00:00

FRIDAY_WINDOW                  freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0   +000 04:00:00

SATURDAY_WINDOW                freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0    +000 20:00:00

SUNDAY_WINDOW                  freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0    +000 20:00:00

7 rows selected.

SQL>

10G中，平时是从晚上10点开始，最多运行8小时；周末是从零点开始，最多可以运行24小时。

从$ORACLE_HOME/rdbms/admin/catmwin.sql中可以看10/11g自动统计信息收集作业的整体流程。

相关文章