DBA_AUTOTASK

Check the current status of the autotask job。

Dba_Autotask_Client：（task 名稱，執行視窗，資源組，狀態）

select client_name,client_tag,status,window_group,consumer_group from Dba_Autotask_Client

select job_class_name,resource_consumer_group,service from dba_scheduler_job_classes;

PLAN and CONSUMER_GROUPS:

1,select * from dba_rsrc_plans where plan='DEFAULT_MAINTENANCE_PLAN';

2,Select  plan,group_or_subplan,type from dba_rsrc_plan_directives where plan not like 'DSS%' and  plan not  like 'ETL%' and plan not like 'MIXED%' order by plan;

3,select * from  DBA_RSRC_CONSUMER_GROUPS

Maintenance Window Groups:

檢視資料庫的視窗組和視窗資訊：

select * from dba_scheduler_window_groups;

select * from dba_scheduler_wingroup_members；

select a.window_name,a.resource_plan,a.repeat_interval from dba_scheduler_windows a;

 

（ORA$AT_WGRP_OS，ORA$AT_WGRP_SA，ORA$AT_WGRP_SQ的彙總表。）

select * from DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW

select window_name, REPEAT_INTERVAL, DURATION,resource_plan from DBA_SCHEDULER_WINDOWS;---檢視資料庫定義的windown :定義的job的開始時間和資源使用情況

 

The duration times for windows of Monday thru Friday are from 10:00PM till 02:00AM and those for windows of Saturday thru Sunday are from 06:00AM  till 02:00AM.

WINDOW_GROUPS

select *  from DBA_SCHEDULER_WINDOW_GROUPS;

預設4個時間視窗組：3個AUTOTASK使用，1個MAINTENANCE_WINDOW_GROUP。

WINGROUP_MEMBERS

AUTOTASKE_WINDOW_GROUP：

（ORA$AT_WGRP_OS，ORA$AT_WGRP_SA, ORA$AT_WGRP_SQ）

select * from DBA_SCHEDULER_WINGROUP_MEMBERS  where window_group_name = 'ORA$AT_WGRP_OS';

MAINTENANCE_WINDOW_GROUP：

select OWNER,JOB_NAME,STATE,SCHEDULE_NAME from dba_scheduler_jobs –（這裡面有些job用到MAINTENANCE_WINDOW_GROUP視窗）

select OWNER,JOB_NAME,STATE,SCHEDULE_NAME from dba_scheduler_job（這裡面有些job用到MAINTENANCE_WINDOW_GROUP視窗）

（SCHEDULE_NAME：Name of the schedule that the job uses，can be a window or a window group。Job的開始時間--Scheduled Date）

 

 

檢視是資料庫自動維護作業否正在執行：

select * from DBA_AUTOTASK_WINDOW_CLIENTS; ---視窗開啟，是否到了時間？

----只有在執行的時候該檢視才有內容的（DBA_AUTOTASK_CLIENT_JOB）：

----  displays information about currently running Scheduler jobs created for automated maintenance tasks

select job_name,client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB;--job是否已經執行完成？

手動執行計劃：

EXEC DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

關閉正在執行的任務：

--

BEGIN

DBMS_SCHEDULER.stop_JOB

(job_name  => ' ORA$AT_SQ_SQL_SW_26',force=>true);

END;

/

檢視歷史執行情況：

select * from DBA_AUTOTASK_JOB_HISTORY;

select * from dba_scheduler_job_run_details;（包含所有的scheduler）

 

關閉AUTOTASK_JOB：

select client_name,status from Dba_Autotask_Client

select * from DBA_AUTOTASK_WINDOW_CLIENTS

 BEGIN

   DBMS_AUTO_TASK_ADMIN.DISABLE(

                  client_name => 'auto optimizer stats collection',

                   operation => NULL, window_name => NULL);

  END;

  /

 

 

修改執行策略：

SHAPE  \* MERGEFORMAT

BEGIN
dbms_auto_task_admin.disable(client_name =>'auto optimizer stats collection', operation =>NULL, window_name =>'THURSDAY_WINDOW');
dbms_auto_task_admin.disable(client_name =>'auto optimizer stats collection', operation =>NULL, window_name =>'SATURDAY_WINDOW');
END;

 

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

 

 

How To Manually Execute the Optimizer Statistics Auto Task in Oracle11g (文件 ID 731935.1)

 

In 11g the Auto-Task infrastructure replaced the need for the gather_stats_job and you can execute the following command to accomplish manual statistics collection:

SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS

Note: The user performing this command must have DBA privilege

 

This prompts the Automated Maintenance Tasks subsystem into starting a job that will gather optimizer statistics, unless such a job is already running. The latter occurs if a maintenance window is currently open. If the job is spawned it will be named ORA$_AT_OS_MANUAL_nnnnnn (nnnnn is one or more decimal digits). Unlike regular Automated Maintenance jobs, the "MANUAL" job is not tied to a specific maintenance window.


To monitor the progress of the execution of the task use the following command:

SQL> select job_name,state from dba_scheduler_jobs where program_name='GATHER_STATS_PROG';

To interrupt the task use the following commands:

SQL> variable jobid varchar2(32)
SQL> exec select job_name into :jobid from dba_scheduler_jobs where program_name='GATHER_STATS_PROG';
SQL> print :jobid
SQL> exec dbms_scheduler.stop_job(:jobid,false)