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