Automatic statistics collection

In addition to manually collecting statistics, OceanBase Database's optimizer performs daily automatic statistics collection using the MAINTENANCE WINDOW to ensure iterative updates of statistics. 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 provides 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: 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

To modify the start time and duration of automatic statistics collection based on your business needs, OceanBase Database allows you to change the following parameters:

• 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 the automatic statistics collection task 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 the automatic statistics collection task 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?

The preceding description covers the MAINTENANCE WINDOW related to automatic statistics collection. What about the process after an automatic statistics collection task starts? The following figure shows the process.

The system determines whether the statistics of a table are outdated based on the proportion of inserts, updates, and deletes performed on the table from the last statistics collection to the current collection. The default threshold is 10%. This change threshold is partition-level. For example, if the change ratio of a partition in a partitioned table exceeds 10%, the system will re-collect statistics for that partition. The default change threshold can be adjusted based on business needs by setting the corresponding parameter. For more information, see Configure a statistics collection strategy. Additionally, the OceanBase Database optimizer provides views to query the number of inserts, updates, and deletes for a table, as shown below: