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 every day and have a maximum collection duration of four hours, as shown in the following table.

Query MAINTENANCE WINDOW information

In OceanBase Database, the MAINTENANCE WINDOW 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

In OceanBase Database, setting attributes for the MAINTENANCE WINDOW 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 time for the next start of 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 are crucial for automatic statistics collection, as they determine whether the table needs statistics collection again. Table data in the database is not updated daily. Therefore, for tables with no data changes or where data changes are within expected ranges, there is no need to collect statistics again, which improves the execution efficiency of automatic statistics collection tasks.

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

Automatic statistics collection strategy

In OceanBase Database, a table's statistics are considered expired if the cumulative number of rows affected by insertions, deletions, or modifications since the last statistics collection exceeds a set threshold. The default threshold is 10% of the total table rows and can be adjusted via Prefs. For details, see Collection strategy parameter (Prefs) management function.

The 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, it is automatically collected.

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

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

• 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 statistics collection is performed automatically.

  • 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 inferred incrementally.

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