Statistics

In a database, the optimizer attempts to generate an optimal execution plan for each SQL query. This process often requires up-to-date, accurate statistics and precise row-count estimates. Optimizer statistics describe the tables and columns in a database and are a critical input to the cost model. The cost model uses statistics about the tables, columns, predicates, and other objects involved in a query to select and optimize an execution plan. Accurate statistics help the optimizer choose the optimal plan.

OceanBase Database supports table-level statistics, column-level statistics, and histograms, as well as manual, automatic, online, and asynchronous statistics collection. In most systems, users do not need to manage statistics directly because the optimizer periodically collects statistics for tables that need to be updated. However, in AP scenarios, statistics might not be collected promptly for very large tables or for tables that must support real-time queries after large-scale updates. This can affect execution plan generation. The following sections describe statistics collection strategies for common AP scenarios.

Statistics overview

Classification of statistics

Statistics can be categorized as follows:

• Table-level statistics (including index table statistics): These include the number of rows, the numbers of macroblocks and microblocks, and the average row length. They are used to estimate the cost of scanning a table.

• Column-level statistics

  • Value distribution: Maximum value, minimum value, average column length, and number of distinct values (NDV).
  • Data skew: Described by a frequency distribution (histogram).
  • Null value rate: Helps the optimizer handle queries involving NULL values.

Statistics collection methods supported by OceanBase Database

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

• Automatic statistics collection: The optimizer runs scheduled collection jobs during maintenance windows. By default, it checks daily whether the statistics of a table need to be updated.

• 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 statistics were last collected, the system adds the table or partition to the candidate set 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 statistics were last collected, divided by the number of rows recorded in the previous 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 are collected immediately.

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

• Partition-level determination for asynchronous collection: For a partitioned table, the system evaluates each partition independently against the candidate condition. For a subpartitioned table, it evaluates each subpartition independently.

Optimization strategies for statistics in AP scenarios

1. Customized collection strategy

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

2. Configure parallelism

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

3. Dynamically adjust update frequency

   • Based on the update pattern of the table data, flexibly configure the statistics update frequency to avoid unnecessary overhead.

Scenario examples

Adjust the statistics collection window

By default, the OceanBase Database optimizer uses maintenance windows to collect statistics automatically every day, ensuring that statistics are updated regularly. The default start time for each daily task is 22:00, and the maximum collection duration is 4 hours, as shown in the following table.

Configure maintenance windows based on your business requirements. For example, if a maintenance window overlaps with peak business hours, you can adjust its start time or disable statistics collection on specific dates. If your environment contains many tables or many very large tables, you can also adjust the duration of the maintenance window.

The following examples show how to configure the maintenance window.

-- Disable automatic statistics collection on Mondays.
CALL DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');

-- Enable automatic statistics collection on Mondays.
CALL DBMS_SCHEDULER.ENABLE('MONDAY_WINDOW');

-- Set the start time of automatic statistics collection on Mondays to 8:00 PM.
CALL DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW', 'NEXT_DATE', '2022-09-12 20:00:00');

-- Set the collection duration of automatic statistics collection on Mondays 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 strategies for very large tables

For very large tables, the default optimizer strategy might not finish collecting statistics within a single maintenance window. Therefore, you must configure an appropriate collection strategy. The following operations account for most of the collection time:

• A full table scan is required because the table contains a large amount of data.
• Histogram collection involves complex calculations and incurs additional overhead.
• For a large subpartitioned table, the default strategy collects statistics and histograms at the subpartition, partition, and global levels. The resulting cost is approximately 3 × (full table scan cost + histogram collection cost).

Optimize the statistics collection strategy based on the characteristics of the table and related queries. We recommend that you take the following actions:

• Set an appropriate default degree of parallelism. Schedule the related automatic collection tasks to run during off-peak hours to minimize their impact on your business. We recommend that you set the degree of parallelism to 8 or less. The following example shows how to configure it:

  -- This statement applies to both Oracle- and MySQL-compatible user tenants.
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'degree', '8');
  

• Configure the default histogram collection method for columns. Consider disabling histogram collection for columns with a uniform data distribution.

  -- These statements apply to both Oracle- and MySQL-compatible user tenants.
  -- 1. If data is uniformly distributed in every column, disable histogram collection for all columns.
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'method_opt', 'for all columns size 1');
  
  -- 2. If data is nonuniform in only a few columns, collect histograms for those columns and disable histogram collection for the others. In this example, histograms are collected for c1 and c2, but not for c3, c4, or c5.
  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');
  

• Configure the default collection granularity for partitioned tables. For hash- or key-partitioned tables, consider collecting only global statistics or deriving global statistics from partition-level statistics.

  -- These statements apply to both Oracle- and MySQL-compatible user tenants.
  -- 1. Collect only global statistics.
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'granularity', 'GLOBAL');
  
  -- 2. Derive global statistics from partition-level statistics.
  CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'granularity', 'APPROX_GLOBAL AND PARTITION');
  

• Use sampling with caution when collecting statistics for large tables. In earlier database versions, sampling a large table can also substantially increase the number of histogram samples and have the opposite of the intended effect. Sampling is suitable only when you need to collect basic statistics without histograms.

  -- These statements apply to both Oracle- and MySQL-compatible user tenants.
  -- 1. Disable histogram collection for all columns.
  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');
  

To clear a default collection option, specify the attribute to delete, as shown in the following example:

-- This statement applies to both Oracle- and MySQL-compatible user tenants. This example deletes the granularity attribute.
CALL DBMS_STATS.delete_table_prefs('database_name', 'table_name', 'granularity');

After configuring a collection strategy, you can query its settings as follows:

-- This statement applies to both Oracle- and MySQL-compatible user tenants. This example queries the degree of parallelism.
SELECT DBMS_STATS.GET_PREFS('degree', 'database_name','table_name') from dual;

You can also manually collect and then lock statistics for a large table. Automatic collection does not update locked statistics. This approach is suitable when data characteristics change only slightly and queries are not sensitive to changes in data values. You must unlock the statistics before collecting them again.

-- This statement applies to both Oracle- and MySQL-compatible user tenants. Lock the statistics of a table.
CALL DBMS_STATS.LOCK_TABLE_STATS('database_name', 'table_name');

-- This statement applies to both Oracle- and MySQL-compatible user tenants. Unlock the statistics of a table.
CALL DBMS_STATS.UNLOCK_TABLE_STATS('database_name', 'table_name');

References

For more information about statistics, see the following topics:

• Statistics include table-level and column-level statistics. For more information about the types of statistics, see Statistics overview.

• OceanBase Database supports manual, automatic, online, and asynchronous statistics collection. For details and instructions, see Overview of statistics collection methods.

• For the trigger conditions and partition-level evaluation granularity of asynchronous statistics collection, see Asynchronous statistics collection.

• For instructions on managing statistics, see Manage statistics.

• For a simple example of using statistics, see Example.