Auto Optimizer Stats Collection in 12c

Environment – Oracle 12c EE 12.1.0.2.0, 64bit on RHEL 7.3
The task auto optimizer stats collection is created by catmwin.sql (run by catproc.sql during database creation). catmwin.sql creates 2 other Automatic Maintenance Clients – auto space advisor & sql tuning advisor.
It creates a MAINTENANCE_WINDOW_GROUP with 7 Maintenance Windows – 5 windows named from MONDAY_WINDOW to FRIDAY_WINDOW starting at 10PM with 4 hour duration, and 2 windows named SATURDAY_WINDOW & SUNDAY_WINDOW starting at 06AM with 20 hour duration. It creates a program named gather_stats_prog which runs dbms_stats.gather_database_stats_job_proc.
In 10g the stats collection was created as a separate job and appeared in DBA_SCHEDULER_JOBS as GATHER_STATS_JOB. From 11g it appears in DBA_AUTOTASK_WINDOW_CLIENTS and only appear in DBA_SCHEDULER_JOBS with system generated names (like ORA$AT_OS_OPT_SY_nnn) when they are executed.
To check on all auto task clients and their status query *_AUTOTASK_CLIENT

col con_id head "Con|tai|ner" form 999
col client_name form a33
col status form a8
col service_name form a20
col window_group form a15
col attributes form a55
col last_change form a15

select	con_id, client_name, status, service_name, window_group, 
	attributes, to_char(last_change, 'DD-MON-YY HH24:MI') last_change
from   	cdb_autotask_client
order	by 1, 2
/

Don’t query *_AUTOTASK_TASK to check the status. A Task can be used by different/multiple clients. So even though we disable the client, the CDB_AUTOTASK_TASK may still show the status as enabled.
I did not find any information on the relationship between *_AUTOTASK_CLIENT and *_AUTOTASK_OPERATION, but looking at the view definitions, i think there is one to many relationship from CLIENT’s to OPERATION’s. Right now the client “auto optimizer stats collection” has only one operation “auto optimizer stats job”.

To disable all automatic maintenance tasks you can execute

execute DBMS_AUTO_TASK_ADMIN.DISABLE;

To disable just the auto optimizer stats collection, you can execute

 exec dbms_auto_task_admin.disable(
       client_name => 'auto optimizer stats collection',
       operation => NULL, window_name => NULL);

To get a summary of client job executions in each maintenance window, query *_AUTOTASK_CLIENT_HISTORY

col con_id head "Con|tai|ner" form 999
col window_name head "Window" form a16
col wst head "Window|Start|Time" form a12
col window_duration head "Window|Duration|Hours" form 999999
col jobs_created head "Jobs|Created" form 999
col jobs_started head "Jobs|Started" form 999
col jobs_completed head "Jobs|Completed" form 999
col wet head "Window|End|Time" form a12

select 	con_id, window_name, to_char(window_start_time, 'DD-MON HH24:MI') wst,
	extract(hour from window_duration) + round(extract(minute from window_duration)/60) window_duration,
	jobs_created, jobs_started, jobs_completed, 
	to_char(window_end_time, 'DD-MON HH24:MI') wet
from 	cdb_autotask_client_history
where 	client_name = 'auto optimizer stats collection'
order	by window_start_time, con_id
/

 Con                  Window         Window                           Window
 tai                  Start        Duration    Jobs    Jobs      Jobs End
 ner Window           Time            Hours Created Started Completed Time
---- ---------------- ------------ -------- ------- ------- --------- ------------
   3 SUNDAY_WINDOW    15-OCT 03:00       20       1       0         0 15-OCT 23:00
   1 SUNDAY_WINDOW    15-OCT 06:00       20       6       6         6 16-OCT 02:00
   1 MONDAY_WINDOW    16-OCT 22:00        4       2       2         2 17-OCT 02:00

As seen above, the client “auto optimizer stats collection” can run multiple times per window (if the prior job completes and the window is open).
As per my observation, the second job is scheduled 1 hour after the first and the rest every 4 hours.
To get the details of each of these jobs like job name, status etc query *_AUTOTASK_JOB_HISTORY

col con_id head "Con|tai|ner" form 999
col window_name head "window" form a16
col wst head "window|start|time" form a12
col window_duration head "window|dura|tion|hours" form 999999
col job_name head "job name" form a22
col jst head "job|start|time" form a12
col job_duration head "job|dura|tion|mins" form 999999
col job_status head "job|status" form a10
col job_error head "job error" form 99
col job_info head "job info" form a40

select 	con_id, window_name, to_char(window_start_time, 'DD-MON HH24:MI') wst,
	extract(hour from window_duration) + round(extract(minute from window_duration)/60) window_duration,
	job_name, to_char(job_start_time, 'DD-MON HH24:MI') jst, job_status,
	extract(hour from job_duration)*60 + round(extract(minute from job_duration)) job_duration,
	job_error, job_info
from 	cdb_autotask_job_history
where 	client_name = 'auto optimizer stats collection'
order	by job_start_time, con_id
/

                                    window                                                    job
 Con                  window          dura                        job                        dura
 tai                  start           tion                        start        job           tion
 ner window           time           hours job name               time         status        mins job error job info
---- ---------------- ------------ ------- ---------------------- ------------ ---------- ------- --------- ----------------------------------------
   1 SUNDAY_WINDOW    15-OCT 06:00      20 ORA$AT_OS_OPT_SY_1543  15-OCT 06:00 SUCCEEDED        5         0
   1 SUNDAY_WINDOW    15-OCT 06:00      20 ORA$AT_OS_OPT_SY_1555  15-OCT 07:07 SUCCEEDED        2         0
   1 SUNDAY_WINDOW    15-OCT 06:00      20 ORA$AT_OS_OPT_SY_1546  15-OCT 11:14 SUCCEEDED        2         0
   1 SUNDAY_WINDOW    15-OCT 06:00      20 ORA$AT_OS_OPT_SY_1557  15-OCT 15:18 SUCCEEDED        4         0
   1 SUNDAY_WINDOW    15-OCT 06:00      20 ORA$AT_OS_OPT_SY_1548  15-OCT 19:25 SUCCEEDED        3         0
   1 SUNDAY_WINDOW    15-OCT 06:00      20 ORA$AT_OS_OPT_SY_1559  15-OCT 23:29 SUCCEEDED        4         0
   1 MONDAY_WINDOW    16-OCT 22:00       4 ORA$AT_OS_OPT_SY_1561  16-OCT 22:00 SUCCEEDED        9         0
   1 MONDAY_WINDOW    16-OCT 22:00       4 ORA$AT_OS_OPT_SY_1564  16-OCT 23:03 SUCCEEDED        3         0

The statistics operations performed by each of these jobs are recorded in *_OPTSTAT_OPERATIONS & *_OPTSTAT_OPERATION_TASKS

                                                                                      
col con_id head "Con|tai|ner" form 999
col id head "Opera|tion|ID" form 9999999
col operation head "Operation" form a30
col job_name head "job name" form a22
col target head "Target" form a10
col jst head "Operation|start|time" form a12
col duration head "Operation|dura|tion|mins" form 999999
col status head "Operation|status" form a10

select 	con_id, id, operation, job_name, target, to_char(start_time, 'DD-MON HH24:MI') jst,
	extract(hour from (end_time - start_time))*60 + extract(minute from (end_time - start_time)) duration,
	status
from  	cdb_optstat_operations
where	operation = 'gather_database_stats (auto)'
order 	by  start_time, con_id
/

                                                                                            Operation
 Con    Opera                                                                  Operation         dura
 tai     tion                                                                  start             tion Operation
 ner       ID Operation                      job name               Target     time              mins status
---- -------- ------------------------------ ---------------------- ---------- ------------ --------- ----------
   1     2042 gather_database_stats (auto)   ORA$AT_OS_OPT_SY_1602  AUTO       23-OCT 23:04         1 COMPLETED
   1     2047 gather_database_stats (auto)   ORA$AT_OS_OPT_SY_1612  AUTO       24-OCT 22:00         4 COMPLETED
   1     2065 gather_database_stats (auto)   ORA$AT_OS_OPT_SY_1604  AUTO       24-OCT 23:10         1 COMPLETED
   3    35049 gather_database_stats (auto)   ORA$AT_OS_OPT_SY_1032  AUTO       25-OCT 03:00       239 TIMED OUT

Details of the parameters used during the operation can be obtained from the "notes" column (not shown above)

Details of stats gathering on individual objects can be viewed in *_OPTSTAT_OPERATION_TASKS (with opid of the task from *_OPTSTAT_OPERATIONS).
For ex – to see all object stats gathered by operation 35049, use the below query

col con_id head "Con|tai|ner" form 999
col jst head "Operation|start|time" form a12
col target head "Target" form a60
col target_type head "Target Type" form a15
col status head "Operation|status" form a10
col duration head "Dura|tion|mins" form 999

select	con_id, to_char(start_time, 'DD-MON HH24:mi') jst,
	target, target_type, status, 
        extract(hour from (end_time - start_time))*60 + extract(minute from (end_time - start_time)) duration
from	cdb_optstat_operation_tasks
where	opid=35049
order	by start_time, con_id
/

 Con Operation                                                                                            Dura
 tai start                                                                                     Operation  tion
 ner time         Target                                                       Target Type     status     mins
---- ------------ ------------------------------------------------------------ --------------- ---------- ----
   3 25-OCT 03:00 USER1.TABLE12345678                                          TABLE           COMPLETED     0
   3 25-OCT 03:00 USER1.TABLE_123                                              TABLE           COMPLETED     0
   3 25-OCT 03:00 SYS.OPATCH_XML_INV                                           TABLE           FAILED        0
   3 25-OCT 03:00 SYS.MON_MODS$                                                TABLE           COMPLETED     0
   3 25-OCT 03:00 SYS.I_MON_MODS$_OBJ                                          INDEX           COMPLETED     0
   3 25-OCT 03:00 SYS.WRI$_HEATMAP_TOP_TABLESPACES                             TABLE           COMPLETED     0
   3 25-OCT 03:00 SYS.SEQ$                                                     TABLE           COMPLETED     0

Details of the column stats and extended stats gathered by each task and the errors encountered can be seen in the "notes" column (not shown above)
转载：https://srivenukadiyala.wordpress.com/2017/10/25/auto-optimizer-stats-collection-in-12c/