What is high-frequency statistics gathering?
High-frequency statistics gathering is available for Exadata and Autonomous Database. It runs periodically throughout the day, inside and outside any maintenance window, and keeps optimizer statistics as fresh as possible. This post explains how to control it.
You will guess that high-frequency statistics gathering refers to the automatic collection of optimizer statistics more frequently than traditional statistics gathering. It is implemented using a background task. The default setup in Oracle Autonomous Database configures the task to start every 900 seconds (15 mins) and to execute for up to 3600 seconds (1 hour):
select task_name,
enabled,
interval,
max_run_time
from dba_autotask_schedule_control
where dbid = sys_context('userenv','con_dbid')
and task_name = 'Auto Statistics Management Task';
TASK_NAME ENABL INTERVAL MAX_RUN_TIME
--------------------------------- ----- ---------- ------------
Auto Statistics Management Task TRUE 900 3600
During the active period, the task will gather statistics on stale objects. If this takes longer than 1 hour, the task will timeout and start again after another 15 minutes (that’s in most cases, but sometimes it will be internally throttled).
How is high-frequency statistics gathering enabled and disabled?
It can be enabled and disabled using these commands (issued from the ADMIN account in autonomous or a DBA account in Exadata):
exec dbms_stats.set_global_prefs('AUTO_TASK_STATUS','ON')
exec dbms_stats.set_global_prefs('AUTO_TASK_STATUS','OFF')
How do you control the task properties?
You can change the startup interval and maximum runtime like this. For example, execute up to once per hour for up to two hours.
exec dbms_stats.set_global_prefs('AUTO_TASK_INTERVAL','3600')
exec dbms_stats.set_global_prefs('AUTO_TASK_MAX_RUN_TIME','7200')
The maximum run time you can set is 24hrs (86400 seconds).
At the time of writing, there’s an error in the DBMS_STATS documentation regarding this maximum (it says 3600 seconds), but there’s a bug to correct this.
How do you know it’s running?
You can see when the task was last scheduled, how long it took (in seconds), and its completion status using the following query. If the task is running, you will see a status of RUNNING.
select task_name,
elapsed_time,
status,
last_schedule_time
from dba_autotask_schedule_control
where dbid = sys_context('userenv','con_dbid')
and task_name = 'Auto Statistics Management Task';
TASK_NAME ELAPSED_TIME STATUS LAST_SCHEDULE_TIME
--------------------------------- ------------ ---------- --------------------------------
Auto Statistics Management Task 18 SUCCEEDED 10-JAN-25 10.43.41.149 AM +00:00
How can you tell if it’s working?
In addition to monitoring the task status (above), you can check to see if you have any tables with stale statistics using a query similar to the following:
select table_name, stale_stats from dba_tab_statistics where owner = '<my-application-schema-name>' and stale_stats = 'YES';
Over time, you should expect to see no stale statistics (no rows returned) or a small number of tables that change over time. You can also check when statistics were gathered for any table by looking at the LAST_ANALYSED column in DBA_TAB_STATISTICS.
If you have very large tables that remain stale, this usually indicates that you need to increase the maximum run time of the task using AUTO_TASK_MAX_RUNTIME. Alternatively, you can use manual approaches like DBMS_STATS.GATHER_TABLE_STATS, even if HFS is enabled. Also, you might want to use a manual approach if you need to guarantee that a stale table has fresh statistics at a particular point in time (for example, during an ELT/ETL batch flow).
It is normal to see a task STATUS of TERMINATED intermittently. It means that the task was collecting statistics when the maximum runtime period was reached. When the task restarts, it will continue to gather statistics and will generally settle down once all objects have fresh statistics. However, if you have very large tables and their statistics cannot be gathered within the maximum time period, the task is likely to be terminated every time it is invoked, so you should either increase the maximum runtime or gather statistics for the relevant tables manually.
There are now views that help you to view task history and understand what gather stats operations have been executed. See the next section.
High Frequency Task Status Information
Oracle Autonomous Database 19c and Oracle Database 23ai have two views that are useful for monitoring high-frequency statistics gathering. They are dba_auto_stat_obj_gather_details and dba_auto_stat_executions.
The former view is particularly useful for checking whether you have objects that repeatedly time out because the AUTO_TASK_MAX_RUN_TIME setting is potentially too short. The maximum value reported in consecutive_time_outs is capped at 7, so if you see this value, you should increase the max run time setting or gather the statistics on the relevant table manually.
select target, consecutive_time_outs from dba_auto_stat_obj_gather_details order by consecutive_time_outs desc fetch first 10 rows only;
The dba_auto_stat_executions view allows you to see when the high-frequency task has been executed and whether or not it timed out. Remember that timeouts are perfectly normal, so it is not necessarily indicative of a problem. It’s most useful to check consequtive_time_outs instead (as shown in the query above).
Note that dba_auto_stat_obj_gather_details is not a continuous history of gather stats operations. Instead, it reports the most recent status information for a given object, when available.
The views dba_optstat_operations and dba_optstat_operation_tasks are not populated with data in Oracle Autonomous Database (to reduce system overheads).
Where can you find out more?
Check out the DBMS_STATS API documentation and the HFS section on this page.