Automatic statistics collection

In addition to manual statistics collection, the OceanBase Database optimizer performs daily automatic statistics collection by using MAINTENANCE WINDOW. This ensures that statistics can be iteratively updated. Similar to native Oracle, the OceanBase Database optimizer defines seven automatic statistics collection tasks from Monday to Sunday. By default, the tasks from Monday to Friday start at 22:00, with a maximum collection duration of 4 hours, and the tasks on Saturday and Sunday start at 6:00, with a maximum collection duration of 20 hours. The following table shows the maintenance windows.

Query the execution status of automatic statistics collection

The OceanBase Database optimizer provides the following views to query the execution status of automatic statistics collection:

For the DBA_SCHEDULER_JOBS view, pay attention to the following columns:

• JOB_NAME: the name of the maintenance window task.
• LAST_START_DATE: the time when the last maintenance window task was executed.
• NEXT_RUN_DATE: the time when the next maintenance window task is scheduled to be executed.
• ENABLED: specifies whether the maintenance window task is enabled.
• FAILURE_COUNT: the number of failures of the maintenance window task. If the value is not 0, contact OceanBase Technical Support for troubleshooting.
• MAX_RUN_DURATION: the maximum duration of the maintenance window task. The default unit is seconds.

Change the start time and duration of automatic statistics collection

Users can modify the start time and duration of automatic statistics collection based on their business characteristics. The OceanBase Database optimizer provides the following methods to change the start time and duration of automatic statistics collection:

• Disable or enable the automatic statistics collection task

  DBMS_SCHEDULER.DISABLE($window_name)
  DBMS_SCHEDULER.ENABLE($window_name);
  

• Set the start time of the next automatic statistics collection task

  DBMS_SCHEDULER.SET_ATTRIBUTE($window_name, 'NEXT_DATE', $next_time);
  

• Set the duration of automatic statistics collection

  DBMS_SCHEDULER.SET_ATTRIBUTE($window_name, 'DURATION', $duation_time);
  

Examples

Example 1: Disable automatic statistics collection on Mondays

call dbms_scheduler.disable('MONDAY_WINDOW');

Example 2: Enable automatic statistics collection on Mondays

call dbms_scheduler.enable('MONDAY_WINDOW');

Example 3: Set the start time of automatic statistics collection on Mondays to 8 p.m.

call dbms_scheduler.set_attribute('MONDAY_WINDOW', 'NEXT_DATE', '2022-09-12 20:00:00');

Example 4: Set the duration of automatic statistics collection on Wednesdays to 8 hours

call dbms_scheduler.set_attribute('WEDNESDAY_WINDOW', 'JOB_ACTION', 'DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(28800000000)');

Mechanism of automatic statistics collection

The preceding content describes the MAINTENANCE WINDOW parameter related to automatic statistics collection. But how does the mechanism work after the automatic statistics collection task starts? <!--The following figure shows the mechanism of automatic statistics collection.

-->

In automatic statistics collection, the optimizer determines whether the statistics of a table are outdated based on the ratio of insert, delete, and update operations performed on the table since the last statistics collection. The default value of this ratio is 10%. Note that this ratio is partition-level. For example, if the ratio of insert, delete, and update operations exceeds 10% for some partitions of a partitioned table, statistics of these partitions are collected. The default ratio can be modified. The OceanBase Database optimizer also provides views that allow you to query the number of insert, delete, and update operations performed on a table. The following table describes the views.