11G中自动收集统计信息

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

在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自动统计信息收集作业的整体流程。