Scripts

Script to get info from the Auto Maintenance Gather Stats job

User Rating:  / 0

Details

Parent Category: Articles

Created on Tuesday, 10 December 2019 13:48

Last Updated on Tuesday, 10 December 2019 14:16

Published on Tuesday, 10 December 2019 13:48

Hits: 4799

Script to get information from the Auto Maintenance Gather Stats Job

set pages 300 lines 300 longchunksize 300 long 30000

alter session set nls_date_format='DD/MM/YYYY HH24:Mi';

spool job_info.txt

col window_name format a30
col resource_plan format a30
col duration format a30
col client_name format a30
col table_name format a30
col owner format a30

alter session set nls_date_format='DD/MM/YYYY HH24:Mi';

select window_start_time,window_duration,job_start_time,job_status,job_duration,job_info from dba_autotask_job_history where client_name like '%stats%' order by window_start_time;

select * from dba_autotask_window_clients;

select * from dba_autotask_status;

select * from dba_autotask_operation;

select WINDOW_NAME,RESOURCE_PLAN,START_DATE,DURATION,ENABLED,ACTIVE from DBA_SCHEDULER_WINDOWS;

select * from dba_scheduler_window_groups;

--select * from dba_rsrc_plans;

--select * from dba_rsrc_plan_directives where plan in ('OPERA_DAILY_OPS','DEFAULT_MAINTENANCE_WINDOW');

--select * from v$rsrc_plan_history;

PROMPT "tables with stale stats"
select owner,table_name from dba_tab_statistics where stale_stats='TRUE';

--select owner,table_name,last_analyzed from dba_tables where trunc(last_analyzed)=trunc(sysdate) order by last_analyzed;

select target,job_name,start_time, end_time - start_time as "Elapsed Time",notes,status from dba_optstat_operation_tasks where status !='COMPLETED' and trunc(start_time) > sysdate -1 order by 3;

COLUMN REPORT FORMAT A300

VARIABLE my_report CLOB;
BEGIN
:my_report := DBMS_STATS.REPORT_STATS_OPERATIONS (
  since => SYSDATE-7
, until => SYSDATE
, detail_level => 'TYPICAL'
, format => 'TEXT'
, auto_only => TRUE
);
END;
/


print my_report;