The OceanBase Database optimizer automatically collects statistics daily by using the MAINTENANCE WINDOW feature implemented through the DBMS_SCHEDULER system package. This ensures the continuous iteration and update of statistics.
MAINTENANCE WINDOW
OceanBase Database defines MAINTENANCE WINDOW as a weekly schedule with 7 automatic statistics collection tasks from Monday to Sunday. Each task starts at 22:00 and has a maximum collection duration of 4 hours, as shown in the table below.
| 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 |
Note
- For OceanBase Database V4.3.5, starting from V4.3.5 BP1, the default start time and maximum collection duration for the
SATURDAY_WINDOWandSUNDAY_WINDOWtasks have been changed from default start time 6:00 and maximum collection duration 20 hours to default start time 22:00 and maximum collection duration 4 hours. - For OceanBase Database V4.2.5, starting from V4.2.5 BP2, the default start time and maximum collection duration for the
SATURDAY_WINDOWandSUNDAY_WINDOWtasks have been changed from default start time 6:00 and maximum collection duration 20 hours to default start time 22:00 and maximum collection duration 4 hours.
Query MAINTENANCE WINDOW information
Currently, MAINTENANCE WINDOW in OceanBase Database is implemented using the DBMS_SCHEDULER system package. Therefore, you can also query the DBMS_SCHEDULER system package views to obtain information about MAINTENANCE WINDOW.
| Mode | View Name | Description |
|---|---|---|
| Oracle mode | DBA_SCHEDULER_JOBS | Query job information. |
| Oracle mode | sys.ALL_VIRTUAL_TENANT_SCHEDULER_JOB_REAL_AGENT | Query the proxy table of job information. |
| MySQL mode | oceanbase.DBA_SCHEDULER_JOBS | Query job information. |
Set MAINTENANCE WINDOW properties
Currently, MAINTENANCE WINDOW properties in OceanBase Database are also set using the DBMS_SCHEDULER system package. The supported property setting syntax is as follows:
Enable or disable automatic statistics collection tasks:
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 collection task:
DBMS_SCHEDULER.SET_ATTRIBUTE($window_name, 'NEXT_DATE', $next_time);Example: Set the start time of automatic statistics collection on Monday to 20:00.
CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'NEXT_DATE', '2022-09-12 20:00:00');
DML STATS
DML STATS records the number of insert, update, and delete operations performed on a table.
The DML STATS of a table plays a crucial role in automatic statistics collection. It determines whether statistics need to be collected again for the table. Since data information in tables in the database is not updated every day, statistics do not need to be collected again for tables with no data changes or data changes within the expected range. 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 and MySQL modes.
| Mode | View | Description |
|---|---|---|
| Oracle mode | Queries the DML STATS recorded in the virtual table | |
| Oracle mode | Queries all DML STATS in the table | |
| MySQL mode | Queries the DML STATS recorded in the virtual table | |
| MySQL mode | oceanbase.DBA_TAB_MODIFICATIONS | Queries all DML STATS in the table |
Automatic statistics collection strategy
In OceanBase Database, statistics for a table are considered expired if the number of incremental DML operations (the total number of insert, update, and delete operations performed between the last statistics collection and the current one) exceeds the set threshold. The default threshold is 10%. You can adjust the threshold using prefs. For more information, see Manage collection policy configuration items (prefs).
The automatic statistics collection strategy of the optimizer in OceanBase Database is as follows:
Automatic statistics collection strategy for system tables and non-partitioned user tables:
If the table does not have statistics at the
GLOBALlevel, automatic statistics collection is performed.If the table has statistics at the
GLOBALlevel, but the statistics are expired, automatic statistics collection is performed.
Otherwise, the optimizer in OceanBase Database does not automatically collect statistics.
Automatic statistics collection strategy for partitioned user tables in OceanBase Database:
If the table has no statistics, all statistics are automatically collected.
If the table has partition-level statistics but no
GLOBALlevel statistics, incremental automatic statistics collection is performed.If the table has
GLOBALlevel statistics, but the statistics are expired, all statistics are automatically collected.If the table has
GLOBALlevel statistics, but not all of them are expired, only the statistics for the expired partitions are automatically collected, and incremental derivation ofGLOBALlevel statistics is performed.