What kind of statistics do the Automated tasks collect?
The 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. The Automatic Statistics Gathering 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. The statistics collected are generic one and not customized; however since they use “AUTO” by default, a number of decisions are automatically tailored to the data.
For many databases these automatic statistics are adequate, however, since these provide basic and general statistics it may be possible to collect better statistics by customizing the statistics collection. The flexibility is there to allow you to gathers stats daily for some tables, collect hourly statistics for others, and just once for static data. You can decide to collect more accurate statistics depending on the data volatility and the performance of the current statistics.
How to enable auto stats collection?
If for some reason automatic optimizer statistics collection is disabled, you can enable it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
exec DBMS_AUTO_TASK_ADMIN.ENABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);
How to disable the auto stats collection?
In situations when you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
exec DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);