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/
微信赞赏
支付宝赞赏
