Statistics

In a database, the optimizer attempts to generate an optimal execution plan for each input SQL query. Generating an optimal plan often requires real-time and effective statistics and accurate row count estimates. Statistics refer to optimizer statistics, which are a collection of data describing table and column information in the database. They are a critical component for the cost-based model to select the optimal execution plan. The optimizer cost model relies on statistics from tables, columns, predicates, and other objects involved in the query to select and optimize execution plans. Accurate and effective statistics help the optimizer choose the optimal execution plan.

OceanBase Database supports various types of statistics, including table-level, column-level, and histogram statistics. It also supports manual, automatic, online, and asynchronous statistics collection. In most systems, users typically do not need to worry about statistics issues because the optimizer regularly performs tasks to collect statistics for tables that need updates. However, in AP scenarios, there may be extremely large tables or tables that undergo large-scale updates and require real-time queries. In such cases, the default statistics collection strategy may not be able to collect statistics in a timely manner, which can affect the generation of the execution plan. The following sections provide targeted guidance on how to collect statistics in AP scenarios.

Overview of statistics

Classification of statistics

Statistics can be mainly classified into the following categories:

• Table-level (including index tables) statistics: Include row count, macroblock count, microblock count, average row length, etc., used to estimate the scan cost of a table.

• Column-level statistics

  • Column value distribution: Maximum value, minimum value, average column length, and number of distinct values (NDV).
  • Data skew: Describes the distribution of data through a frequency distribution (Histogram).
  • Null value rate: Helps the optimizer handle queries related to NULL values.

OceanBase supported statistics collection methods

• Manual statistics collection: Users can trigger statistics collection by using SQL statements. This method is suitable for large tables or specific query optimization. Users can also specify collection strategies, such as the degree of parallelism, collection granularity, and number of histogram buckets.

• Automatic statistics collection: The optimizer runs scheduled collection jobs through maintenance windows and analyzes whether table statistics need to be updated on a daily basis.

• Online statistics collection: In scenarios such as batch import, PDML, and CREATE TABLE ... AS, statistics can be collected online using the GATHER_OPTIMIZER_STATISTICS hint and the system variable _optimizer_gather_stats_on_load (which is enabled by default). The APPEND hint of the direct load feature can also be used for online statistics collection.

• Asynchronous statistics collection: The system periodically checks data changes in tables or partitions. If the data has changed significantly since the last statistics collection, the system adds the table or partition as a candidate for asynchronous statistics collection.

Statistics update mechanism

• Automatic statistics collection: The system determines whether statistics are stale based on the cumulative number of rows affected by insert, update, and delete operations since the last statistics collection. The default staleness threshold is 10% of the number of rows recorded in the last statistics. Statistics that exceed the threshold participate in automatic statistics collection during a maintenance window.

• Asynchronous statistics collection: The system uses a threshold independent of the maintenance-window threshold to determine whether data has changed significantly. The change ratio is the cumulative number of rows affected by insert, update, and delete operations since the last statistics collection divided by the number of rows recorded in the last statistics. By default, a table or partition becomes a candidate for asynchronous statistics collection when the change ratio is greater than 2. Becoming a candidate does not mean that statistics collection is completed immediately.

  OceanBase Database supports asynchronous statistics collection starting with V4.2.4. Starting with V4.3.5 BP2, asynchronous statistics collection uses the preceding data change ratio to determine candidates.

• Partition-level determination for asynchronous collection: For a partitioned table, the system independently determines whether each partition meets the candidate condition. For a subpartitioned table, the system makes the determination for each subpartition.

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_STATS('database_name', 'table_name');

-- The same as that for unlocking table statistics in an Oracle or MySQL business tenant
CALL DBMS_STATS.UNLOCK_TABLE_STATS('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.

• Asynchronous statistics collection

• 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.