Automatic statistics collection

In addition to manually collecting statistics, OceanBase Database's optimizer performs daily automatic statistics collection using MAINTENANCE WINDOW to ensure iterative updates of statistics. Similar to native Oracle, the OceanBase Database optimizer defines seven automatic statistics collection tasks for each day from Monday to Sunday. The default start time for tasks from Monday to Friday is 22:00, with a maximum collection duration of 4 hours. For Saturday and Sunday, the default start time is 6:00, with a maximum collection duration of 20 hours. The following table provides details.

Check the execution of automatic statistics collection

OceanBase Database provides views for you to query 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 date when the maintenance window task was last executed.
• NEXT_RUN_DATE: the date 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 adapt to your business needs, OceanBase Database 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 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 collection duration of the automatic statistics collection task

  1. Step 1:

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

  2. Step 2:

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

  Here is an example:

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

  1. Step 1:

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

  2. Step 2:

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

How does automatic statistics collection work?

The preceding topic describes the MAINTENANCE WINDOW option for automatic statistics collection. How does automatic statistics collection work? The following figure shows the mechanism.

When automatic statistics collection is enabled, the system determines whether statistics for a table are outdated based on the proportion of inserts, deletes, and updates performed on the table between the last statistics collection and the current collection. The default threshold is 10%. Note that this change threshold is partition-level. For example, if the proportion of inserts, deletes, and updates performed on some partitions of a partitioned table exceeds 10%, statistics for these partitions will be recalculated. The default change threshold can be modified based on business needs. For more information, see Configure statistics collection strategy. In addition, the OceanBase Database optimizer provides views for querying the number of inserts, deletes, and updates on a table. The views are described in the following table: