Automatic statistics collection

At present, the OceanBase Database optimizer implements automatic statistics collection daily by using MAINTENANCE WINDOW provided by the DBMS_SCHEDULER system package, thereby ensuring continuous iterative updates of statistics.

MAINTENANCE WINDOW

OceanBase Database runs seven automatic statistics collection jobs in the maintenance windows from Monday to Sunday, with one job each day. From Monday to Friday, the jobs start at 22:00, and the maximum collection duration is 4 hours. On Saturday and Sunday, the jobs start at 6:00, and the maximum collection duration is 20 hours, as shown in the following table.

Query MAINTENANCE WINDOW information

At present, MAINTENANCE WINDOW in the OceanBase Database is implemented based on the DBMS_SCHEDULER system package. Therefore, you can also query relevant information of MAINTENANCE WINDOW in views related to the DBMS_SCHEDULER system package.

Set properties of MAINTENANCE WINDOW

At present, properties of MAINTENANCE WINDOW in OceanBase Database are set based on the DBMS_SCHEDULER system package. The following syntax is supported:

• Disable or enable automatic statistics collection:

  DBMS_SCHEDULER.DISABLE($window_name);
  
  DBMS_SCHEDULER.ENABLE($window_name);
  

  • Example 1: Disable automatic statistics collection for Mondays

    CALL  DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');
    

  • Example 2: Enable automatic statistics collection for Mondays

    CALL DBMS_SCHEDULER.ENABLE('MONDAY_WINDOW');
    

• Set the time when the next statistics collection job starts:

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

  Example: Set the start time of the automatic statistics collection job for Mondays to 8 p.m.

  CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'NEXT_DATE', '2022-09-12 20:00:00');
  

• Set the time interval at which a statistics collection job is reinitiated:

  DBMS_SCHEDULER.SET_ATTRIBUTE($window_name, 'DURATION', $duration_time);
  

  Example: Set the time interval at which the statistics collection job is reinitiated to 6 hours.

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

DML STATS

DML STATS records the numbers of addition, deletion, and modification operations performed on a table.

DML STATS of a table is essential for automatic statistics collection. It determines whether statistics need to be collected again on a table. Not all tables in the database are updated daily. Therefore, for tables that do not have data changes and tables whose data changes are within the expected scope, statistics do not need to be collected again. This mechanism improves the execution efficiency of automatic statistics collection jobs.

The following table lists the views that provide DML STATS information in Oracle and MySQL modes.

Automatic statistics collection strategies

In OceanBase Database, statistics are considered as expired when they meet the following criteria: the percentage of incremental DML operations on the current table exceeds the specified threshold. The percentage of incremental DML operations is the number of addition, deletion, and modification operations performed on the table during the period between the last statistics collection and the current statistics collection to the total number of DML operations in the last statistics collection. The default value of the threshold is 10%. You can use preference settings (prefs) to adjust the threshold. For more information, see Collection strategy prefs management.

The OceanBase Database optimizer automatically collects statistics based on the following strategies:

• Automatic statistics collection strategies for system tables and non-partitioned user tables:

  • If the table does not have GLOBAL-level statistics, statistics are collected automatically.

  • If the table has GLOBAL-level statistics and the statistics have expired, statistics are collected automatically.

  Otherwise, the OceanBase Database optimizer does not automatically collect statistics.

• OceanBase Database supports the following automatic statistics collection strategies for partitioned tables:

  • If the table does not have any statistics, collect all statistics automatically.

  • If the table has partition-level statistics but no GLOBAL-level statistics, collect statistics automatically in an incremental way.

  • If the table has GLOBAL-level statistics and the statistics have expired, all statistics are collected automatically.

  • If the table has GLOBAL-level statistics, and the statistics of only some partitions have expired, the system automatically collects the statistics of partitions with expired statistics and deduces the GLOBAL-level statistics based on the incremental data.