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 start time for tasks from Monday to Sunday is 22:00, 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 relevant views of the DBMS_SCHEDULER system package to obtain information about the MAINTENANCE WINDOW.

Set MAINTENANCE WINDOW attributes

Currently, the attribute settings for the MAINTENANCE WINDOW in OceanBase Database are also implemented through the DBMS_SCHEDULER system package. The currently supported attribute setting 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. They determine whether statistics collection is needed for a table again. The data in tables in the database 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. This improves the execution efficiency of automatic statistics collection tasks.

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

Automatic statistics collection strategy

In OceanBase Database, a table's statistics are considered expired if the cumulative impact of insert, delete, and update operations on the table since the last statistics collection exceeds a set threshold. The default threshold is 10% of the total number of rows in the table, which can be adjusted via Prefs. For details, see Manage collection strategy parameters (Prefs).

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, but not all have 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 collection and update strategies for partitioned table statistics, and detailed examples, see Collection and update strategies for partitioned table statistics.