Automatic statistics collection

In addition to manually collecting statistics, OceanBase Database 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 from Monday to Sunday. By default, tasks for Monday to Friday start at 22:00 with a maximum collection duration of 4 hours, while tasks for Saturday and Sunday start at 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 the following views for you to query 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 for 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, with the default unit being seconds.

Modify the start time and duration of automatic statistics collection

To adapt to different business needs, OceanBase Database allows you to modify the start time and duration of automatic statistics collection. Here are the methods:

• Disable automatic statistics collection tasks

  DBMS_SCHEDULER.DISABLE('$window_name')
  

  Example:

  Disable statistics collection on Mondays.

  CALL DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');
  

• Enable automatic statistics collection tasks

  DBMS_SCHEDULER.ENABLE('$window_name')
  

  Example:

  Enable statistics collection on Mondays.

  CALL DBMS_SCHEDULER.ENABLE('MONDAY_WINDOW');
  

• Set the next start time for automatic statistics collection tasks

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

  Example:

  Set the start time for 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 step:

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

  2. Second step:

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

  Example:

  Set the duration for statistics collection on Mondays to 6 hours.

  1. First step:

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

  2. Second step:

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

Automatic statistics collection mechanism

The preceding section describes the MAINTENANCE WINDOW option related to automatic statistics collection. What is the mechanism for automatic statistics collection after the task starts?

The mechanism for determining whether statistics for a table are outdated is 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%. It is important to note that this change threshold is partition-level. For example, if the proportion of inserts, updates, and deletes in some partitions of a partitioned table exceeds 10%, statistics will be collected for those partitions. The default change threshold can be adjusted based on business needs by configuring the appropriate parameters. Additionally, the OceanBase Database optimizer provides views for querying the number of inserts, updates, and deletes for a table, as follows: