Enabling automatic statistics collection

Having accurate and complete database statistics is critical to efficient data access and optimal workload performance. Use the automatic statistics collection feature of the automated table maintenance functionality to update and maintain relevant database statistics.

About this task

To enable automatic statistics collection, you must first configure your database by setting the auto_maint and the auto_tbl_maint database configuration parameters to ON.

After setting the auto_maint and the auto_tbl_maint database configuration parameters to ON, you have the following options:

• To enable background statistics collection, set the auto_runstats database configuration parameter to ON.
• To enable background statistics collection for statistical views, set both the auto_stats_views and auto_runstats database configuration parameters to ON.
• To enable background statistics collection to use sampling automatically for large tables and statistical views, also set the auto_sampling database configuration parameter to ON. Use this setting in addition to auto_runstats (tables only) or to auto_runstats and auto_stats_views (tables and statistical views).
• To enable real-time statistics collection, set both auto_stmt_stats and auto_runstats database configuration parameters to ON.
• To enable background statistics collection of column group statistics on base tables, set both the auto_runstats and auto_cg_stats database configuration parameters to ON.  To minimize the overhead of collecting the extra column group statistics on large tables, enable background statistics collection to use sampling automatically by setting auto_sampling to ON.