Information on Statistics collection on 11gR2

In 11gR2 statistics collection is automatic by default.The statistics will be collected by using an auto job, if the statistics found to be stale for an object or if any DDL command used against the object.

Please see the below for the job details.

select client_name,status,consumer_group,attributes from DBA_AUTOTASK_CLIENT
 
CLIENT_NAME                    STATUS    CONSUMER_GROUP     
------------                   ---------  --------------
auto optimzer stat collection ENABLED ORA$AUTOTASK_STATS_GROUP


The stale percent is determined based on the number of DML operations occured on the table ie., the percentage of number of rows updated or inserted or deleted on the table. By default the threshold limit for the stale percentage is 10%.

You can check this values by using the below query.

select dbms_stats.GET_PREFS ('STALE_PERCENT','OWNER','TABLENAME') from dual.

To check the objects considered for auto stats collection we can check the parameter AUTOSTATS_TARGET.The value of this parameter controls the objects considered for stats collection.

The values:
   'ALL' - Statistics collected for all objects in system
   'ORACLE' - Statistics collected for all Oracle owned objects
   'AUTO' - Oracle decides on which objects to collect statistics

We can check this by using the below query

select dbms_stats.GET_PREFS ('AUTOSTATS_TARGET') from dual;
DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET')                                      
-------------------------------------------
AUTO 

To check the percentage of rows modified or inserted or deleted on the tables you can use the below query

select * from DBA_TAB_MODIFICATIONS where table_owner='SCOTT'
If you need to check the information about stats collection on your tables, you can use the below query

select * from DBA_TAB_STATS_HISTORY where owner='SCOTT'
select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB
where client_name='%stats%';
To enable or disable the Auto stats job please use the below procs.

ENABLE:

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

DISABLE:

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

Other useful queries

SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed FROM dba_autotask_client_history WHERE client_name like '%stats%';

SELECT * FROM dba_autotask_client_history WHERE client_name like '%stats%';

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

SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed FROM dba_autotask_client_history WHERE client_name like '%stats%';


Regards,
Navaneeth