Auto DoP 下 insert into select 可能触发在线统计信息收集而造成相关 SQL 计划失效和反复硬解析

为了加速查询并满足业务需求，通常可以开启并行执行来优化查询时间。为了解决手动指定并行度的不便和限制，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。

测试过程

准备测试数据：

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。

   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都是软解析。

   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]> 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]> 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。

   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 及更高版本。