How to check automatic statistics collection in Oracle in 11g

Automatic statistics collection is a new feature starting 10g. Oracle has done a lot of changes to it starting with 11g. In this article, we will see How to check automatic statistics collection in Oracle, How to enable automatic statistics collection in Oracle

What is automatic statistics collection?

• Automatic statistics jobs automatically gather Missing and Stale statistics on tables, indexes, and partitions for use by the Cost Based Optimizer (CBO) in determining the most optimal access paths for queries.
• This job was built to assist with the collection of statistics from the start with newly created databases so that there are accurate statistics to use rather than relying on defaults.

How to check automatic statistics collection in Oracle

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

How to enable it

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

How to disable it

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

How do to check the values of parameters ( estimate percent, type of histograms etc) used by the job?

For 19c

set pages 100 lines 80
select dbms_stats.get_prefs('ESTIMATE_PERCENT'),
dbms_stats.get_prefs('METHOD_OPT'),
dbms_stats.get_prefs('GRANULARITY'),
dbms_stats.get_prefs('CASCADE'),
dbms_stats.get_prefs('DEGREE'),
dbms_stats.get_prefs('NO_INVALIDATE'),
dbms_stats.get_prefs('AUTOSTATS_TARGET'),
dbms_stats.get_prefs('STALE_PERCENT'),
dbms_stats.get_prefs('INCREMENTAL'),
dbms_stats.get_prefs('PUBLISH'),
dbms_stats.get_prefs('CONCURRENT'),
dbms_stats.get_prefs('INCREMENTAL_STALENESS'),
dbms_stats.get_prefs('INCREMENTAL_LEVEL'),
dbms_stats.get_prefs('STAT_CATEGORY'),
dbms_stats.get_prefs('AUTO_STAT_EXTENSIONS'),
dbms_stats.get_prefs('APPROXIMATE_NDV_ALGORITHM'),
dbms_stats.get_prefs('AUTO_TASK_STATUS')
from dual;

I hope you like this article on automatic statistics collection in Oracle

Related Articles

How to delete Optimizer preference: check out How to delete Optimizer preference at table level, schema level, database level and global level with exact commands
How to gather Statistics with DBMS_STATS :check out How to gather Statistics with DBMS_STATS for table, schema, database and index level using oracle preferred way
Optimizer statistics preferences in Oracle: check out what is Optimizer statistics preferences in Oracle, various preferences available in Oracle 19c, How to set the Optimizer statistics preferences
How to check optimizer statistics preferences at the table level 
PREFERENCE_OVERRIDES_PARAMETER in Oracle
How to set Schema level preference