To list execution history:
set lines 999
set pages 999
col WINDOW_NAME for a16
col JOB_START_TIME for a40
col JOB_DURATION for a15
col JOB_STATUS for a9
col JOB_INFO for a70
SELECT WINDOW_NAME, JOB_START_TIME,JOB_DURATION,JOB_STATUS,JOB_INFO
FROM DBA_AUTOTASK_JOB_HISTORY
WHERE client_name ='auto optimizer stats collection'
--AND JOB_STATUS <> 'SUCCEEDED'
--AND JOB_START_TIME >= sysdate-2
ORDER BY window_start_time
/
--check if the maintenance window schedules are properly set
set lines 999
col START_TIME for a40
select * from DBA_AUTOTASK_SCHEDULE order by START_TIME;
--How to enable auto stats collection?
EXEC DBMS_AUTO_TASK_ADMIN.ENABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL) ;
exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection', operation => 'auto optimizer stats job', window_name => NULL);
---To monitor the progress of the execution of the task use the following command:
select job_name,state from dba_scheduler_jobs where program_name='GATHER_STATS_PROG';
select OWNER,PROGRAM_NAME,ENABLED from DBA_SCHEDULER_PROGRAMS where program_name='GATHER_STATS_PROG';
--This sql will give an overall count of the number of application tables with stale statistics greater than 10%
SELECT COUNT(*)
FROM DBA_TABLES DT,
DBA_TAB_MODIFICATIONS DTM
WHERE DT.OWNER = DTM.TABLE_OWNER
AND DT.TABLE_NAME = DTM.TABLE_NAME
AND NUM_ROWS > 0
AND ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) >= 10
AND OWNER NOT LIKE 'APEX%'
AND owner NOT LIKE 'SYS%'
AND owner != 'DBSNMP';
--This sql will show the tables with stale statistics per schema. It will also show the last analyzed date for these tables and the percentage change for the tables.
set lines 999
set pages 9999
SELECT dt.owner "Owner",
dt.table_name "Table",
dt.last_analyzed "Last Analyzed",
ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) "% Change"
FROM DBA_TABLES DT,
DBA_TAB_MODIFICATIONS DTM
WHERE DT.OWNER = DTM.TABLE_OWNER
AND DT.TABLE_NAME = DTM.TABLE_NAME
AND NUM_ROWS > 0
AND ROUND ( (DELETES + UPDATES + INSERTS) / NUM_ROWS * 100) >= 10
AND OWNER NOT LIKE 'APEX%'
AND owner NOT LIKE 'SYS%'
AND owner != 'DBSNMP'
ORDER BY 1,3 DESC;
