Automatic Statistics Collection script


prompt
set feed off
prompt Auto tasks (11G)
prompt
accept x_days prompt "Days [1] : " default 1
prompt
select decode( b.instance_number, a.inst_id, '-->', null ) "Current",
 a.instance_name, a.host_name, a.version, a.startup_time,
 a.status, a.parallel, a.thread#, a.archiver, a.log_switch_wait, a.logins,
 a.shutdown_pending, a.database_status, a.instance_role, a.active_state, a.blocked,
 a.instance_number
from gv$instance a,
 v$instance b
where b.instance_number (+)= a.inst_id
order by a.inst_id;
prompt
prompt
prompt
prompt
declare
 cursor csr_main_cursor is select * from DBA_AUTOTASK_TASK order by client_name, task_name;
 ln_counter number := 0;
begin
 dbms_output.put_line( 'Details from SYS.DBA_AUTOTASK_TASK' );
 dbms_output.put_line( 'Displays information about current and past automated maintenance tasks.' );
for i in csr_main_cursor loop
 dbms_output.put_line( rpad( '-', 80, '-' ) );
 if i.client_name is not null then
 dbms_output.put_line( 'Client Name : ' || i.client_name );
 end if;
 if i.task_name is not null then
 dbms_output.put_line( 'Task Name : ' || i.task_name );
 end if;
 if i.task_target_type is not null then
 dbms_output.put_line( 'Task Target Type : ' || i.task_target_type );
 end if;
 if i.task_target_name is not null then
 dbms_output.put_line( 'Task Target Name : ' || i.task_target_name );
 end if;
 if i.operation_name is not null then
 dbms_output.put_line( 'Operation Name : ' || i.operation_name );
 end if;
 if i.attributes is not null then
 dbms_output.put_line( 'Attributes : ' || i.attributes );
 end if;
 if i.task_priority is not null then
 dbms_output.put_line( 'Task Priority : ' || i.task_priority );
 end if;
 if i.priority_override is not null then
 dbms_output.put_line( 'Priority Override : ' || i.priority_override );
 end if;
 if i.status is not null then
 dbms_output.put_line( 'Status : ' || i.status );
 end if;
 if i.deferred_window_name is not null then
 dbms_output.put_line( 'Deferred Window Name : ' || i.deferred_window_name );
 end if;
 if i.current_job_name is not null then
 dbms_output.put_line( 'Current Job Name : ' || i.current_job_name );
 end if;
 if i.job_scheduler_status is not null then
 dbms_output.put_line( 'Job Scheduler Status : ' || i.job_scheduler_status );
 end if;
 if i.estimate_type is not null then
 dbms_output.put_line( 'Estimate Type : ' || i.estimate_type );
 end if;
 if i.estimated_weight is not null then
 dbms_output.put_line( 'Estimated Weight : ' || i.estimated_weight );
 end if;
 if i.estimated_duration is not null then
 dbms_output.put_line( 'Estimated Duration : ' || i.estimated_duration );
 end if;
 if i.estimated_cpu_time is not null then
 dbms_output.put_line( 'Estimated Cpu Time : ' || i.estimated_cpu_time );
 end if;
 if i.estimated_temp is not null then
 dbms_output.put_line( 'Estimated Temp : ' || i.estimated_temp );
 end if;
 if i.estimated_dop is not null then
 dbms_output.put_line( 'Estimated Dop : ' || i.estimated_dop );
 end if;
 if i.estimated_io_rate is not null then
 dbms_output.put_line( 'Estimated Io Rate : ' || i.estimated_io_rate );
 end if;
 if i.estimated_undo_rate is not null then
 dbms_output.put_line( 'Estimated Undo Rate : ' || i.estimated_undo_rate );
 end if;
 if i.retry_count is not null then
 dbms_output.put_line( 'Retry Count : ' || i.retry_count );
 end if;
 if i.last_good_date is not null then
 dbms_output.put_line( 'Last Good Date : ' || i.last_good_date );
 end if;
 if i.last_good_priority is not null then
 dbms_output.put_line( 'Last Good Priority : ' || i.last_good_priority );
 end if;
 if i.last_good_duration is not null then
 dbms_output.put_line( 'Last Good Duration : ' || i.last_good_duration );
 end if;
 if i.last_good_cpu_time is not null then
 dbms_output.put_line( 'Last Good Cpu Time : ' || i.last_good_cpu_time );
 end if;
 if i.last_good_temp is not null then
 dbms_output.put_line( 'Last Good Temp : ' || i.last_good_temp );
 end if;
 if i.last_good_dop is not null then
 dbms_output.put_line( 'Last Good Dop : ' || i.last_good_dop );
 end if;
 if i.last_good_io_rate is not null then
 dbms_output.put_line( 'Last Good Io Rate : ' || i.last_good_io_rate );
 end if;
 if i.last_good_undo_rate is not null then
 dbms_output.put_line( 'Last Good Undo Rate : ' || i.last_good_undo_rate );
 end if;
 if i.last_good_cpu_wait is not null then
 dbms_output.put_line( 'Last Good Cpu Wait : ' || i.last_good_cpu_wait );
 end if;
 if i.last_good_io_wait is not null then
 dbms_output.put_line( 'Last Good Io Wait : ' || i.last_good_io_wait );
 end if;
 if i.last_good_undo_wait is not null then
 dbms_output.put_line( 'Last Good Undo Wait : ' || i.last_good_undo_wait );
 end if;
 if i.last_good_temp_wait is not null then
 dbms_output.put_line( 'Last Good Temp Wait : ' || i.last_good_temp_wait );
 end if;
 if i.last_good_concurrency is not null then
 dbms_output.put_line( 'Last Good Concurrency : ' || i.last_good_concurrency );
 end if;
 if i.last_good_contention is not null then
 dbms_output.put_line( 'Last Good Contention : ' || i.last_good_contention );
 end if;
 if i.next_try_date is not null then
 dbms_output.put_line( 'Next Try Date : ' || i.next_try_date );
 end if;
 if i.last_try_date is not null then
 dbms_output.put_line( 'Last Try Date : ' || i.last_try_date );
 end if;
 if i.last_try_priority is not null then
 dbms_output.put_line( 'Last Try Priority : ' || i.last_try_priority );
 end if;
 if i.last_try_result is not null then
 dbms_output.put_line( 'Last Try Result : ' || i.last_try_result );
 end if;
 if i.last_try_duration is not null then
 dbms_output.put_line( 'Last Try Duration : ' || i.last_try_duration );
 end if;
 if i.last_try_cpu_time is not null then
 dbms_output.put_line( 'Last Try Cpu Time : ' || i.last_try_cpu_time );
 end if;
 if i.last_try_temp is not null then
 dbms_output.put_line( 'Last Try Temp : ' || i.last_try_temp );
 end if;
 if i.last_try_dop is not null then
 dbms_output.put_line( 'Last Try Dop : ' || i.last_try_dop );
 end if;
 if i.last_try_io_rate is not null then
 dbms_output.put_line( 'Last Try Io Rate : ' || i.last_try_io_rate );
 end if;
 if i.last_try_undo_rate is not null then
 dbms_output.put_line( 'Last Try Undo Rate : ' || i.last_try_undo_rate );
 end if;
 if i.last_try_cpu_wait is not null then
 dbms_output.put_line( 'Last Try Cpu Wait : ' || i.last_try_cpu_wait );
 end if;
 if i.last_try_io_wait is not null then
 dbms_output.put_line( 'Last Try Io Wait : ' || i.last_try_io_wait );
 end if;
 if i.last_try_undo_wait is not null then
 dbms_output.put_line( 'Last Try Undo Wait : ' || i.last_try_undo_wait );
 end if;
 if i.last_try_temp_wait is not null then
 dbms_output.put_line( 'Last Try Temp Wait : ' || i.last_try_temp_wait );
 end if;
 if i.last_try_concurrency is not null then
 dbms_output.put_line( 'Last Try Concurrency : ' || i.last_try_concurrency );
 end if;
 if i.last_try_contention is not null then
 dbms_output.put_line( 'Last Try Contention : ' || i.last_try_contention );
 end if;
 if i.mean_good_duration is not null then
 dbms_output.put_line( 'Mean Good Duration : ' || i.mean_good_duration );
 end if;
 if i.mean_good_cpu_time is not null then
 dbms_output.put_line( 'Mean Good Cpu Time : ' || i.mean_good_cpu_time );
 end if;
 if i.mean_good_temp is not null then
 dbms_output.put_line( 'Mean Good Temp : ' || i.mean_good_temp );
 end if;
 if i.mean_good_dop is not null then
 dbms_output.put_line( 'Mean Good Dop : ' || i.mean_good_dop );
 end if;
 if i.mean_good_io is not null then
 dbms_output.put_line( 'Mean Good Io : ' || i.mean_good_io );
 end if;
 if i.mean_good_undo is not null then
 dbms_output.put_line( 'Mean Good Undo : ' || i.mean_good_undo );
 end if;
 if i.mean_good_cpu_wait is not null then
 dbms_output.put_line( 'Mean Good Cpu Wait : ' || i.mean_good_cpu_wait );
 end if;
 if i.mean_good_io_wait is not null then
 dbms_output.put_line( 'Mean Good Io Wait : ' || i.mean_good_io_wait );
 end if;
 if i.mean_good_undo_wait is not null then
 dbms_output.put_line( 'Mean Good Undo Wait : ' || i.mean_good_undo_wait );
 end if;
 if i.mean_good_temp_wait is not null then
 dbms_output.put_line( 'Mean Good Temp Wait : ' || i.mean_good_temp_wait );
 end if;
 if i.mean_good_concurrency is not null then
 dbms_output.put_line( 'Mean Good Concurrency : ' || i.mean_good_concurrency );
 end if;
 if i.mean_good_contention is not null then
 dbms_output.put_line( 'Mean Good Contention : ' || i.mean_good_contention );
 end if;
 if i.info_field_1 is not null then
 dbms_output.put_line( 'Info Field 1 : ' || i.info_field_1 );
 end if;
 if i.info_field_2 is not null then
 dbms_output.put_line( 'Info Field 2 : ' || i.info_field_2 );
 end if;
 if i.info_field_3 is not null then
 dbms_output.put_line( 'Info Field 3 : ' || i.info_field_3 );
 end if;
 if i.info_field_4 is not null then
 dbms_output.put_line( 'Info Field 4 : ' || i.info_field_4 );
 end if;
 dbms_output.put_line( chr(10) );
 ln_counter := ln_counter + 1;
 end loop;
if ln_counter = 0 then
 dbms_output.put_line( chr(10) || 'No records found' || chr(10) );
 else
 dbms_output.put_line( 'Found ' || ln_counter || ' record(s)' );
 end if;
end;
/
prompt
prompt
prompt
prompt
declare
 cursor csr_main_cursor is select * from DBA_AUTOTASK_CLIENT order by client_name;
 ln_counter number := 0;
begin
 dbms_output.put_line( 'Details from SYS.DBA_AUTOTASK_CLIENT' );
 dbms_output.put_line( 'Displays statistical data for each automated maintenance task over 7-day and 30-day periods' );
for i in csr_main_cursor loop
 dbms_output.put_line( rpad( '-', 80, '-' ) );
 if i.client_name is not null then
 dbms_output.put_line( 'Client Name : ' || i.client_name );
 end if;
 if i.status is not null then
 dbms_output.put_line( 'Status : ' || i.status );
 end if;
 if i.consumer_group is not null then
 dbms_output.put_line( 'Consumer Group : ' || i.consumer_group );
 end if;
 if i.client_tag is not null then
 dbms_output.put_line( 'Client Tag : ' || i.client_tag );
 end if;
 if i.priority_override is not null then
 dbms_output.put_line( 'Priority Override : ' || i.priority_override );
 end if;
 if i.attributes is not null then
 dbms_output.put_line( 'Attributes : ' || i.attributes );
 end if;
 if i.window_group is not null then
 dbms_output.put_line( 'Window Group : ' || i.window_group );
 end if;
 if i.service_name is not null then
 dbms_output.put_line( 'Service Name : ' || i.service_name );
 end if;
 if i.resource_percentage is not null then
 dbms_output.put_line( 'Resource Percentage : ' || i.resource_percentage );
 end if;
 if i.use_resource_estimates is not null then
 dbms_output.put_line( 'Use Resource Estimates : ' || i.use_resource_estimates );
 end if;
 if i.mean_job_duration is not null then
 dbms_output.put_line( 'Mean Job Duration : ' || i.mean_job_duration );
 end if;
 if i.mean_job_cpu is not null then
 dbms_output.put_line( 'Mean Job Cpu : ' || i.mean_job_cpu );
 end if;
 if i.mean_job_attempts is not null then
 dbms_output.put_line( 'Mean Job Attempts : ' || i.mean_job_attempts );
 end if;
 if i.mean_incoming_tasks_7_days is not null then
 dbms_output.put_line( 'Mean Incoming Tasks 7 Days : ' || i.mean_incoming_tasks_7_days );
 end if;
 if i.mean_incoming_tasks_30_days is not null then
 dbms_output.put_line( 'Mean Incoming Tasks 30 Days : ' || i.mean_incoming_tasks_30_days );
 end if;
 if i.total_cpu_last_7_days is not null then
 dbms_output.put_line( 'Total Cpu Last 7 Days : ' || i.total_cpu_last_7_days );
 end if;
 if i.total_cpu_last_30_days is not null then
 dbms_output.put_line( 'Total Cpu Last 30 Days : ' || i.total_cpu_last_30_days );
 end if;
 if i.max_duration_last_7_days is not null then
 dbms_output.put_line( 'Max Duration Last 7 Days : ' || i.max_duration_last_7_days );
 end if;
 if i.max_duration_last_30_days is not null then
 dbms_output.put_line( 'Max Duration Last 30 Days : ' || i.max_duration_last_30_days );
 end if;
 if i.window_duration_last_7_days is not null then
 dbms_output.put_line( 'Window Duration Last 7 Days : ' || i.window_duration_last_7_days );
 end if;
 if i.window_duration_last_30_days is not null then
 dbms_output.put_line( 'Window Duration Last 30 Days : ' || i.window_duration_last_30_days );
 end if;
 dbms_output.put_line( chr(10) );
 ln_counter := ln_counter + 1;
 end loop;
if ln_counter = 0 then
 dbms_output.put_line( chr(10) || 'No records found' || chr(10) );
 else
 dbms_output.put_line( 'Found ' || ln_counter || ' record(s)' );
 end if;
end;
/
prompt
prompt
prompt
prompt
declare
 lt_dba_objects dba_objects%rowtype;
 cursor csr_main_cursor is select * from DBA_AUTOTASK_CLIENT_JOB order by client_name;
 ln_counter number := 0;
begin
 dbms_output.put_line( 'Details from SYS.DBA_AUTOTASK_CLIENT_JOB' );
 dbms_output.put_line( 'Displays information about currently running Scheduler jobs created for automated maintenance tasks.' );
 dbms_output.put_line( 'It provides information about some objects targeted by those jobs, as well as some additional statistics' );
 dbms_output.put_line( 'from previous instantiations of the same task. Some of this additional data is taken from generic Scheduler views.' );
 for i in csr_main_cursor loop
 dbms_output.put_line( rpad( '-', 80, '-' ) );
 if i.client_name is not null then
 dbms_output.put_line( 'Client Name : ' || i.client_name );
 end if;
 if i.job_name is not null then
 dbms_output.put_line( 'Job Name : ' || i.job_name );
 end if;
 if i.job_scheduler_status is not null then
 dbms_output.put_line( 'Job Scheduler Status : ' || i.job_scheduler_status );
 end if;
 if i.task_name is not null then
 dbms_output.put_line( 'Task Name : ' || i.task_name );
 end if;
 if i.task_target_type is not null then
 dbms_output.put_line( 'Task Target Type : ' || i.task_target_type );
 end if;
 if i.task_target_name is not null then
 dbms_output.put_line( 'Task Target Name : ' || i.task_target_name );
 end if;
 if i.task_priority is not null then
 dbms_output.put_line( 'Task Priority : ' || i.task_priority );
 end if;
 if i.task_operation is not null then
 dbms_output.put_line( 'Task Operation : ' || i.task_operation );
 end if;
 dbms_output.put_line( chr(10) );
 ln_counter := ln_counter + 1;
 end loop;
if ln_counter = 0 then
 dbms_output.put_line( chr(10) || 'No records found' || chr(10) );
 else
 dbms_output.put_line( 'Found ' || ln_counter || ' record(s)' );
 end if;
end;
/
prompt
prompt
prompt
prompt
declare
 lt_dba_objects dba_objects%rowtype;
 cursor csr_main_cursor is
 select * from DBA_AUTOTASK_OPERATION order by client_name;
 ln_counter number := 0;
begin
 dbms_output.put_line( 'Details from SYS.DBA_AUTOTASK_OPERATION' );
 dbms_output.put_line( 'Displays all automated maintenance task operations for each client.' );
 for i in csr_main_cursor loop
 dbms_output.put_line( rpad( '-', 80, '-' ) );
 if i.client_name is not null then
 dbms_output.put_line( 'Client Name : ' || i.client_name );
 end if;
 if i.operation_name is not null then
 dbms_output.put_line( 'Operation Name : ' || i.operation_name );
 end if;
 if i.operation_tag is not null then
 dbms_output.put_line( 'Operation Tag : ' || i.operation_tag );
 end if;
 if i.priority_override is not null then
 dbms_output.put_line( 'Priority Override : ' || i.priority_override );
 end if;
 if i.attributes is not null then
 dbms_output.put_line( 'Attributes : ' || i.attributes );
 end if;
 if i.use_resource_estimates is not null then
 dbms_output.put_line( 'Use Resource Estimates : ' || i.use_resource_estimates );
 end if;
 if i.status is not null then
 dbms_output.put_line( 'Status : ' || i.status );
 end if;
 dbms_output.put_line( chr(10) );
 ln_counter := ln_counter + 1;
 end loop;
if ln_counter = 0 then
 dbms_output.put_line( 'No records found' );
 else
 dbms_output.put_line( 'Found ' || ln_counter || ' record(s)' );
 end if;
end;
/
prompt
prompt
prompt
prompt
declare
 lt_dba_objects dba_objects%rowtype;
 cursor csr_main_cursor is
 select * from DBA_AUTOTASK_JOB_HISTORY
 where window_start_time > sysdate - '&&x_days'
 order by window_start_time;
 ln_counter number := 0;
begin
 dbms_output.put_line( 'Details from SYS.DBA_AUTOTASK_JOB_HISTORY' );
 dbms_output.put_line( ' ' );
 dbms_output.put_line( 'Dsplays the history of automated maintenance task job runs. Jobs are added to this view after they finish executing' );
 dbms_output.put_line( ' ' );
 for i in csr_main_cursor loop
 dbms_output.put_line( rpad( '-', 80, '-' ) );
 if i.client_name is not null then
 dbms_output.put_line( 'Client Name : ' || i.client_name );
 end if;
 if i.window_name is not null then
 dbms_output.put_line( 'Window Name : ' || i.window_name );
 end if;
 if i.window_start_time is not null then
 dbms_output.put_line( 'Window Start Time : ' || i.window_start_time );
 end if;
 if i.window_duration is not null then
 dbms_output.put_line( 'Window Duration : ' || i.window_duration );
 end if;
 if i.job_name is not null then
 dbms_output.put_line( 'Job Name : ' || i.job_name );
 end if;
 if i.job_status is not null then
 dbms_output.put_line( 'Job Status : ' || i.job_status );
 end if;
 if i.job_start_time is not null then
 dbms_output.put_line( 'Job Start Time : ' || i.job_start_time );
 end if;
 if i.job_duration is not null then
 dbms_output.put_line( 'Job Duration : ' || i.job_duration );
 end if;
 if i.job_error is not null then
 dbms_output.put_line( 'Job Error : ' || i.job_error );
 end if;
 if i.job_info is not null then
 dbms_output.put_line( 'Job Info : ' || i.job_info );
 end if;
 dbms_output.put_line( chr(10) );
 ln_counter := ln_counter + 1;
 end loop;
if ln_counter = 0 then
 dbms_output.put_line( 'No records found' );
 else
 dbms_output.put_line( 'Found ' || ln_counter || ' record(s)' );
 end if;
end;
/
prompt
prompt
prompt
prompt
prompt DBA_AUTOTASK_CLIENT_HISTORY
prompt
prompt Displays per-window history of job execution counts for each automated maintenance task. This information is viewable in the Job History page of Enterprise Manager.
prompt
col "Client Name" for a31
col "Window Name" for a16
col "Jobs Created" for 999999
col "Jobs Started" for 999999
col "Jobs Completed" for 999999
col "Window Start Time" for a27
col "Window Duration" for a27
col "Window End Time" for a27
select client_name "Client Name",
 window_name "Window Name",
 window_start_time "Window Start Time",
 window_duration "Window Duration",
 jobs_created "Jobs Created",
 jobs_started "Jobs Started",
 jobs_completed "Jobs Completed",
 window_end_time "Window End Time"
from DBA_AUTOTASK_CLIENT_HISTORY
where window_start_time > sysdate - '&&x_days'
order by window_start_time;
prompt
prompt
prompt
prompt
prompt Other views:
prompt
prompt DBA_AUTOTASK_WINDOW_HISTORY
prompt Displays historical information for automated maintenance task windows.
prompt select * from DBA_AUTOTASK_WINDOW_HISTORY order by window_start_time
prompt /
prompt
prompt DBA_AUTOTASK_SCHEDULE
prompt Displays the schedule of maintenance windows for the next 32 days for each client
prompt select * from DBA_AUTOTASK_SCHEDULE order by start_time
prompt /
prompt
prompt DBA_AUTOTASK_WINDOW_CLIENTS
prompt Displays the windows that belong to MAINTENANCE_WINDOW_GROUP, along with the Enabled or Disabled status for the window for each maintenance task. Primarily used by Enterprise Manager.
prompt select * from DBA_AUTOTASK_WINDOW_CLIENTS order by window_next_time
prompt /
prompt
set feed on

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: