Automatic statistics collection

In addition to manual statistics collection, 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 for Monday through Sunday. Tasks from Monday to Friday start at 22:00 by default, with a maximum collection duration of 4 hours, while tasks on Saturday and Sunday start at 6:00 by default, 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, focus on the following fields:

• 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 will be executed.
• 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, which is in seconds by default.

Modify the start time and duration of automatic statistics collection

To adapt to your business needs, OceanBase Database optimizer allows you to modify the start time and duration of automatic statistics collection. You can modify 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 next start time of automatic statistics collection tasks

  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:00 PM.

  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, set the start time of automatic statistics collection tasks:

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

  2. Second, set the duration of automatic statistics collection tasks:

     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, set the start time of automatic statistics collection tasks:

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

  2. Second, set the duration of automatic statistics collection tasks:

     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 is the process when an automatic statistics collection task starts? The following figure shows the process.

When automatic statistics collection is performed, whether the statistics of a table are outdated is determined based on the proportion of insert, delete, and update operations performed on the table from the last statistics collection to the current collection. The default threshold is 10%. Note that this change threshold is partition-level. For example, if the proportion of insert, delete, and update operations performed on some partitions of a partitioned table exceeds 10%, statistics will be collected for these partitions. The default change threshold can be modified based on business needs. For more information, see Configure a statistics collection strategy. In addition, OceanBase Database provides views for querying the number of insert, delete, and update operations performed on a table. The following table describes the views.