Automatic statistics collection

Currently, the optimizer of OceanBase Database implements daily automatic statistics collection through the MAINTENANCE WINDOW defined by the DBMS_SCHEDULER system package, ensuring continuous iteration and update of statistics.

MAINTENANCE WINDOW

OceanBase Database defines the MAINTENANCE WINDOW as having seven automatic statistics collection tasks from Monday to Sunday. The tasks start at 22:00 on each day from Monday to Sunday, with a maximum collection duration of 4 hours, as shown in the following table.

Query MAINTENANCE WINDOW information

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

Set MAINTENANCE WINDOW attributes

Currently, setting attributes for the MAINTENANCE WINDOW in OceanBase Database is also implemented through the DBMS_SCHEDULER system package. The currently supported attribute syntax is as follows:

• Disable/Enable automatic statistics task collection:

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

  • Example 1: Disable automatic statistics collection on Monday

    CALL  DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');
    

  • Example 2: Enable automatic statistics collection on Monday

    CALL DBMS_SCHEDULER.ENABLE('MONDAY_WINDOW');
    

• Set the next start time for the statistics task:

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

  Example: Set the start time for automatic statistics collection on Monday to 8:00 PM.

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

DML STATS

DML STATS records the number of insert, delete, and update operations performed on a table.

The DML STATS of a table play a crucial role in automatic statistics collection. It determines whether statistics collection is required for a table again. The data information in a database table is not updated daily. Therefore, for tables with no data changes or where data changes are within the expected range, there is no need to collect statistics again, which improves the execution efficiency of automatic statistics collection tasks.

The following table lists the relevant views and tables that support querying DML STATS information in Oracle-compatible mode and MySQL-compatible mode.

Automatic statistics collection strategy

In OceanBase Database, the criteria for determining whether table statistics are expired are as follows: if the cumulative number of rows affected by insert, delete, and update operations on a table since the last statistics collection exceeds a set threshold, the statistics are considered expired. The default value for this threshold is 10% of the total number of rows in the table, which can be adjusted via Prefs. For details, see Collection strategy parameter (Prefs) management function.

The current automatic statistics collection strategies of the OceanBase Database optimizer are as follows:

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

  • If a table has no GLOBAL-level statistics, automatic statistics collection is performed.

  • If a table has GLOBAL-level statistics but they have expired, automatic statistics collection is performed.

  Otherwise, the OceanBase Database optimizer does not perform automatic statistics collection.

• Automatic collection strategy for partitioned user tables in OceanBase Database:

  • If a table has no statistics at all, all statistics are automatically collected.

  • If a table has partition-level statistics but no GLOBAL-level statistics, incremental automatic statistics collection is performed.

  • If a table has GLOBAL-level statistics but they have expired, all statistics are automatically collected.

  • If a table has GLOBAL-level statistics that are not all expired, only some partition statistics are expired, the expired partition statistics are automatically collected, and the GLOBAL-level statistics are derived incrementally.

For more information about the statistics collection and update strategies for partitioned tables, as well as detailed examples, see Statistics collection and update strategies for partitioned tables.