Oracle 19c : High-Frequency Statistics Gathering

Real-time statistics are a complement to conventional gathered statistics, so there is still a need for both. To help keep gathered statistics up-to-date, we added a new task to do it more frequently

Purpose of High-Frequency Automatic Optimizer Statistics Collection

AutoTask schedules tasks to run automatically in maintenance windows. By default, one window is scheduled for each day of the week. Automatic optimizer statistics collection (DBMS_STATS) runs in all predefined maintenance windows.

Statistics can go stale between two consecutive statistics collection tasks. If data changes frequently, the stale statistics could cause performance problems. For example, a brokerage company might receive tremendous data during trading hours, leading the optimizer to use stale statistics for queries executed during this period.

How High-Frequency Automatic Optimizer Statistics Collection Works

To enable and disable the high-frequency task, set the execution interval, and set the maximum run time, use the DBMS_STATS.SET_GLOBAL_PREFS procedure. The high-frequency task is “lightweight” and only gathers stale statistics. It does not perform actions such as purging statistics for non-existent objects or invoking Optimizer Statistics Advisor. The standard automated job performs these additional tasks.

Automatic statistics collection jobs that run in the maintenance window are not affected by the high-frequency jobs. The high-frequency tasks do not start during the maintenance window, so only one statistics task runs at a time. You can monitor the tasks by querying DBA_AUTO_STAT_EXECUTIONS.

To enable and disable the task, use DBMS_STATS.SET_GLOBAL_PREFS.

You can use DBMS_STATS.SET_GLOBAL_PREFS to set preferences to any of the following values:

AUTO_TASK_STATUS

Enables or disables the high-frequency automatic optimizer statistics collection. Values are:

ON — Enables high-frequency automatic optimizer statistics collection.

OFF — Disables high-frequency automatic optimizer statistics collection. This is the default.

AUTO_TASK_MAX_RUN_TIME

Configures the maximum run time in seconds of an execution of high-frequency automatic optimizer statistics collection. The maximum value is 3600 (equal to 1 hour), which is the default.

AUTO_TASK_INTERVAL

Specifies the interval in seconds between executions of high-frequency automatic optimizer statistics collection. The minimum value is 60. The default is 900 (equal to 15 minutes).

To configure the high-frequency task, you must have administrator privileges.