Automatic statistics collection

In addition to manually collecting statistics, OceanBase Database's optimizer performs daily automatic statistics collection using MAINTENANCE WINDOW to ensure that statistics are iteratively updated. Similar to native Oracle, OceanBase Database's optimizer defines seven automatic statistics collection tasks from Monday to Sunday. For tasks scheduled from Monday to Friday, the default start time is 22:00 with a maximum collection duration of 4 hours. For tasks scheduled on Saturday and Sunday, the default start time is 6:00 with a maximum collection duration of 20 hours. The following table describes the maintenance windows.

Check the execution of automatic statistics collection

OceanBase Database optimizer provides the following views for you to check the execution of automatic statistics collection.

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

• JOB_NAME: the name of the maintenance window task.
• LAST_START_DATE: the time when the maintenance window task was last executed.
• NEXT_RUN_DATE: the time when the maintenance window task will be executed next.
• ENABLED: indicates 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.
• MAX_RUN_DURATION: the maximum duration of the maintenance window task. The unit is seconds by default.

Modify the start time and duration of automatic statistics collection

To adapt to different business characteristics, OceanBase Database optimizer allows you to modify the start time and duration of automatic statistics collection.

• Disable automatic statistics collection tasks

  DBMS_SCHEDULER.DISABLE('$window_name')
  

  Here is an example:

  Disable automatic statistics collection on Mondays.

  CALL DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');
  

• Enable automatic statistics collection tasks

  DBMS_SCHEDULER.ENABLE('$window_name')
  

  Here is an example:

  Enable automatic statistics collection on Mondays.

  CALL DBMS_SCHEDULER.ENABLE('MONDAY_WINDOW');
  

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

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

  Here is an example:

  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');
  

• Set the duration of automatic statistics collection tasks

  1. First, execute the following statement:

     DBMS_SCHEDULER.SET_ATTRIBUTE('$window_name', 'JOB_ACTION', 'DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC()')
     

  2. Then, execute the following statement:

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

  Here is an example:

  Set the duration of automatic statistics collection on Mondays to 6 hours.

  1. First, execute the following statement:

     CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'JOB_ACTION', 'DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC()')
     

  2. Then, execute the following statement:

     CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'DURATION', 21600);
     

How does automatic statistics collection work?

This section describes how automatic statistics collection works.

The system determines whether statistics for a table are outdated based on the proportion of inserts, updates, and deletes performed on the table between the last statistics collection and the current collection. The default threshold is 10%. This change threshold is partition-level. For example, if the change threshold for a partition of a partitioned table exceeds 10%, statistics will be recalculated for that partition. The change threshold can be configured based on business needs. For more information, see Configure a statistics collection strategy. Additionally, the OceanBase Database optimizer provides views for querying the number of inserts, updates, and deletes for a table. The following table describes these views: