Automatic statistics collection

In addition to manually collecting statistics, OceanBase Database's optimizer automatically collects statistics on a daily basis using the MAINTENANCE WINDOW feature to ensure that statistics can be iteratively updated. Similar to native Oracle, OceanBase Database's optimizer defines seven automatic statistics collection tasks for each day from Monday to Sunday. By default, these tasks start at 22:00 and have a maximum collection duration of 4 hours, as shown in the table below.

Check the execution status of automatic statistics collection

OceanBase Database provides the following views to check the execution status of automatic statistics collection.

For the DBA_SCHEDULER_JOBS view, focus on the following fields:

• JOB_NAME: the name of the maintenance window task.
• LAST_START_DATE: the date and time of the last execution of the maintenance window task.
• NEXT_RUN_DATE: the date and time of the next execution of the maintenance window task.
• ENABLED: indicates whether the maintenance window task is enabled.
• FAILURE_COUNT: the number of failures of the maintenance window task. If this 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

Considering that users may need to adjust the start time and duration of automatic statistics collection based on their specific business requirements, OceanBase Database's optimizer provides the following methods to modify the properties 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, run the following statement:

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

  2. Then, run 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.

  3. First, run the following statement:

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

  4. Then, run the following statement:

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

How automatic statistics collection works

The previous section introduced the MAINTENANCE WINDOW related to automatic statistics collection. But what happens after an automatic statistics collection task starts? The diagram below illustrates the workflow of automatic statistics collection.

When automatic statistics collection is performed, the system determines whether a table’s statistics are outdated based on the proportion of inserts, deletes, and updates that have occurred since the last collection. The default threshold is 10%. Note that this threshold is evaluated at the partition level. For example, if the proportion of inserts, deletes, or updates in certain partitions of a partitioned table exceeds 10%, statistics for those partitions will be recalculated. You can adjust this threshold according to your business needs. For more information. For more information, see Configure the statistics collection strategy. In addition, OceanBase Database's optimizer provides views for querying the number of inserts, deletes, and updates on a table. These views are described in the following table: