Automatic statistics collection

The OceanBase Database optimizer uses MAINTENANCE WINDOW tasks, which are implemented through the DBMS_SCHEDULER system package, to collect statistics automatically every day and keep them up to date.

MAINTENANCE WINDOW

OceanBase Database provides seven MAINTENANCE WINDOW tasks for automatic statistics collection, one for each day of the week. Each task starts at 22:00 and can run for a maximum of four hours, as shown in the following table.

Query MAINTENANCE WINDOW information

In OceanBase Database, MAINTENANCE WINDOW tasks are implemented through the DBMS_SCHEDULER system package. You can therefore query DBMS_SCHEDULER-related views for information about these tasks.

Set MAINTENANCE WINDOW attributes

In OceanBase Database, MAINTENANCE WINDOW attributes are also configured through the DBMS_SCHEDULER system package. The following syntax is supported:

• Disable or enable automatic statistics collection tasks:

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

  • Example 1: Disable automatic statistics collection on Mondays

    CALL  DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');
    

  • Example 2: Enable automatic statistics collection on Mondays

    CALL DBMS_SCHEDULER.ENABLE('MONDAY_WINDOW');
    

• Set the next start time of a statistics collection task:

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

  Example: Set automatic statistics collection to start at 8:00 PM on Mondays.

  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.

DML STATS plays a crucial role in automatic statistics collection because it determines whether statistics need to be collected again for a table. Table data is not necessarily updated every day. Therefore, statistics do not need to be recollected for tables whose data has not changed or has changed only within the expected range. This improves the efficiency of automatic statistics collection tasks.

The following table lists the views and tables that you can use to query DML STATS in Oracle- and MySQL-compatible modes.

Automatic statistics collection strategy

In OceanBase Database, a table's statistics are considered stale when the cumulative number of rows affected by insert, delete, and update operations since statistics were last collected exceeds a specified threshold. The default threshold is 10% of the total number of rows in the table and can be adjusted by using Prefs. For more information, see Manage statistics preferences.

The OceanBase Database optimizer uses the following automatic statistics collection strategies:

• Automatic collection strategy for system tables and nonpartitioned user tables:

  • If a table has no GLOBAL-level statistics, statistics are collected automatically.

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

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

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

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

  • If a table has partition-level statistics but no GLOBAL-level statistics, statistics are collected automatically and incrementally.

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

  • If a table has GLOBAL-level statistics that are not stale but the statistics of some partitions are stale, statistics are collected automatically for the stale partitions, and GLOBAL-level statistics are derived incrementally.

For more information about statistics collection and update strategies for partitioned tables and related examples, see Statistics collection and update strategies for partitioned tables.