Automatically collect statistics

The OceanBase Database optimizer performs automatic daily statistics collection based on the maintenance window to keep statistics updated. Like in a native Oracle database, the OceanBase Database optimizer schedules an automatic statistics collection task each day. 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 describes the details.

Query the execution of automatic statistics collection tasks

The following table describes the views that you can query to learn about the status of statistics collection tasks automatically executed by the OceanBase Database optimizer.

Key fields to note within the DBA_SCHEDULER_JOBS view include:

• JOB_NAME: the name of the maintenance window job.
• LAST_START_DATE: the time when the last maintenance window job was performed.
• NEXT_RUN_DATE: the time when the next maintenance window job will be executed.
• ENABLED: indicates whether the current maintenance window job is enabled.
• FAILURE_COUNT: the number of failed maintenance window jobs. If the value is not 0, contact OceanBase Technical Support for troubleshooting.
• MAX_RUN_DURATION: the maximum duration of the maintenance window job. By default, the duration is in seconds.

Modify the start time and duration of automatic statistics collection jobs

The OceanBase Database optimizer allows you to modify the attributes of automatic statistics collection jobs as needed by using the following methods:

• Disable and enable automatic statistics collection jobs

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

• Set the start time of the next automatic statistics job

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

• Set the duration of automatic statistics collection jobs

  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 the automatic statistics collection job 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 the automatic statistics collection job on Mondays to 6 hours.

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

Working mechanism of automatic statistics collection

The following figure shows how an automatic statistics collection job is executed.

The OceanBase Database optimizer determines whether the statistics of a table have expired based on the percentage of the changed table data since the last statistics collection. The default percentage is 10%. Note that this percentage of change is a partition-level parameter. For example, if the percentage of added, deleted, and updated data of a partition exceeds 10% of the total data of the partition, the statistics of this partition are re-collected. The default percentage of change can be configured as needed by setting a preference. For more information, see Configure statistics collection strategies. The following table describes the views that the OceanBase Database optimizer provided for you to query the number of ADD, DELETE, and UPDATE operations on a table.