Statistics

Statistics update mechanism

• Threshold-triggered update: Asksychronous statistics update is triggered when table data changes exceed a certain percentage (by default, data volume changes exceed 10 times).

• Partitioned table support: OceanBase supports partition-level statistics update and management.

Optimization strategies for statistics in AP scenarios

1. Customized collection strategies

   • Selective collection: Configure separate collection tasks for core query tables or key columns in AP scenarios.
   • Partition priority: Prioritize updating partitions with the highest usage frequency or the greatest changes.

2. Configure parallelism

   • When collecting statistics for ultra-large tables, configure the collection parallelism appropriately.

3. Dynamically adjust the update frequency

   • Flexibly configure the statistics update frequency based on the table data update pattern to avoid unnecessary overhead.

Scenario examples

Adjust the statistics collection window

By default, the OceanBase optimizer uses maintenance windows to perform daily automatic statistics collection, ensuring statistics are updated iteratively. 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.

You need to configure the maintenance windows appropriately based on your business requirements. For example, if a maintenance window coincides with business peak hours, you can adjust its start time or disable statistics collection on specific days. If your business environment contains a large number of tables or many ultra-large tables, you can adjust the collection duration of the maintenance windows.

The following are some configuration examples.

-- Disable Automatic Monday Stat Collection
CALL DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');

-- Enable Monday Automatic Statistics Collection
CALL DBMS_SCHEDULER.ENABLE('MONDAY_WINDOW');

-- 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');

-- Set the duration for automatic statistics collection on Monday to 6 hours.
-- 6 hours <=> 6 * 60 * 60 * 1000 * 1000 <=> 21600000000 us
CALL DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW', 'JOB_ACTION', 'DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(21600000000)');

Statistics collection strategy for ultra-large tables

In scenarios with ultra-large tables, the optimizer's default statistics collection strategy may not complete collecting all table statistics within a single maintenance window. Therefore, it is necessary to set an appropriate collection strategy for ultra-large tables. When collecting statistics for ultra-large tables, the main time-consuming aspects are:

• The table has a large volume of data, requiring a full-table scan for collection, which is time-consuming.
• Histogram collection involves complex calculations, adding additional time cost.
• For large partitioned tables, statistics and histograms are collected by default for subpartitions, partitions, and the entire table, resulting in a cost of 3 * (cost(full-table scan) + cost(histogram)).

Based on these time-consuming points, optimizations can be made according to the actual situation of the table and related query patterns. Recommendations are as follows:

• Set an appropriate default collection parallelism. Note that after setting the parallelism, you need to schedule related automatic collection tasks during off-peak business hours to avoid impacting operations. It is recommended to keep the parallelism within 8. You can set it using the following method.

  -- The business tenants of Oracle or MySQL are the same:
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'degree', '8');
  

• Set the default histogram collection method for columns. Consider disabling histogram collection for columns with evenly distributed data.

  -- Same business tenant for Oracle or MySQL
  -- 1. If the data distribution is uniform across all columns of the table, you can set all columns not to collect histograms as follows:
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'method_opt', 'for all columns size 1');
  
  -- 2. If only a very few columns in the table have uneven data distribution and histograms need to be collected for those columns, while histograms are not needed for others, you can set it as follows (c1, c2 collect histograms, c3, c4, c5 do not collect histograms):
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'method_opt', 'for columns c1 size 254, c2 size 254, c3 size 1, c4 size 1, c5 size 1');
  

• Set the default collection granularity for partitioned tables. For some partitioned tables, such as hash partitions or key partitions, consider collecting only global statistics, or you can also set the collection method to derive global statistics from partitions.

  -- Same business tenant for Oracle or MySQL
  -- 1. Set to collect global statistics only.
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'granularity', 'GLOBAL');
  
  -- 2. Set the partition to derive the global collection method
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'granularity', 'APPROX_GLOBAL AND PARTITION');
  

• Use caution when setting sampling for large table statistics collection. When sampling is enabled for large tables, the histogram sample size in earlier versions can become large, which may counteract the intended effect. Setting sampling is suitable only for scenarios where only basic statistics are collected and histograms are not collected.

  -- Oracle or MySQL business tenants are the same, such as the deleted granularity.
  -- 1. Set all columns to not collect histograms:
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'method_opt', 'for all columns size 1');
  
  -- 2. Set the sampling ratio to 10%.
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'estimate_percent', '10');
  

Additionally, if you need to clear/delete a set default collection strategy, you only need to specify the attribute to clear {attribute}, using the following method.

-- Oracle or MySQL business tenants are the same, such as deletion granularity.
CALL DBMS_STATS.delete_table_prefs('database_name', 'table_name', 'granularity');

If you have set the relevant collection strategies and need to check whether they were successfully configured, you can use the following method to query.

-- The same as that of an Oracle or MySQL business tenant, such as obtaining a specified degree of parallelism
SELECT DBMS_STATS.GET_PREFS('degree', 'database_name','table_name') from dual;

Besides the methods mentioned above, you can also consider locking the relevant statistics after manually collecting them for a large table. Note that once a table's statistics are locked, automatic collection will not update them. This is suitable for scenarios where data characteristics do not change significantly and data values are not sensitive. If you need to recollect locked statistics, you must unlock them first.

-- The same as that of Oracle or MySQL business tenants, locking table statistics
CALL DBMS_STATS.LOCK_TABLE_PREFS('database_name', 'table_name');

-- The same as that for unlocking table statistics in an Oracle or MySQL business tenant
CALL DBMS_STATS.UNLOCK_TABLE_PREFS('database_name', 'table_name');

References

For detailed information and usage instructions on statistics, see the following topics:

• Statistics include Table Level Statistics and Column Level Statistics. For more information about statistics types, see Overview of statistics.

• The OceanBase Database optimizer supports both manual and automatic statistics collection. For detailed information and operational guidance on statistics collection, see Overview of statistics collection methods.

• For detailed operational guidance on statistics management, see the Statistics management section.

• To understand how statistics are used through a simple example, see Sample usage.