1. Check auto stats gather task assigned which group and check if that window-group is exists or not.
SELECT client_name, window_group FROM dba_autotask_client;
2. Created Window Group (ORA$AT_WGRP_OS) as it was not exists
BEGIN
DBMS_SCHEDULER.CREATE_WINDOW_GROUP ('ORA$AT_WGRP_OS');
end;
/
3. Now we need to a window to that window-group. Thus created a new window and added.
BEGIN
dbms_scheduler.create_window(window_name=> 'MONDAY_WINDOW', duration => numtodsinterval(2, 'hour'),resource_plan=> 'DEFAULT_MAINTENANCE_PLAN',repeat_interval => 'FREQ=DAILY;byday=MON;byhour=22;byminute=0; bysecond=0');
end;
/
BEGIN
dbms_scheduler.add_window_group_member(group_name => 'ORA$AT_WGRP_OS', window_list => ''MONDAY_WINDOW');
end;
/
Similarly created all windows and added to a group.
select * from dba_scheduler_wingroup_members;
4. Now enable the auto-task for gather statistics.
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
5. Query to check window timings and duration.
SELECT wgm.window_name, w.repeat_interval
FROM dba_scheduler_wingroup_members wgm
JOIN dba_scheduler_windows w ON wgm.window_name = w.window_name
WHERE wgm.window_group_name = 'ORA$AT_WGRP_OS';
6. Query to check the client history
SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed
FROM dba_autotask_client_history
WHERE client_name like '%stats%';
7. Query to check auto task history
SELECT client_name, job_status, job_start_time, job_duration
FROM dba_autotask_job_history
WHERE client_name like '%stats%'
ORDER BY job_start_time;
8. Query to check the task-name of the auto-task clients
SELECT client_name, task_name, task_target_type
FROM dba_autotask_task;
9. Program Name
SELECT program_name
FROM dba_scheduler_programs
WHERE program_name='GATHER_STATS_PROG';
References Sites:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/scheduse007.htm#i1011367
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tasks004.htm#CIHCDBEC
http://www.oracle-base.com/articles/11g/AutomatedDatabaseMaintenanceTaskManagement_11gR1.php
http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/OAUTO1/Default.aspx
http://www.oracle-base.com/forums/viewtopic.php?f=1&t=11890
References Views:
dba_scheduler_wingroup_members
dba_scheduler_windows
dba_scheduler_window_groups
dba_autotask_client
dba_autotask_client_history
dba_autotask_job_history
DBA_AUTOTASK_CLIENT_JOB
dba_autotask_operation
dba_autotask_task
DBA_AUTOTASK_WINDOW_CLIENTS
DBA_AUTOTASK_WINDOW_HISTORY
DBA_AUTOTASK_SCHEDULE