Auto Task in Oracle 11g  

Posted by Mawahid

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



This entry was posted on Thursday, December 30, 2010 at 2:55 AM . You can follow any responses to this entry through the comments feed .

0 comments

Post a Comment