What is automatic statistics collection in Oracle?
- 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 automatic statistics collection in Oracle
exec DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => 'auto optimizer stats job',
window_name => NULL);
How to disable automatic statistics collection in Oracle
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.gSQL> et_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;
Leave a Reply