How Can We Find Out Status Of Task 'Auto Optimizer Stats Collection' [ID 77

郑全

In this Document

Goal

Fix

11g

10g

References

---

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]
Information in this document applies to any platform.

Goal

Explain how to find the status of 'GATHER_STATS_JOB' or in fact 'auto optimizer stats collection' in 11g.

Fix

11g

In 11g, the automated maintenance task that collects statistics is called 'auto optimizer stats collection'. You can determine its' status by selecting fromDBA_AUTOTASK_CLIENT:

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

CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED


The DBA_AUTOTASK_CLIENT view also displays frequency information about current and past automated maintenance tasks in columns MEAN_INCOMING_TASKS_7_DAYS and MEAN_INCOMING_TASKS_30_DAYS so you can see this historical information using a query like:

SELECT CLIENT_NAME ,
STATUS ,
MEAN_INCOMING_TASKS_7_DAYS,
MEAN_INCOMING_TASKS_30_DAYS
FROM DBA_AUTOTASK_CLIENT
WHERE CLIENT_NAME = 'auto optimizer stats collection'
/

For details of the other columns in DBA_AUTOTASK_CLIENT,see:

Oracle Database Reference
11g Release 2 (11.2)
Part Number E25513-01
http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_3084.htm

NOTE that the status in DBA_AUTOTASK_TASK and DBA_AUTOTASK_CLIENT may be different. See:

Document 858852.1 DBA_AUTOTASK_TASK and DBA_AUTOTASK_CLIENT Shows Different Status For Auto Optimizer Stats Collection

10g

The following is the 'old method' for checking the current and past automated maintenance tasks:

SELECT j.job_name ,
j.program_name ,
j.schedule_name,
j.job_class ,
p.enabled
FROM dba_scheduler_programs p,
dba_scheduler_jobs j
WHERE p.program_name=j.program_name;


JOB_NAME
------------------------------
PROGRAM_NAME
--------------------------------------------------------------------------------
SCHEDULE_NAME
--------------------------------------------------------------------------------
JOB_CLASS ENABL
------------------------------ -----
..
GATHER_STATS_JOB
GATHER_STATS_PROG
MAINTENANCE_WINDOW_GROUP
AUTO_TASKS_JOB_CLASS TRUE

Note: If the 10g method is used on 11g, then it does not return any information as the implementation is quite different in 11g:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning and Real Application Testing options

SQL> SELECT job_name ,
job_type ,
program_name ,
schedule_name,
job_class
FROM dba_scheduler_jobs
WHERE job_name = 'GATHER_STATS_JOB';

no rows selected

References

NOTE:1233203.1 - FAQ: Automatic Statistics Collection Jobs (10g and 11g)