Automatic statistics collection

In addition to manual statistics collection, the OceanBase Database optimizer currently performs daily automatic statistics collection through the MAINTENANCE WINDOW to ensure statistics are updated iteratively. Similar to native Oracle, the OceanBase Database optimizer defines seven automatic statistics collection tasks from Monday to Sunday. The default 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 the execution status of automatic statistics collection

The OceanBase Database optimizer provides the following views for querying the execution status of automatic statistics collection.

For the DBA_SCHEDULER_JOBS view, pay attention to the following fields:

• JOB_NAME: The name of the maintenance window task.
• LAST_START_DATE: The time when the last maintenance window task was executed.
• NEXT_RUN_DATE: The time when the next maintenance window task will be executed.
• ENABLED: Whether the current maintenance window task is enabled.
• FAILURE_COUNT: The number of failures of the maintenance window task. If it is non-zero, contact OceanBase Technical Support for troubleshooting.
• MAX_RUN_DURATION: The maximum duration of a maintenance window task. The default unit is seconds.

Modify the attributes of automatic statistics collection

Considering that users need to modify attributes such as the start time and collection duration of automatic statistics collection according to their business characteristics, the OceanBase Database optimizer provides the following methods to modify the attributes of automatic statistics collection:

1. Disable automatic statistics collection.

   OceanBase Database can use the system package DBMS_SCHEDULER.DISABLE to disable automatic statistics collection.

   Example:

   MySQL-compatible mode

   Oracle-compatible mode

   • Disable automatic statistics collection on Monday.

     CALL DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');
     

   • Disable automatic statistics collection on Tuesday.

     CALL DBMS_SCHEDULER.DISABLE('TUESDAY_WINDOW');
     

   • Disable automatic statistics collection on Wednesday.

     CALL DBMS_SCHEDULER.DISABLE('WEDNESDAY_WINDOW');
     

   • Disable automatic statistics collection on Thursday.

     CALL DBMS_SCHEDULER.DISABLE('THURSDAY_WINDOW');
     

   • Disable automatic statistics collection on Friday.

     CALL DBMS_SCHEDULER.DISABLE('FRIDAY_WINDOW');
     

   • Disable automatic statistics collection on Saturday.

     CALL DBMS_SCHEDULER.DISABLE('SATURDAY_WINDOW');
     

   • Automatically collect statistics on Sundays is prohibited.

     CALL DBMS_SCHEDULER.DISABLE('SUNDAY_WINDOW');
     

   Notice

   To execute the following statements in a user tenant of Oracle-compatible mode, you must use the corresponding sys user.

   • Automatically collect statistics on Mondays is prohibited.

     CALL DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');
     

   • Automatically collect statistics on Tuesdays is prohibited.

     CALL DBMS_SCHEDULER.DISABLE('TUESDAY_WINDOW');
     

   • Automatically collect statistics on Wednesdays is prohibited.

     CALL DBMS_SCHEDULER.DISABLE('WEDNESDAY_WINDOW');
     

   • Automatically collect statistics on Thursdays is prohibited.

     CALL DBMS_SCHEDULER.DISABLE('THURSDAY_WINDOW');
     

   • Automatically collect statistics on Fridays is prohibited.

     CALL DBMS_SCHEDULER.DISABLE('FRIDAY_WINDOW');
     

   • Automatically collect statistics on Saturdays is prohibited.

     CALL DBMS_SCHEDULER.DISABLE('SATURDAY_WINDOW');
     

   • Automatically collect statistics on Sundays is prohibited.

     CALL DBMS_SCHEDULER.DISABLE('SUNDAY_WINDOW');
     

2. Enable automatic statistical task collection.

   OceanBase Database can use the system package DBMS_SCHEDULER.ENABLE to enable automatic statistical task collection.

   Example:

   MySQL-compatible mode

   Oracle-compatible mode

   • Enable automatic statistics collection on Monday.

     CALL DBMS_SCHEDULER.ENABLE('MONDAY_WINDOW');
     

   • Enable automatic statistics collection on Tuesday.

     CALL DBMS_SCHEDULER.ENABLE('TUESDAY_WINDOW');
     

   • Enable automatic statistics collection on Wednesday.

     CALL DBMS_SCHEDULER.ENABLE('WEDNESDAY_WINDOW');
     

   • Enable automatic statistics collection on Thursday.

     CALL DBMS_SCHEDULER.ENABLE('THURSDAY_WINDOW');
     

   • Enable automatic statistics collection on Friday.

     CALL DBMS_SCHEDULER.ENABLE('FRIDAY_WINDOW');
     

   • Enable automatic statistics collection on Saturday.

     CALL DBMS_SCHEDULER.ENABLE('SATURDAY_WINDOW');
     

   • Enable automatic statistics collection on Sunday.

     CALL DBMS_SCHEDULER.ENABLE('SUNDAY_WINDOW');
     

   Notice

   To execute the following statements in a user tenant of Oracle-compatible mode, you must use the corresponding sys user.

   • Enable automatic statistics collection on Monday.

     CALL DBMS_SCHEDULER.ENABLE('MONDAY_WINDOW');
     

   • Enable automatic statistics collection on Tuesday.

     CALL DBMS_SCHEDULER.ENABLE('TUESDAY_WINDOW');
     

   • Enable automatic statistics collection on Wednesday.

     CALL DBMS_SCHEDULER.ENABLE('WEDNESDAY_WINDOW');
     

   • Enable automatic statistics collection on Thursday.

     CALL DBMS_SCHEDULER.ENABLE('THURSDAY_WINDOW');
     

   • Enable automatic statistics collection on Friday.

     CALL DBMS_SCHEDULER.ENABLE('FRIDAY_WINDOW');
     

   • Enable automatic statistics collection on Saturday.

     CALL DBMS_SCHEDULER.ENABLE('SATURDAY_WINDOW');
     

   • Enable automatic statistics collection on Sunday.

     CALL DBMS_SCHEDULER.ENABLE('SUNDAY_WINDOW');
     

3. Adjust the scheduling time for automatic statistics collection.

   Automatic collection tasks in OceanBase Database are implemented based on DBMS_SCHEDULER. Therefore, you can use DBMS_SCHEDULER.SET_ATTRIBUTE to adjust the scheduling time for automatic statistics collection.

   Notice

   When setting the NEXT_DATE for each maintenance window, the specified date must match the day of the week represented by the window name. For example, the NEXT_DATE for MONDAY_WINDOW must be set to a Monday date, and that for FRIDAY_WINDOW must be set to a Friday date, and so on.

   Example:

   MySQL-compatible mode

   Oracle-compatible mode

   Suppose the current time is 11:00 AM on Thursday, March 7, 2024. You need to adjust it to start automatic statistics collection at 2:00 AM on Friday:

   CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'NEXT_DATE', '2024-03-11 02:00:00');
   CALL DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW', 'NEXT_DATE', '2024-03-12 02:00:00');
   CALL DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW', 'NEXT_DATE', '2024-03-13 02:00:00');
   CALL DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW', 'NEXT_DATE', '2024-03-14 02:00:00');
   CALL DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW', 'NEXT_DATE', '2024-03-08 02:00:00');
   CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW', 'NEXT_DATE', '2024-03-09 02:00:00');
   CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW', 'NEXT_DATE', '2024-03-10 02:00:00');
   

   Notice

   When executing the following statements in an Oracle-compatible mode user tenant, you must use the corresponding sys user.

   1. Set the date format.

      SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
      

   2. Set the timestamp format.

      SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF';
      

   3. Set the timestamp format with a time zone.

      SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
      

   4. Suppose it is currently Thursday, March 7, 2024, at 11:00 AM, and you need to adjust it to start automatic statistics collection from 2:00 AM on Friday:

      CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'NEXT_DATE', '2024-03-11 02:00:00');
      CALL DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW', 'NEXT_DATE', '2024-03-12 02:00:00');
      CALL DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW', 'NEXT_DATE', '2024-03-13 02:00:00');
      CALL DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW', 'NEXT_DATE', '2024-03-14 02:00:00');
      CALL DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW', 'NEXT_DATE', '2024-03-08 02:00:00');
      CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW', 'NEXT_DATE', '2024-03-09 02:00:00');
      CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW', 'NEXT_DATE', '2024-03-10 02:00:00');
      

4. Adjust the maximum collection duration for automatic statistics collection.

   To extend or shorten the statistics collection time for a specific day, you need to adjust both the window duration and the time limit of the collection task. The two items use different units and must be converted consistently.

   Item	Interface	Unit	Description
   Window duration	DBMS_SCHEDULER.SET_ATTRIBUTE(..., 'duration', value)	seconds	The maximum duration that the window allows a task to run, which is the window opening duration.
   Time limit of the collection task	The parameter of GATHER_DATABASE_STATS_JOB_PROC(duration) in DBMS_SCHEDULER.SET_ATTRIBUTE(..., 'JOB_ACTION', '...')	microseconds	The maximum duration that this automatic collection can run within the whole window.

   Therefore, if you want the window on a specific day to last 8 hours, set duration to 8 × 3600 = 28800 (seconds) and the parameter of GATHER_DATABASE_STATS_JOB_PROC to 8 × 3600 × 1000000 = 28800000000 (microseconds).

   Example:

   The following example adjusts the Wednesday window to 8 hours.

   MySQL-compatible mode

   Oracle-compatible mode

   1. Set the window duration (unit: seconds). For example, 8 hours = 28800 seconds:

      CALL DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW', 'duration', 28800);
      

   2. Set the action of the statistics collection task in this window (unit of the time limit: microseconds). 8 hours = 28800000000 microseconds:

      CALL DBMS_SCHEDULER.SET_ATTRIBUTE(
        'WEDNESDAY_WINDOW',
        'JOB_ACTION',
        'DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(28800000000)'
      );
      

   For other days, replace WEDNESDAY_WINDOW with the corresponding window name.

   Notice

   To execute the following statements in a user tenant of Oracle-compatible mode, you must use the corresponding sys user.

   1. Set the window duration (unit: seconds). For example, 8 hours = 28800 seconds:

      CALL DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW', 'duration', 28800);
      

   2. Set the action of the statistics collection task in this window (unit of the time limit: microseconds). 8 hours = 28800000000 microseconds:

      CALL DBMS_SCHEDULER.SET_ATTRIBUTE(
        'WEDNESDAY_WINDOW',
        'JOB_ACTION',
        'DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(28800000000)'
      );
      

   For other days, replace WEDNESDAY_WINDOW with the corresponding window name.

   The window names for the seven days are as follows:

   Day	Window name
   Monday	MONDAY_WINDOW
   Tuesday	TUESDAY_WINDOW
   Wednesday	WEDNESDAY_WINDOW
   Thursday	THURSDAY_WINDOW
   Friday	FRIDAY_WINDOW
   Saturday	SATURDAY_WINDOW
   Sunday	SUNDAY_WINDOW

   The following table provides conversion references for common durations:

   Duration	duration (seconds)	Parameter of GATHER_DATABASE_STATS_JOB_PROC (microseconds)
   4 hours	14400	14400000000
   6 hours	21600	21600000000
   8 hours	28800	28800000000
   12 hours	43200	43200000000
   20 hours	72000	72000000000

   Formula: seconds = hours × 3600; microseconds = seconds × 1000000.

   Notice

   Do not mix the units: `SET_ATTRIBUTE(..., 'duration', ...)` uses seconds, while `GATHER_DATABASE_STATS_JOB_PROC(...)` uses microseconds. Convert the two based on the same target duration.

   After the adjustment, you can run the following SQL statements to view the configuration of each window:

   MySQL-compatible mode

   Oracle-compatible mode

   SELECT JOB_NAME, ENABLED, REPEAT_INTERVAL, MAX_RUN_DURATION
   FROM oceanbase.DBA_SCHEDULER_JOBS
   WHERE JOB_NAME IN (
     'MONDAY_WINDOW', 'TUESDAY_WINDOW', 'WEDNESDAY_WINDOW',
     'THURSDAY_WINDOW', 'FRIDAY_WINDOW', 'SATURDAY_WINDOW', 'SUNDAY_WINDOW'
   );
   

   SELECT JOB_NAME, ENABLED, REPEAT_INTERVAL, MAX_RUN_DURATION
   FROM DBA_SCHEDULER_JOBS
   WHERE JOB_NAME IN (
     'MONDAY_WINDOW', 'TUESDAY_WINDOW', 'WEDNESDAY_WINDOW',
     'THURSDAY_WINDOW', 'FRIDAY_WINDOW', 'SATURDAY_WINDOW', 'SUNDAY_WINDOW'
   );
   

   Here, MAX_RUN_DURATION is the current window duration in seconds.

How automatic statistics collection works

The preceding topic describes the MAINTENANCE WINDOW related to automatic statistics collection. So, how does automatic statistics collection work after a task is initiated? The following figure illustrates the mechanism of automatic statistics collection.

In the above process of automatic statistics collection, whether a table's statistics are considered outdated is primarily determined by the percentage of insertions, deletions, and modifications on that table from the time of the last statistics collection to the current collection. The default threshold is 10%. Note that this change ratio is calculated at the partition level. For example, if the percentage of inserts, deletions, or modifications in some partitions of a partitioned table exceeds 10%, statistics for those partitions will be collected again. Of course, the default change ratio is configurable. You can adjust it based on your actual business needs by setting perfs. For details, see the Configure the statistics collection strategy section. Additionally, the OceanBase Database optimizer provides relevant views to query the number of inserts, deletions, and modifications for a table, as follows: