基于湖库一体架构，统一管理结构化、半结构化与非结构化等多模态数据，一个系统承载事务处理、实时分析与 AI 工作负载。
统计信息和估行机制的使用
更新时间：2025-07-10 15:26:46
编辑
分享
上文介绍了 OceanBase 数据库优化器的统计信息和估行机制，在业务场景中如何通过计划去分析统计信息是否有使用或者说统计信息是没有过期的？如何通过计划分析当前计划选择了哪种方式去估行的呢？本节将通过一些具体的例子一一展开介绍。
如何通过计划分析当前计划选择了哪种方式估行
首先如下创建了按照 c1 列 Hash 分区且分区数为 4 的 t_part 表，并插入 10000 行数据。
obclient [TEST]> create table t_part(c1 int, c2 int, c3 int) partition by hash(c1) partitions 4;
Query OK, 0 rows affected (0.164 sec)
obclient [TEST]> insert into t_part select mod(level,500),mod(level,1000),level from dual connect by level<=10000;
Query OK, 10000 rows affected (0.186 sec)
Records: 10000 Duplicates: 0 Warnings: 0
obclient [TEST]> commit;
Query OK, 0 rows affected (0.040 sec)
当没有统计信息时，优化器会尝试采用动态采样的方式估计行数。观察 Optimization Info 中的 estimation method:[DYNAMIC SAMPLING FULL] ，可以看到当前计划使用的是动态采样的方法。当 set optimizer_dynamic_sampling = 0; 时，则关闭了动态采样，优化器会使用默认统计信息。
-- 没有统计信息，尝试动态采样
obclient [TEST]> explain extended select * from t_part where c1 > 1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------- |
| |0 |PX COORDINATOR | |9960 |29434 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|9960 |20651 | |
| |2 | └─PX PARTITION ITERATOR| |9960 |907 | |
| |3 | └─TABLE FULL SCAN |T_PART |9960 |907 | |
| ============================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(T_PART.C1(0x7f5fd0c20c90), T_PART.C2(0x7f5fd0c22010), T_PART.C3(0x7f5fd0c22330))(0x7f5fd0cab130)]), filter(nil), rowset=256 |
| 1 - output([INTERNAL_FUNCTION(T_PART.C1(0x7f5fd0c20c90), T_PART.C2(0x7f5fd0c22010), T_PART.C3(0x7f5fd0c22330))(0x7f5fd0cab130)]), filter(nil), rowset=256 |
| dop=1 |
| 2 - output([T_PART.C1(0x7f5fd0c20c90)], [T_PART.C2(0x7f5fd0c22010)], [T_PART.C3(0x7f5fd0c22330)]), filter(nil), rowset=256 |
| force partition granule |
| 3 - output([T_PART.C1(0x7f5fd0c20c90)], [T_PART.C2(0x7f5fd0c22010)], [T_PART.C3(0x7f5fd0c22330)]), filter([T_PART.C1(0x7f5fd0c20c90) > 1(0x7f5fd0c21600)]), rowset=256 |
| access([T_PART.C1(0x7f5fd0c20c90)], [T_PART.C2(0x7f5fd0c22010)], [T_PART.C3(0x7f5fd0c22330)]), partitions(p[0-3]) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T_PART.__pk_increment(0x7f5fd0c22a20)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| FULL(@"SEL$1" "TEST"."T_PART"@"SEL$1") |
| PARALLEL( AUTO ) |
| OPTIMIZER_FEATURES_ENABLE('4.2.4.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| T_PART: |
| table_rows:10000 |
| physical_range_rows:10000 |
| logical_range_rows:10000 |
| index_back_rows:0 |
| output_rows:9960 |
| table_dop:1 |
| dop_method:Auto DOP |
| avaiable_index_name:[T_PART] |
| stats info:[version=0, is_locked=0, is_expired=0] |
| dynamic sampling level:1 |
| estimation method:[DYNAMIC SAMPLING FULL] |
| Plan Type: |
| DISTRIBUTED |
| Note: |
| Degree of Parallelisim is 1 because of Auto DOP |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
55 rows in set
obclient [TEST]> set optimizer_dynamic_sampling = 0;
Query OK, 0 rows affected (0.035 sec)
-- 没有统计信息，不能使用动态采样，会使用默认统计信息
obclient [TEST]> explain extended select * from t_part where c1 > 1;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------- |
| |0 |PX COORDINATOR | |1 |16 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|1 |16 | |
| |2 | └─PX PARTITION ITERATOR| |1 |16 | |
| |3 | └─TABLE FULL SCAN |T_PART |1 |16 | |
| ============================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(T_PART.C1(0x7f60d6420c90), T_PART.C2(0x7f60d6422010), T_PART.C3(0x7f60d6422330))(0x7f60d64ab020)]), filter(nil), rowset=16 |
| 1 - output([INTERNAL_FUNCTION(T_PART.C1(0x7f60d6420c90), T_PART.C2(0x7f60d6422010), T_PART.C3(0x7f60d6422330))(0x7f60d64ab020)]), filter(nil), rowset=16 |
| dop=1 |
| 2 - output([T_PART.C1(0x7f60d6420c90)], [T_PART.C2(0x7f60d6422010)], [T_PART.C3(0x7f60d6422330)]), filter(nil), rowset=16 |
| force partition granule |
| 3 - output([T_PART.C1(0x7f60d6420c90)], [T_PART.C2(0x7f60d6422010)], [T_PART.C3(0x7f60d6422330)]), filter([T_PART.C1(0x7f60d6420c90) > 1(0x7f60d6421600)]), rowset=16 |
| access([T_PART.C1(0x7f60d6420c90)], [T_PART.C2(0x7f60d6422010)], [T_PART.C3(0x7f60d6422330)]), partitions(p[0-3]) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T_PART.__pk_increment(0x7f60d6422a20)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| FULL(@"SEL$1" "TEST"."T_PART"@"SEL$1") |
| PARALLEL( AUTO ) |
| OPTIMIZER_FEATURES_ENABLE('4.2.4.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| T_PART: |
| table_rows:1 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:0 |
| output_rows:0 |
| table_dop:1 |
| dop_method:Auto DOP |
| avaiable_index_name:[T_PART] |
| stats info:[version=0, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[DEFAULT] |
| Plan Type: |
| DISTRIBUTED |
| Note: |
| Degree of Parallelisim is 1 because of Auto DOP |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
55 rows in set
接下来继续更改查询中的条件，使其能够分区裁剪得到一个分区，查询（explain extended select * from t_part where c1 = 1）的计划如下，可以看见虽然没有收集统计信息，但是可以估算到一个比较准确的行数。同样的通过观察 Optimization Info 中的 estimation method:[DEFAULT, STORAGE]，可以看到估行方式是默认统计信息与存储层估行。那么 24 行是如何估算出来的呢？首先可以看到计划的 QUERY RANGE 是 (MIN ; MAX)，通过 QUERY RANGE 存储层返回的行数是 2400 行；而谓词条件 c1 = 1 的选择率由于没有统计信息可用，只能通过默认统计信息计算，默认的列的 NDV 是 100，因此估算的 c1 = 1 的选择率是 1/100，估算的行为：2400 * 1/100 = 24；上述场景中，虽然可以通过 QUERY RANGE 拿到一个比较准确的行数，但是针对那些不能抽取 QUERY RANGE 的谓词条件，仍然只能依靠默认统计信息来计算选择率，因此仍然最终的估行结果仍然会有误差。
obclient [TEST]> explain extended select * from t_part where c1 = 1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------- |
| |0 |TABLE FULL SCAN|T_PART|24 |104 | |
| ================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_PART.C1(0x7f61b0a20c90)], [T_PART.C2(0x7f61b0a22010)], [T_PART.C3(0x7f61b0a22330)]), filter([T_PART.C1(0x7f61b0a20c90) = 1(0x7f61b0a21600)]), rowset=256 |
| access([T_PART.C1(0x7f61b0a20c90)], [T_PART.C2(0x7f61b0a22010)], [T_PART.C3(0x7f61b0a22330)]), partitions(p1) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T_PART.__pk_increment(0x7f61b0a22a20)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| FULL(@"SEL$1" "TEST"."T_PART"@"SEL$1") |
| PARALLEL( AUTO ) |
| OPTIMIZER_FEATURES_ENABLE('4.2.4.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| T_PART: |
| table_rows:2400 |
| physical_range_rows:2400 |
| logical_range_rows:2400 |
| index_back_rows:0 |
| output_rows:23 |
| table_dop:1 |
| dop_method:Auto DOP |
| avaiable_index_name:[T_PART] |
| stats info:[version=0, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[DEFAULT, STORAGE] |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelisim is 1 because of Auto DOP |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
47 rows in set (0.038 sec)| Query Plan |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------- |
| |0 |TABLE FULL SCAN|T_PART|1 |4 | |
| ================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_PART.C1(0x7f5ffd620c90)], [T_PART.C2(0x7f5ffd622010)], [T_PART.C3(0x7f5ffd622330)]), filter([T_PART.C1(0x7f5ffd620c90) = 1(0x7f5ffd621600)]), rowset=16 |
| access([T_PART.C1(0x7f5ffd620c90)], [T_PART.C2(0x7f5ffd622010)], [T_PART.C3(0x7f5ffd622330)]), partitions(p1) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T_PART.__pk_increment(0x7f5ffd622a20)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| FULL(@"SEL$1" "TEST"."T_PART"@"SEL$1") |
| PARALLEL( AUTO ) |
| OPTIMIZER_FEATURES_ENABLE('4.2.4.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| T_PART: |
| table_rows:1 |
| physical_range_rows:1 |
| logical_range_rows:1 |
| index_back_rows:0 |
| output_rows:1 |
| table_dop:1 |
| dop_method:Auto DOP |
| avaiable_index_name:[T_PART] |
| stats info:[version=0, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[DEFAULT, STORAGE] |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelisim is 1 because of Auto DOP |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
47 rows in set
最后，先收集完 T_PART 的统计信息，然后重新查询 explain extended select * from t_part where c1 = 1 和 explain extended select * from t_part where c1 > 1 的计划如下，可以看见在查询 explain extended select * from t_part where c1 = 1 的计划中，估行是准确的，通过观察 Optimization Info 中的 est_method:[OPTIMIZER STATISTICS]，可以看到当前计划使用的估行方式是通过收集的统计信息进行行数估计的；同时可以看见在查询 explain extended select * from t_part where c1 > 1 的计划中，其估行结果也是准确的，因为使用了收集的统计信息计算了准确的谓词选择率，加上存储层估计行数，因此可以得到一个准确的估行结果。
obclient [TEST]>call dbms_stats.gather_table_stats('TEST','T_PART');
Query OK, 0 rows affected (0.19 sec)
obclient [TEST]> explain extended select * from t_part where c1 > 1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------- |
| |0 |PX COORDINATOR | |9980 |19368 | |
| |1 |└─EXCHANGE OUT DISTR |:EX10000|9980 |13681 | |
| |2 | └─PX PARTITION ITERATOR| |9980 |908 | |
| |3 | └─TABLE FULL SCAN |T_PART |9980 |908 | |
| ============================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([INTERNAL_FUNCTION(T_PART.C1(0x7f603da20c90), T_PART.C2(0x7f603da22010), T_PART.C3(0x7f603da22330))(0x7f603daab0a0)]), filter(nil), rowset=256 |
| 1 - output([INTERNAL_FUNCTION(T_PART.C1(0x7f603da20c90), T_PART.C2(0x7f603da22010), T_PART.C3(0x7f603da22330))(0x7f603daab0a0)]), filter(nil), rowset=256 |
| dop=1 |
| 2 - output([T_PART.C1(0x7f603da20c90)], [T_PART.C2(0x7f603da22010)], [T_PART.C3(0x7f603da22330)]), filter(nil), rowset=256 |
| force partition granule |
| 3 - output([T_PART.C1(0x7f603da20c90)], [T_PART.C2(0x7f603da22010)], [T_PART.C3(0x7f603da22330)]), filter([T_PART.C1(0x7f603da20c90) > 1(0x7f603da21600)]), rowset=256 |
| access([T_PART.C1(0x7f603da20c90)], [T_PART.C2(0x7f603da22010)], [T_PART.C3(0x7f603da22330)]), partitions(p[0-3]) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T_PART.__pk_increment(0x7f603da22a20)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| FULL(@"SEL$1" "TEST"."T_PART"@"SEL$1") |
| PARALLEL( AUTO ) |
| OPTIMIZER_FEATURES_ENABLE('4.2.4.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| T_PART: |
| table_rows:10000 |
| physical_range_rows:10000 |
| logical_range_rows:10000 |
| index_back_rows:0 |
| output_rows:9979 |
| table_dop:1 |
| dop_method:Auto DOP |
| avaiable_index_name:[T_PART] |
| stats info:[version=1720598262361958, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[OPTIMIZER STATISTICS] |
| Plan Type: |
| DISTRIBUTED |
| Note: |
| Degree of Parallelisim is 1 because of Auto DOP |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
55 rows in set (0.037 sec)
obclient [TEST]> explain extended select * from t_part where c1 = 1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================= |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------- |
| |0 |TABLE FULL SCAN|T_PART|20 |104 | |
| ================================================= |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_PART.C1(0x7f5fc6e20c90)], [T_PART.C2(0x7f5fc6e22010)], [T_PART.C3(0x7f5fc6e22330)]), filter([T_PART.C1(0x7f5fc6e20c90) = 1(0x7f5fc6e21600)]), rowset=256 |
| access([T_PART.C1(0x7f5fc6e20c90)], [T_PART.C2(0x7f5fc6e22010)], [T_PART.C3(0x7f5fc6e22330)]), partitions(p1) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T_PART.__pk_increment(0x7f5fc6e22a20)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| FULL(@"SEL$1" "TEST"."T_PART"@"SEL$1") |
| PARALLEL( AUTO ) |
| OPTIMIZER_FEATURES_ENABLE('4.2.4.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| T_PART: |
| table_rows:2400 |
| physical_range_rows:2400 |
| logical_range_rows:2400 |
| index_back_rows:0 |
| output_rows:20 |
| table_dop:1 |
| dop_method:Auto DOP |
| avaiable_index_name:[T_PART] |
| stats info:[version=1720598262361958, is_locked=0, is_expired=0] |
| dynamic sampling level:0 |
| estimation method:[OPTIMIZER STATISTICS, STORAGE] |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelisim is 1 because of Auto DOP |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
47 rows in set
当然，上述只是列举了一些简单的例子方便理解。在实际的业务场景中，对于一些计划不优的问题，可以依据现有的计划分析当前的估行是否有问题，如果估行有问题可以根据上述例子确认统计信息是否有问题；如果是统计信息的问题，可以尝试重新收集统计信息之后再看看计划是否有变化。本节只是以抛砖引玉的方式介绍了统计信息和估行在 OceanBase 数据库优化器中的应用，真实的业务场景中计划会更加复杂，复杂的谓词选择率计算也会更加复杂，因此也需要根据实际的应用场景具体去分析。
通过设置系统变量控制估行
为了提升执行计划的选择准确度，优化器的基数估计起到了关键作用。然而，不准确的基数估计可能导致执行计划出现偏差。基数估计通常依赖于一系列假设，例如谓词独立假设和联接包含假设等。
在实际应用中，用户的数据模型往往与这些假设不完全一致，导致优化器可能生成偏差较大的估计结果，从而选择不理想的执行计划。因此，优化器需要通过设置系统变量 cardinality_estimation_model 来控制部分假设，以生成多种基数估计结果，从而优化执行计划的选择。
