--- title: Auto DoP 下 insert into select 可能触发在线统计信息收集而造成相关 SQL 计划失效和反复硬解析-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 Auto DoP 下 insert into select 可能触发在线统计信息收集而造成相关 SQL 计划失效和反复硬解析相关的常见问题和使用技巧，帮助您快速解决 Auto DoP 下 insert into select 可能触发在线统计信息收集而造成相关 SQL 计划失效和反复硬解析的难题。 image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # Auto DoP 下 insert into select 可能触发在线统计信息收集而造成相关 SQL 计划失效和反复硬解析 更新时间：2026-05-14 07:41 适用版本： V4.2.x、V4.3.x 内容类型：TechNote 为了加速查询并满足业务需求，通常可以开启并行执行来优化查询时间。为了解决手动指定并行度的不便和限制，OceanBase 数据库 V4.2 版本引入了 Auto DoP 功能。查询优化器可以通过 Auto DOP 功能在生成查询计划时评估查询需要执行的时间，自动确定是否开启并行和开启适量的并行度。这样可以避免由于手动指定并行度而导致的性能下降。 但是，要启用 Auto DOP 功能，一定需对系统业务负载类型进行评估，并合理配置相关系统变量（`parallel_degree_limit` 和 `parallel_min_scan_time_threshold`）。否则，开启 Auto DoP 可能导致适得其反，给系统带来巨大风险。比如在 OLTP 的业务场景直接无脑开启 Auto DoP 后，个别低频的大查询会抢占大量的系统资源，反而影响了短频快的TP类查询。如果这些低频的 SQL 通过开启 Auto DoP 获取了一个非常高的并行度，一旦将来某一天这些低频 SQL 突然变成了一个高频的 SQL，就会产生将 CPU 打爆的风险。 开启 Auto DoP 的另外一个意料之外的结果是针对 Queuing 模式的表频繁进行数据增删操作时，如果使用了 `insert into ... select ...` 类型的语句对 Queuing 表进行数据插入，这些 insert 语句可能会从之前的非并发 SQL 自动变成 PDML。而在系统变量 `_optimizer_gather_stats_on_load` 为默认值 True 时，启动了 PDML 且 DoP>1 时将会自动触发在线收集统计信息的功能。一旦表上的统计信息发生了在线收集，该表相关的执行计划均会自动失效，导致后面与该表相关的所有 SQL 都需要重新硬解析，无形中增加 SQL 的总体响应耗时。 ## 详细说明 ### 测试验证 **测试版本：** OceanBase 数据库 V4.2.5 BP2。 ### 测试过程 **准备测试数据：** ```shell MySQL [test]> create table t2 (c1 int, c2 int, c3 int); Query OK, 0 rows affected (0.253 sec) MySQL [test]> insert into t2 select row_number() over(), row_number() over(), row_number() over() from TABLE(GENERATOR(1000000)); Query OK, 1000000 rows affected (8.609 sec) Records: 1000000 Duplicates: 0 Warnings: 0 ``` 1. **在默认关闭 Auto DoP 的情况下，反复执行 `insert into t1 select * from t2` 和 `delete from t1`**。 ```shell MySQL [test]> show variables like 'parallel%'; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | parallel_degree_limit | 0 | | parallel_degree_policy | MANUAL | | parallel_min_scan_time_threshold | 1000 | | parallel_servers_target | 30 | +----------------------------------+--------+ 4 rows in set (0.001 sec) MySQL [test]> create table t1 (c1 int, c2 int, c3 int); Query OK, 0 rows affected (0.09 sec) MySQL [test]> insert into t1 select * from t2; Query OK, 1000000 rows affected (8.834 sec) Records: 1000000 Duplicates: 0 Warnings: 0 MySQL [test]> delete from t1; Query OK, 1000000 rows affected (8.397 sec) MySQL [test]> insert into t1 select * from t2; Query OK, 1000000 rows affected (8.315 sec) Records: 1000000 Duplicates: 0 Warnings: 0 MySQL [test]> delete from t1; Query OK, 1000000 rows affected (9.117 sec) MySQL [test]> insert into t1 select * from t2; Query OK, 1000000 rows affected (8.872 sec) Records: 1000000 Duplicates: 0 Warnings: 0 MySQL [test]> delete from t1; Query OK, 1000000 rows affected (9.964 sec) ``` 2. **查看 SQL Audit 记录，可以发现，只有第一次执行 delete from t1 时发生了SQL硬解析，后面两次执行同样的SQL都是软解析**。 ```shell MySQL [test]> select usec_to_time(request_time), query_sql,is_hit_plan,get_plan_time,elapsed_time,execute_time from oceanbase.gv$ob_sql_audit where query_sql='delete from t1' order by request_time; +----------------------------+----------------+-------------+---------------+--------------+--------------+ | usec_to_time(request_time) | query_sql | is_hit_plan | get_plan_time | elapsed_time | execute_time | +----------------------------+----------------+-------------+---------------+--------------+--------------+ | 2025-03-01 20:03:52.796876 | delete from t1 | 0 | 6312 | 8384995 | 8378603 | | 2025-03-01 20:05:17.409018 | delete from t1 | 1 | 37 | 9104716 | 9104633 | | 2025-03-01 20:06:40.039705 | delete from t1 | 1 | 46 | 9950162 | 9950036 | +----------------------------+----------------+-------------+---------------+--------------+--------------+ 3 rows in set (0.015 sec) ``` 3. **开启 Auto DoP 并设置 `parallel_min_scan_time_threshold=10ms`**。 MySQL [test]> set parallel_degree_policy='AUTO'; Query OK, 0 rows affected (0.001 sec) MySQL [test]> set parallel_min_scan_time_threshold=10; Query OK, 0 rows affected (0.001 sec) ``` 4. **在开启 Auto DoP 并设置一个很小的基表扫描执行时间阈值 `parallel_min_scan_time_threshold` 的情况下，反复执行 `insert into t1 select * from t2` 和 `delete from t1`**。 ```shell MySQL [test]> insert into t1 select * from t2; Query OK, 1000000 rows affected (5.745 sec) Records: 1000000 Duplicates: 0 Warnings: 0 MySQL [test]> select dbms_xplan.display_cursor(0,'all') from dual; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =================================================================================================================================== |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)| ----------------------------------------------------------------------------------------------------------------------------------- |0 |OPTIMIZER STATS MERGE | |1000000 |7655362 |0 |5607960 |0 |30081 | |1 |└─PX COORDINATOR | |1000000 |7652715 |0 |5607960 |5570950 |5577176 | |2 | └─EXCHANGE OUT DISTR |:EX10001 |1000000 |7196279 |0 |5577426 |0 |37 | |3 | └─INSERT | |1000000 |6854814 |0 |5577426 |0 |5021851 | |4 | └─OPTIMIZER STATS GATHER | |1000000 |522123 |1000000 |5316084 |0 |295206 | |5 | └─EXCHANGE IN DISTR | |1000000 |521241 |1000000 |5315030 |180226 |400894 | |6 | └─EXCHANGE OUT DISTR (RANDOM)|:EX10000 |1000000 |369095 |1000000 |5218106 |4991020 |5107417 | |7 | └─SUBPLAN SCAN |ANONYMOUS_VIEW1|1000000 |27630 |1000000 |5213887 |0 |2130 | |8 | └─PX BLOCK ITERATOR | |1000000 |26748 |1000000 |5213887 |0 |522 | |9 | └─TABLE FULL SCAN |t2 |1000000 |26748 |1000000 |5213887 |0 |99029 | =================================================================================================================================== Outputs & filters: ------------------------------------- 0 - output(nil), filter(nil), rowset=256 1 - output([column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c1)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c2)], [column_conv(INT,PS:(11,0),NULL, ANONYMOUS_VIEW1.c3)]), filter(nil), rowset=256 2 - output([column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c1)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c2)], [column_conv(INT,PS:(11,0),NULL, ANONYMOUS_VIEW1.c3)]), filter(nil), rowset=256 dop=3 3 - output([column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c1)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c2)], [column_conv(INT,PS:(11,0),NULL, ANONYMOUS_VIEW1.c3)]), filter(nil) columns([{t1: ({t1: (t1.__pk_increment, t1.c1, t1.c2, t1.c3)})}]), partitions(p0), column_values([T_HIDDEN_PK], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c1)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c2)], [column_conv(INT, PS:(11,0),NULL,ANONYMOUS_VIEW1.c3)]) 4 - output([column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c1)], [column_conv(INT,PS:(11,0),NULL,ANONYMOUS_VIEW1.c2)], [column_conv(INT,PS:(11,0),NULL, ANONYMOUS_VIEW1.c3)], [T_HIDDEN_PK]), filter(nil), rowset=256 5 - output([T_HIDDEN_PK], [ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2], [ANONYMOUS_VIEW1.c3]), filter(nil), rowset=256 6 - output([T_HIDDEN_PK], [ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2], [ANONYMOUS_VIEW1.c3]), filter(nil), rowset=256 dop=3 7 - output([ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2], [ANONYMOUS_VIEW1.c3]), filter(nil), rowset=256 access([ANONYMOUS_VIEW1.c1], [ANONYMOUS_VIEW1.c2], [ANONYMOUS_VIEW1.c3]) 8 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=256 9 - output([t2.c1], [t2.c2], [t2.c3]), filter(nil), rowset=256 access([t2.c1], [t2.c2], [t2.c3]), partitions(p0) is_index_back=false, is_global_index=false, range_key([t2.__pk_increment]), range(MIN ; MAX)always true Used Hint: ------------------------------------- /*+ */ Qb name trace: ------------------------------------- stmt_id:0, INS$1 stmt_id:1, SEL$1 Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA PARALLEL(@"SEL$1" "test"."t2"@"SEL$1" 3) FULL(@"SEL$1" "test"."t2"@"SEL$1") PARALLEL( AUTO ) ENABLE_PARALLEL_DML OPTIMIZER_FEATURES_ENABLE('4.2.5.1') END_OUTLINE_DATA */ Optimization Info: ------------------------------------- t2: table_rows:1000000 physical_range_rows:1000000 logical_range_rows:1000000 index_back_rows:0 output_rows:1000000 table_dop:3 dop_method:Auto DOP avaiable_index_name:[t2] stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0] dynamic sampling level:0 estimation method:[DEFAULT, STORAGE] Plan Type: DISTRIBUTED Note: Degree of Parallelisim is 3 because of Auto DOP | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.226 sec) ``` **备注：上面执行计划中的 `OPTIMIZER STATS GATHER/OPTIMIZER STATS MERGE` 对应的就是自动在线收集统计信息的执行算子**。 ## 影响租户 影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户以及 MySQL 租户。 ## 适用版本 OceanBase 数据库 V4.2 及更高版本。 Previous [如何获取 SQL 的物理执行计划](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002999340) Next [large_query_worker_percentage 无法限制住 CPU](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000006084950) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线