Automatic Stats Jobs

script to check stats job stats

for 11g
_________________________________________________________________________________________________________
set pages 999
set lines 180
col client_name for a35
col JOB_STATUS for a10
col JOB_START_TIME for a47
col JOB_DURATION for a15
select client_name, job_status, job_start_time, job_duration from dba_autotask_job_history
where client_name='auto optimizer stats collection' order by job_start_time;



for 10g
_______________________________________________________________________________________________________________________________

set pages 999
set lines 300
col JOB_NAME for a25
col STATUS for a10
col ACTUAL_START_DATE for a40
col RUN_DURATION for a15
select JOB_NAME,STATUS,ACTUAL_START_DATE,RUN_DURATION from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name ='GATHER_STATS_JOB' order by ACTUAL_START_DATE;

For 11g
Troubleshooting Scheduler Autotasks issues (Doc ID 1561498.1)

How to Force Table Statistics to be Gathered on Every Execution of Automatic Statistics and GATHER_DATABASE_STATS jobs (Doc ID 1490594.1)
exec dbms_stats.set_table_prefs('SCOTT','EMP','STALE_PERCENT',0);

BEGIN
dbms_auto_task_admin.enable();
END;

• How to enable auto stats collection?

  If for some reason automatic optimizer statistics collection is disabled, you can enable it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
  
  

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

• How to disable the auto stats collection?

  In situations when you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
  
  

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

• How can I check the status of the 'auto optimizer stats collection'?

  The status of the automatic statistics collection can be checked using:
  
  

  select client_name, JOB_SCHEDULER_STATUS 
   from DBA_AUTOTASK_CLIENT_JOB
   where client_name='auto optimizer stats collection';

  
  The possible Job status:
  
  • DISABLED
  • RETRY SCHEDULED
  • SCHEDULED
  • RUNNING
  • COMPLETED
  • BROKEN
  • FAILED
  • REMOTE
  • SUCCEEDED
  • CHAIN_STALLED

• How can I check whether or not the database has the 'auto optimizer stats collection' job enabled to run during the next maintenance window?

  SELECT CLIENT_NAME,
         STATUS
  FROM   DBA_AUTOTASK_CLIENT
  WHERE  CLIENT_NAME = 'auto optimizer stats collection';

• How can I see the history of the automatic stats job for each day?

  
  

  SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed
   FROM dba_autotask_client_history
   WHERE client_name like '%stats%';
   
                                                    JOBS    JOBS     JOBS
   CLIENT_NAME                     WINDOW_NAME      CREATED STARTED  COMPLETED
   ------------------------------- ---------------- ------- -------- ----------
   auto optimizer stats collection THURSDAY_WINDOW        1        1          1
   auto optimizer stats collection SUNDAY_WINDOW          3        3          3
   auto optimizer stats collection MONDAY_WINDOW          1        1          1
   auto optimizer stats collection SATURDAY_WINDOW        2        2          2

• How to manually execute the Optimizer Statistics Auto Task?

  
  In 11g the Auto-Task infrastructure replaced the need for the gather_stats_job and you can execute the following command to accomplish manual statistics collection:
  
  

  SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;