DBA_AUTOTASK in Oracle Database

You can query the DBA_AUTOTASK_OPERATION to find out the names and status of the automatic tasks in your database as shown in the following example:


SELECT client_name, status FROM dba_autotask_operation;

CLIENT_NAME                                                      STATUS  
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED 
auto space advisor                                               ENABLED 
sql tuning advisor                                               ENABLED 

3 rows selected.

You can query dba_autotask_job_history.


SELECT client_name, job_status, job_start_time, job_duration
FROM dba_autotask_job_history
WHERE client_name like '%stats%'
ORDER BY client_name;

CLIENT_NAME	                JOB_STATUS	JOB_START_TIME	JOB_DURATION

auto optimizer stats collection	SUCCEEDED	18-NOV-12 02.05.59.651555 PM CST6CDT	+00 00:00:07.000000
auto optimizer stats collection	SUCCEEDED	18-NOV-12 06.06.13.945643 PM CST6CDT	+00 00:00:09.000000
auto optimizer stats collection	SUCCEEDED	18-NOV-12 10.06.33.809775 PM CST6CDT	+00 00:00:16.000000
auto optimizer stats collection	SUCCEEDED	18-NOV-12 06.00.02.447574 AM CST6CDT	+00 00:02:39.000000
auto optimizer stats collection	SUCCEEDED	18-NOV-12 10.05.39.557510 AM CST6CDT	+00 00:02:01.000000
auto optimizer stats collection	SUCCEEDED	19-NOV-12 10.00.01.225705 PM CST6CDT	+00 00:10:04.000000
auto optimizer stats collection	SUCCEEDED	23-OCT-12 10.00.01.358181 PM CST6CDT	+00 00:03:14.000000
auto optimizer stats collection	SUCCEEDED	21-NOV-12 10.00.02.435126 PM CST6CDT	+00 00:15:17.000000
auto optimizer stats collection	SUCCEEDED	31-OCT-12 10.00.07.182041 PM CST6CDT	+00 00:18:05.000000


You can checkout job windows using this query.


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


WINDOW_NAME	 REPEAT_INTERVAL

MONDAY_WINDOW	 freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0
TUESDAY_WINDOW	 freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0
THURSDAY_WINDOW	 freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0
FRIDAY_WINDOW	 freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0
SATURDAY_WINDOW	 freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0
SUNDAY_WINDOW	 freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0

Thanks
A Rahim Khan

Advertisements
    • Md. Tanweer
    • June 15th, 2015

    Thanks for your Article.

    Brother What is the benefits of it, It consume the CPU resources, at this condition what you suggest. e.g:

    insert into wri$_adv_objspace_trend_data select timepoint,
    space_usage, space_alloc, quality from
    table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,
    NULL, ‘FALSE’, :5, ‘FALSE’))

    When it execute it take 58 minutes or more than it

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: