oracle查询统计任务,【Oracle Database】统计信息自动收集任务

SQL> select client_name,task_name, status from dba_autotask_task where client_name = 'auto optimizer stats collection';

CLIENT_NAME                                                      TASK_NAME                                                        STATUS

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

auto optimizer stats collection                                  gather_stats_prog                                                ENABLED

SQL> col program_action for a80

SQL> col enabled for a20

SQL> select program_action, number_of_arguments, enabled from dba_scheduler_programs where owner = 'SYS' and program_name = 'GATHER_STATS_PROG';

PROGRAM_ACTION                                                                   NUMBER_OF_ARGUMENTS ENABLED

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

dbms_stats.gather_database_stats_job_proc                                                          0 TRUE

--禁用统计信息自动收集任务

SQL> exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

PL/SQL procedure successfully completed.

SQL> select client_name,status from dba_autotask_client where client_name = 'auto optimizer stats collection';

CLIENT_NAME                                                      STATUS

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

auto optimizer stats collection                                  DISABLED

--启用统计信息自动收集任务

SQL> exec dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

PL/SQL procedure successfully completed.

SQL> select client_name,status from dba_autotask_client where client_name = 'auto optimizer stats collection';

CLIENT_NAME                                                      STATUS

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

auto optimizer stats collection                                  ENABLED

--关闭单个时间窗口

SQL> exec dbms_auto_task_admin.disable (client_name => 'auto optimizer stats collection',operation => NULL,window_name => 'MONDAY_WINDOW');

PL/SQL procedure successfully completed.

SQL> col window_next_time for a50

SQL> col window_active for a50

SQL> col optimizer_stats for a20

SQL> select window_name,window_next_time,window_active,optimizer_stats from dba_autotask_window_clients where window_name = 'MONDAY_WINDOW';

WINDOW_NAME                    WINDOW_NEXT_TIME                                   WINDOW_ACTIVE                                      OPTIMIZER_STATS

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

MONDAY_WINDOW                  14-DEC-20 10.00.00.000000 PM PRC                   FALSE                                              DISABLED

--启用单个时间窗口

SQL> exec dbms_auto_task_admin.enable (client_name => 'auto optimizer stats collection',operation => NULL,window_name => 'MONDAY_WINDOW');

PL/SQL procedure successfully completed.

SQL> select window_name,window_next_time,window_active,optimizer_stats from dba_autotask_window_clients where window_name = 'MONDAY_WINDOW';

WINDOW_NAME                    WINDOW_NEXT_TIME                                   WINDOW_ACTIVE                                      OPTIMIZER_STATS

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

MONDAY_WINDOW                  14-DEC-20 10.00.00.000000 PM PRC                   FALSE                                              ENABLED

--关闭所有时间窗口

SQL> exec dbms_auto_task_admin.disable (client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

PL/SQL procedure successfully completed.

SQL> select window_name,window_next_time,window_active,optimizer_stats from dba_autotask_window_clients;

WINDOW_NAME                    WINDOW_NEXT_TIME                                   WINDOW_ACTIVE                                      OPTIMIZER_STATS

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

MONDAY_WINDOW                  14-DEC-20 10.00.00.000000 PM PRC                   FALSE                                              DISABLED

TUESDAY_WINDOW                 15-DEC-20 10.00.00.000000 PM PRC                   FALSE                                              DISABLED

WEDNESDAY_WINDOW               16-DEC-20 10.00.00.000000 PM PRC                   FALSE                                              DISABLED

THURSDAY_WINDOW                10-DEC-20 10.00.00.000000 PM PRC                   FALSE                                              DISABLED

FRIDAY_WINDOW                  11-DEC-20 11.00.00.000000 PM PRC                   FALSE                                              DISABLED

SATURDAY_WINDOW                12-DEC-20 06.00.00.000000 AM PRC                   FALSE                                              DISABLED

SUNDAY_WINDOW                  13-DEC-20 06.00.00.000000 AM PRC                   FALSE                                              DISABLED

--启用所有时间窗口

SQL> exec dbms_auto_task_admin.enable (client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

PL/SQL procedure successfully completed.

SQL> select window_name,window_next_time,window_active,optimizer_stats from dba_autotask_window_clients;

WINDOW_NAME                    WINDOW_NEXT_TIME                                   WINDOW_ACTIVE                                      OPTIMIZER_STATS

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

MONDAY_WINDOW                  14-DEC-20 10.00.00.000000 PM PRC                   FALSE                                              ENABLED

TUESDAY_WINDOW                 15-DEC-20 10.00.00.000000 PM PRC                   FALSE                                              ENABLED

WEDNESDAY_WINDOW               16-DEC-20 10.00.00.000000 PM PRC                   FALSE                                              ENABLED

THURSDAY_WINDOW                10-DEC-20 10.00.00.000000 PM PRC                   FALSE                                              ENABLED

FRIDAY_WINDOW                  11-DEC-20 11.00.00.000000 PM PRC                   FALSE                                              ENABLED

SATURDAY_WINDOW                12-DEC-20 06.00.00.000000 AM PRC                   FALSE                                              ENABLED

SUNDAY_WINDOW                  13-DEC-20 06.00.00.000000 AM PRC                   FALSE                                              ENABLED

--修改时间窗口

SQL> set line 200

SQL> col repeat_interval for a60

SQL> col duration for a50

SQL> col enabled for a20

SQL> 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';

WINDOW_NAME                    REPEAT_INTERVAL                                              DURATION                                           ENABLED

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

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

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=0; 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

SQL> exec dbms_scheduler.disable (NAME => '"SYS"."FRIDAY_WINDOW"', FORCE => TRUE);

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.set_attribute (name => '"SYS"."FRIDAY_WINDOW"',attribute => 'repeat_interval',value=> 'freq=weekly;byday=fri;byhour=23;byminute=0;bysecond=0');

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.set_attribute (name => '"SYS"."FRIDAY_WINDOW"',attribute => 'duration',value=> '0 08:00:00');

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.enable (name => '"SYS"."FRIDAY_WINDOW"');

PL/SQL procedure successfully completed.

SQL> 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';

WINDOW_NAME                    REPEAT_INTERVAL                                              DURATION                                           ENABLED

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

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

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=weekly;byday=fri;byhour=23;byminute=0;bysecond=0        +000 08: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

--统计信息自动收集任务历史执行记录

SQL> select client_name, window_name, jobs_created, jobs_started, jobs_completed from dba_autotask_client_history where client_name like '%stats%';

CLIENT_NAME                              WINDOW_NAME                    JOBS_CREATED JOBS_STARTED JOBS_COMPLETED

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

auto optimizer stats collection          TUESDAY_WINDOW                            1            1              1

auto optimizer stats collection          WEDNESDAY_WINDOW                          1            1              1