Oracle 23c New Features for DBA'sAtleast I found hard checking in many other areas,hence writing this
You may not find the no rows for gather_stats_job in 11g due to the enhancments in maintainence groups.
The collection of statistics along with some other maintainence tasks have been incorporated in to dba auto task admin client procedure
First you need to see whether they auto task admin in database or not.
SQL> SELECT client_name, status, mean_job_duration FROM dba_autotask_client; 2 CLIENT_NAME STATUS MEAN_JOB_DURATION ---------------------------------------------------------------- -------- -------------------------------- auto optimizer stats collection ENABLED +000000000 00:03:25.541666667 auto space advisor ENABLED +000000000 00:02:28.150000000 sql tuning advisor ENABLED +000000000 00:45:18.733333333 set linesize 121 col client_name format a35 col consumer_group format a25 col service_name format a15 col window_group format a20 SQL> SELECT client_name, status, consumer_group, window_group FROM dba_autotask_client; CLIENT_NAME STATUS CONSUMER_GROUP WINDOW_GROUP ----------------------------------- -------- ------------------------- -------------------- auto optimizer stats collection ENABLED ORA$AUTOTASK_STATS_GROUP ORA$AT_WGRP_OS auto space advisor ENABLED ORA$AUTOTASK_SPACE_GROUP ORA$AT_WGRP_SA sql tuning advisor ENABLED ORA$AUTOTASK_SQL_GROUP ORA$AT_WGRP_SQ
Note:- If not run, $ORACLE_HOME/rdbms/admin/dbmsatsk.sql
Drill down to Scheduler window groups
SQL> SELECT * FROM DBA_SCHEDULER_WINDOW_GROUPS; WINDOW_GROUP_NAME ENABL NUMBER_OF_WINDOWS NEXT_START_DATE ------------------------------ ----- ----------------- ------------------------------------- MAINTENANCE_WINDOW_GROUP TRUE 7 07-MAR-13 22.00.00.000000 GB-EIRE ORA$AT_WGRP_OS TRUE 7 07-MAR-13 22.00.00.000000 GB-EIRE ORA$AT_WGRP_SA TRUE 7 07-MAR-13 22.00.00.000000 GB-EIRE ORA$AT_WGRP_SQ TRUE 7 07-MAR-13 22.00.00.000000 GB-EIRE
Further drill down to scheduler windows
SQL> select WINDOW_NAME,RESOURCE_PLAN, REPEAT_INTERVAL,DURATION,ENABLED,ACTIVE from dba_scheduler_windows;
WINDOW_NAME REPEAT_INTERVAL DURATION ENABL ACTIV
---------------------- ---------------------------------------- ---------------------- -----
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute= +000 04:00:00 TRUE FALSE
0; bysecond=0
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute= +000 04:00:00 TRUE FALSE
0; bysecond=0
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute= +000 04:00:00 TRUE FALSE
0; bysecond=0
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute= +000 04:00:00 TRUE FALSE
0; bysecond=0
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute= +000 04:00:00 TRUE FALSE
0; bysecond=0
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0 +000 20:00:00 TRUE FALSE
; bysecond=0
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=6;byminute=0 +000 20:00:00 TRUE FALSE
; bysecond=0
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byh +000 08:00:00 FALSE FALSE
our=22;byminute=0; bysecond=0
WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0 +002 00:00:00 FALSE FALSE
;bysecond=0
Now check the scheduler run details
SQL> select job_name,status,run_duration,log_date from dba_scheduler_job_run_details where job_name like '%ORA%' and rownum Note: When the scheduler window started it will create a job name like above OS stands for Optimizer Statistics SQ stands for SQL Tuning Advisory SA stands for Segment Advisory JOB_NAME STATUS RUN_DURATION LOG_DATE ----------------------------------------------------- ----------------------------------- ORA$AT_SA_SPC_SY_842 SUCCEEDED +000 00:03:27 06-FEB-13 22.03.29.006418 +00:00 ORA$AT_SA_SPC_SY_7 SUCCEEDED +000 00:00:26 06-JAN-13 14.09.45.682518 +00:00 ORA$AT_SA_SPC_SY_22 SUCCEEDED +000 00:00:26 06-JAN-13 22.10.04.505120 +00:00 ORA$AT_OS_OPT_SY_821 SUCCEEDED +000 00:03:39 05-FEB-13 22.03.41.418487 +00:00 ORA$AT_SQ_SQL_SW_3 SUCCEEDED +000 00:50:03 06-JAN-13 06.50.05.276734 +00:00 ORA$AT_OS_OPT_SY_21 SUCCEEDED +000 00:00:35 06-JAN-13 22.10.13.355621 +00:00 ORA$AT_OS_OPT_SY_4 SUCCEEDED +000 00:00:37 06-JAN-13 10.09.45.396471 +00:00 ORA$AT_OS_OPT_SY_8 SUCCEEDED +000 00:00:33 06-JAN-13 18.10.01.547017 +00:00 ORA$AT_SQ_SQL_SW_843 SUCCEEDED +000 00:56:18 06-FEB-13 22.56.20.397311 +00:00 9 rows selected. SQL>
Another way to find scheduler run of your statistics
SQL> select operation||decode(target,null,null,'-'||target) operation ,to_char(start_time,'YYMMDD HH24:MI:SS.FF4') start_time ,to_char( end_time,'YYMMDD HH24:MI:SS.FF4') end_time from dba_optstat_operations where operation='gather_database_stats(auto)' order by start_time desc / operation(on) START_TIME END_TIME ---------------------------------------- ------------------------- ------------------------- gather_database_stats(auto) 130210 18:00:38.0680 130210 18:00:50.3929 gather_database_stats(auto) 130210 14:00:30.2518 130210 14:00:50.9174 gather_database_stats(auto) 130210 10:00:22.7646 130210 10:00:48.2924 gather_database_stats(auto) 130210 06:00:02.7987 130210 06:01:55.7652 gather_database_stats(auto) 130209 06:00:02.7911 130209 06:01:53.6289 gather_database_stats(auto) 130208 22:00:02.5849 130208 22:10:12.5439 gather_database_stats(auto) 130207 22:00:03.0104 130207 22:08:29.8338 gather_database_stats(auto) 130206 22:00:02.9352 130206 22:03:22.9765 gather_database_stats(auto) 130205 22:00:03.2965 130205 22:03:41.3111 gather_database_stats(auto) 130204 22:00:02.4189 130204 22:20:01.9220
-Thanks
Geek DBA
Follow Me!!!