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 are updated iteratively. 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, while 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 of automatic statistics collection

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

For the DBA_SCHEDULER_JOBS view, focus on 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: 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.

Modify the start time and collection duration of automatic statistics collection

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

• Enable or disable 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

• Enable automatic statistics collection tasks

  DBMS_SCHEDULER.ENABLE('$window_name')
  

Example 1: Disable automatic statistics collection on Mondays

Enable automatic statistics collection on Mondays.

Example 2: Enable automatic statistics collection on Mondays

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

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

**Here is an example:**

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. What is the mechanism of automatic statistics collection tasks? 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 will be collected. The default ratio can be modified. For more information, you can set the corresponding parameter based on your business requirements. For more information, see Configure statistics collection strategy. The OceanBase Database optimizer also provides views that you can use to query the number of insert, delete, and update operations performed on a table. The following table describes the views.