In 11g, there is no GATHER_STATS_JOB. It has been now incorporated into DBMS_AUTO_TASK_ADMIN job along with Automatic segment advisor and Automatic tuning advisor. All these 3 run in all
maintenance windows. New maintenance windows introduced with 11g are
You can check the name and state of this job using following query
SQL> SELECT client_name, status FROM dba_autotask_operation;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
Window - Description
MONDAY_WINDOW - Starts at 10 p.m. on Monday and ends at 2 a.m.
TUESDAY_WINDOW - Starts at 10 p.m. on Tuesday and ends at 2 a.m.
WEDNESDAY_WINDOW -Starts at 10 p.m. on Wednesday and ends at 2 a.m.
THURSDAY_WINDOW - Starts at 10 p.m. on Thursday and ends at 2 a.m.
FRIDAY_WINDOW -Starts at 10 p.m. on Friday and ends at 2 a.m.
SATURDAY_WINDOW - Starts at 6 a.m. on Saturday and is 20 hours long.
SUNDAY_WINDOW -Starts at 6 a.m. on Sunday and is 20 hours long.
To enable the job (in case it is disabled)
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
You can check history/run duration of these jobs by using following query
select client_name,JOB_NAME,JOB_STATUS,JOB_START_TIME,JOB_DURATION from DBA_AUTOTASK_JOB_HISTORY where JOB_START_TIME >systimestamp -7 and client_name='sql tuning advisor'
CLIENT_NAME JOB_NAME JOB_STATUS JOB_START_TIME JOB_DURATION
------------------------------ ------------------------------ --------------- -------------------------------------------------- --------------------
sql tuning advisor ORA$AT_SQ_SQL_SW_521 SUCCEEDED 30-MAR-12 04.00.01.698038 PM AMERICA/LOS_ANGELES +000 00:22:13
sql tuning advisor ORA$AT_SQ_SQL_SW_493 SUCCEEDED 01-APR-12 04.00.02.701398 PM AMERICA/LOS_ANGELES +000 00:42:26
sql tuning advisor ORA$AT_SQ_SQL_SW_522 SUCCEEDED 31-MAR-12 04.00.07.642613 PM AMERICA/LOS_ANGELES +000 00:00:37
select * from dba_autotask_client
where client_name =
"auto optimizer stats collection"
"auto optimizer stats collection"
