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 defines MAINTENANCE WINDOW as seven automatic statistics collection jobs from Monday to Sunday. The jobs start at 22:00 from Monday to Sunday, and the maximum collection duration is 4 hours, as shown in the following table.
| Maintenance window name | Start time/Frequency | Maximum collection duration |
|---|---|---|
| MONDAY_WINDOW | 22:00/per week | 4 hours |
| TUESDAY_WINDOW | 22:00/per week | 4 hours |
| WEDNESDAY_WINDOW | 22:00/per week | 4 hours |
| THURSDAY_WINDOW | 22:00/per week | 4 hours |
| FRIDAY_WINDOW | 22:00/per week | 4 hours |
| SATURDAY_WINDOW | 22:00/per week | 4 hours |
| SUNDAY_WINDOW | 22:00/per week | 4 hours |
Query MAINTENANCE WINDOW information
At present, MAINTENANCE WINDOW in 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.
| Mode | View name | Description |
|---|---|---|
| Oracle-compatible mode | DBA_SCHEDULER_JOBS | Query job information |
| Oracle-compatible mode | sys.ALL_VIRTUAL_TENANT_SCHEDULER_JOB_REAL_AGENT | Query the agent table for job information |
| MySQL-compatible mode | oceanbase.DBA_SCHEDULER_JOBS | Query job information |
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 syntaxes are 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');
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.
| Mode | View name | Description |
|---|---|---|
| Oracle-compatible mode | GV$DML_STATS, V$DML_STATS | Query the DML STATS of tables recorded in the virtual table |
| Oracle-compatible mode | sys.ALL_VIRTUAL_MONITOR_MODIFIED_REAL_AGENT | Query the DML STATS of tables recorded in the agent table |
| Oracle-compatible mode | ALL_TAB_MODIFICATIONS, DBA_TAB_MODIFICATIONS, USER_TAB_MODIFICATIONS | Query all DML STATS in the table |
| MySQL-compatible mode | oceanbase.GV$DML_STATS, oceanbase.V$DML_STATS | Query the DML STATS of tables recorded in the virtual table |
| MySQL-compatible mode | oceanbase.DBA_TAB_MODIFICATIONS | Query all DML STATS in the table |
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 a preference to adjust the threshold. For more information, see Manage statistics preferences.
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 statistics, statistics are collected automatically.
If the table has global 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 statistics, collect statistics automatically in an incremental way.
If the table has global statistics and the statistics have expired, all statistics are collected automatically.
If the table has global statistics, and the statistics on only some partitions have expired, the system automatically collects the statistics on partitions with expired statistics and deduces the global statistics in an incremental way.
For more information about statistics collection and update strategies for partitioned tables, and detailed examples, see Strategies for collecting and updating statistics for partitioned tables.