In a database, the optimizer attempts to generate an optimal execution plan for each SQL query. This usually requires up-to-date, effective statistics and accurate row count estimates. Statistics, also called optimizer statistics, describe the tables and columns in the database and are a critical input for the cost model when selecting an execution plan. Based on the statistics of the tables, columns, and predicates involved in a query, the cost model selects and optimizes execution plans. Accurate and effective statistics help the optimizer choose the optimal plan.
OceanBase AI Database supports the collection and management of statistics for internal tables (local tables) and external catalog tables (such as external tables in Hive or Iceberg format). It covers basic table-level and column-level statistics, as well as histograms, and provides a variety of strategies, including scheduled automatic collection, manual collection, online collection, and asynchronous automatic collection.
Internal table statistics
In most systems, users typically do not need to worry about statistics-specific issues, because the optimizer periodically runs tasks to collect statistics for tables that need updating. However, in AP scenarios, there may be some ultra-large tables or tables that require real-time queries after large-scale updates. In such cases, the default statistics collection strategy may not be able to complete the collection in a timely manner, which can affect the generation of execution plans. The following sections describe how to collect statistics in some AP scenarios.
Types of statistics
Statistics are classified into the following types:
Table-level (including index tables) statistics: These include row count, macroblock count, microblock count, and average row length, and are 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.
Statistics collection methods supported by OceanBase AI Database
Automatic collection: By default, the optimizer's scheduled job checks every day whether table statistics need to be updated.
Manual collection: You can trigger statistics collection by using SQL commands. This method is suitable for ultra-large tables or specific query optimization. When collecting statistics manually, you can also specify collection strategies such as the degree of parallelism, collection granularity, and number of histogram buckets.
Online collection: In scenarios such as batch import, PDML, and
CREATE TABLE ... AS, statistics can be collected online by using theGATHER_OPTIMIZER_STATISTICShint and the system variable_optimizer_gather_stats_on_load(enabled by default). TheAPPENDhint of the direct load feature can also be used for online statistics collection.
Statistics update mechanism
Threshold-triggered update: An asynchronous statistics update is triggered when changes to table data exceed a certain proportion (by default, when the data volume changes by a factor of more than 10).
Support for partitioned tables: OceanBase AI Database supports partition-level statistics update and management.
Optimization strategies for statistics in AP scenarios
Customized collection strategies
- Selective collection: Configure separate collection tasks for the core query tables or key columns in AP scenarios.
- Partition priority: Prioritize updating partitions with the highest usage frequency or the greatest changes.
Configure parallelism
- When collecting statistics for ultra-large tables, configure the collection parallelism appropriately.
Dynamically adjust the update frequency
- Flexibly configure the statistics update frequency based on the table data's update pattern to avoid unnecessary overhead.
Scenario examples
Adjust the statistics collection window
By default, the OceanBase AI Database optimizer uses maintenance windows for daily automatic statistics collection to ensure 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.
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 |
Configure maintenance windows appropriately based on the actual business situation. For example, if a maintenance window coincides with business peak hours, you can adjust its start time or disable statistics collection on specific days. When the business environment contains many tables or many ultra-large tables, you can adjust the collection duration of the maintenance windows.
The following are some configuration examples.
-- Disable automatic statistics collection on Monday
CALL DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW');
-- Enable automatic statistics collection on Monday
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 be able to collect all table statistics within a single maintenance window. Therefore, it is necessary to set an appropriate collection strategy for ultra-large tables. The main time-consuming aspects of collecting statistics for ultra-large tables are:
- The table contains a large amount 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 aspects, you can optimize according to the table's actual situation and related query patterns. The recommendations are as follows:
Set an appropriate default collection parallelism. Note that after setting the parallelism, related automatic collection tasks should be scheduled 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.
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.
-- 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 few columns in the table have uneven data distribution and histograms need to be collected for those columns, while no histograms are needed for the others, you can set it up as follows (collect histograms for c1 and c2, but not for c3, c4, and 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');Set the default collection granularity for partitioned tables. For some partitioned tables, such as hash or key partitioned tables, consider collecting only global statistics, or set the collection method to derive global statistics from partitions.
-- 1. Set to collect global statistics only CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'granularity', 'GLOBAL'); -- 2. Set the collection method to derive GLOBAL statistics from partitions CALL DBMS_STATS.SET_TABLE_PREFS('database_name', 'table_name', 'granularity', 'APPROX_GLOBAL AND PARTITION');Use caution when setting sampling-based statistics collection for large tables. When sampling is enabled for large tables, the histogram sample size also becomes large in earlier versions, which can have the opposite effect. Setting sampling-based collection is only suitable for scenarios where only basic statistics are collected and histograms are not collected.
-- 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');
To clear a collection strategy that you have configured, specify the attribute to clear, {attribute}, as follows.
CALL DBMS_STATS.delete_table_prefs('database_name', 'table_name', 'granularity');
If you have configured collection strategies and need to check whether they were set successfully, run the following query.
SELECT DBMS_STATS.GET_PREFS('degree', 'database_name','table_name') from dual;
Besides the preceding methods, you can also consider locking the relevant statistics after manually collecting statistics for large tables. 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.
CALL DBMS_STATS.LOCK_TABLE_STATS('database_name', 'table_name');
CALL DBMS_STATS.UNLOCK_TABLE_STATS('database_name', 'table_name');
HMS Catalog external table statistics collection strategy
OceanBase AI Database extends the DBMS_STATS package by adding interfaces such as GATHER_CATALOG_TABLE_STATS, and supports the collection of table-level and column-level statistics for external tables (in Hive or Iceberg format) in HMS Catalog. The optimizer can use these statistics to select better execution plans for external table queries (such as JOIN order, degree of parallelism, etc.), significantly improving external table query performance.
Feature introduction
Types of statistics
The following statistics are supported:
Level |
Contents |
Note |
|---|---|---|
| Table-level | Rows, average row length, files, data size | Supported at the global and partition levels |
| Column-level | NDV, NULL count, maximum/minimum value, and average column length | Supported at the global and partition levels |
Lock statistics
You can lock the statistics of a specified table or partition to prevent them from being overwritten by later collection operations. This is typically used to stabilize execution plans in stable environments.
Supported formats
- Hive Parquet/ORC/CSV tables
- Iceberg tables
Limitations
- Currently, only Hive format tables (Parquet/ORC/CSV) in HMS Catalog are supported. Iceberg format supports statistics collection but does not currently support partition enumeration.
- Statistical information is stored in internal system tables of OceanBase AI Database and is not written back to HMS.
- CDC/SCN snapshot read is not supported (external tables themselves do not support this feature).
- The collection operation is executed synchronously, which may take a long time for large tables. It is recommended to perform it during off-peak business hours.
Procedure
Step 1: Collect global statistics
Collect global (table-level + column-level) statistics for external tables in HMS Catalog.
Collect statistics for the lineitem table:
obclient> CALL DBMS_STATS.GATHER_CATALOG_TABLE_STATS(
'hive_catalog', -- catalog name
'test_oss', -- database name
'lineitem', -- table name
NULL, -- partition name, NULL indicates the entire table
100, -- sampling percentage; 100 indicates a full collection
'BLOCK', -- sampling method, BLOCK indicates block sampling
NULL, -- method_opt, NULL uses default
32 -- degree of parallelism
);
For more information about DBMS_STATS.GATHER_CATALOG_TABLE_STATS, see GATHER_CATALOG_TABLE_STATS.
Step 2: View the collection results
View table-level statistics.
obclient> SELECT * FROM oceanbase.DBA_OB_EXTERNAL_TAB_STATISTICS WHERE catalog_name = 'hive_catalog' AND database_name = 'test_oss' AND table_name = 'lineitem';For more information about
oceanbase.DBA_OB_EXTERNAL_TAB_STATISTICS, see oceanbase.DBA_OB_EXTERNAL_TAB_STATISTICS.View column-level statistics (global).
obclient> SELECT * FROM oceanbase.DBA_OB_EXTERNAL_TAB_COL_STATISTICS WHERE catalog_name = 'hive_catalog' AND database_name = 'test_oss' AND table_name = 'lineitem';For more information about
oceanbase.DBA_OB_EXTERNAL_TAB_COL_STATISTICS, see oceanbase.DBA_OB_EXTERNAL_TAB_COL_STATISTICS.View partition column statistics.
obclient> SELECT * FROM oceanbase.DBA_OB_EXTERNAL_PART_COL_STATISTICS WHERE catalog_name = 'hive_catalog' AND database_name = 'test_oss' AND table_name = 'lineitem';For more information about
oceanbase.DBA_OB_EXTERNAL_PART_COL_STATISTICS, see oceanbase.DBA_OB_EXTERNAL_PART_COL_STATISTICS.
Step 3: Collect statistics for a specified partition
obclient> CALL DBMS_STATS.GATHER_CATALOG_TABLE_STATS(
'hive_catalog',
'test_oss',
'lineitem',
'l_shipdate=1998-01-01', -- Specify the partition.
100,
'BLOCK',
NULL,
32
);
For more information about DBMS_STATS.GATHER_CATALOG_TABLE_STATS, see GATHER_CATALOG_TABLE_STATS.
Step 4: Set collection parameters
Adjust the batch size for partition processing by using the SET_CATALOG_TABLE_PREFS procedure:
Set the batch size to 64.
obclient> CALL DBMS_STATS.SET_CATALOG_TABLE_PREFS( 'hive_catalog', 'test_oss', 'lineitem', 'CATALOG_STATS_BATCH_SIZE', '64' );For more information about
DBMS_STATS.SET_CATALOG_TABLE_PREFS, see SET_CATALOG_TABLE_PREFS.View the current settings.
obclient> SELECT DBMS_STATS.GET_CATALOG_PREFS( 'CATALOG_STATS_BATCH_SIZE', 'hive_catalog', 'test_oss', 'lineitem' );For more information about
DBMS_STATS.GET_CATALOG_PREFS, see GET_CATALOG_PREFS.Drop the setting (to restore the default value).
obclient> CALL DBMS_STATS.DELETE_CATALOG_TABLE_PREFS( 'hive_catalog', 'test_oss', 'lineitem', 'CATALOG_STATS_BATCH_SIZE' );For more information about
DBMS_STATS.DELETE_CATALOG_TABLE_PREFS, see DELETE_CATALOG_TABLE_PREFS.
Step 5: Lock and unlock statistics
Lock table statistics (to disable automatic refresh).
obclient> CALL DBMS_STATS.LOCK_CATALOG_TABLE_STAT( 'hive_catalog', 'test_oss', 'lineitem' );For more information about
DBMS_STATS.LOCK_CATALOG_TABLE_STAT, see LOCK_CATALOG_TABLE_STAT.Unlock.
obclient> CALL DBMS_STATS.UNLOCK_CATALOG_TABLE_STAT( 'hive_catalog', 'test_oss', 'lineitem' );For more information about
DBMS_STATS.UNLOCK_CATALOG_TABLE_STAT, see UNLOCK_CATALOG_TABLE_STAT.
Step 6: Force a full collection
When you need to ignore freshness checks and forcibly collect statistics for all partitions, specify force => TRUE:
obclient> CALL DBMS_STATS.GATHER_CATALOG_TABLE_STATS(
'hive_catalog',
'test_oss',
'lineitem',
NULL,
100,
'BLOCK',
NULL,
32,
'DEFAULT', -- granularity
TRUE -- force, to force a full collection
);
For more information about DBMS_STATS.GATHER_CATALOG_TABLE_STATS, see GATHER_CATALOG_TABLE_STATS.
References
For detailed information and usage instructions on statistics, see the following topics:
Statistics fall into two types: table-level statistics and column-level statistics. For more information about types of statistics, see Overview of statistics.
The optimizer of OceanBase AI Database supports both manual and automatic statistics collection. For more information about statistics collection, see Overview of statistics collection methods.
For more information about how to manage statistics, see Overview of statistics.
For a simple example of how statistics are used, see Comprehensive example.