仅并行度相差1却致执行计划与运行时长差异巨大的原因解析

一、优化器选择低效计划的原因 #

1. 并行度触发的执行计划切换 #

Oracle优化器会根据并行度（DOP）的变化重新评估各执行路径的成本。当DOP从6提升到7时，优化器的成本模型可能误判，认为先对数据进行跨进程重分布（Shuffle）再计算窗口函数的方案成本更低，但实际场景中，这种重分布会引发大量临时表空间I/O（数据在进程间传递、落地磁盘），直接导致总I/O次数从70万飙升至2600万，耗时剧增。

而DOP≤6时，优化器会选择更高效的路径：并行扫描表后，每个并行进程独立计算窗口函数，无需跨进程传递大量数据，I/O仅集中在表扫描阶段，因此耗时可控。

2. 成本估算偏差 #

Oracle的成本模型依赖表的统计信息（行数、列基数、数据分布等）判断执行计划成本。如果MY_TABLE的统计信息不准确（比如FLAG=1.0的数据集分布、COL_CD的基数估算错误），优化器会错误评估并行操作的CPU/I/O权重：当DOP升高时，过度低估数据重分布的I/O成本，反而高估并行计算的CPU收益，最终选择了低效计划。

二、无需反复测试并行度的解决方案 #

1. 固定高效执行计划 #

SQL Plan Baseline

将DOP=6时的高效执行计划固化为基线，强制优化器始终选用该计划，不受后续DOP变化影响：

• 先以DOP=6执行目标SQL，确保生成高效计划；
• 执行以下语句将计划加载为基线：

  DECLARE
    l_plans_loaded NUMBER;
  BEGIN
    l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
      sql_id => '你的SQL_ID', -- 可通过V$SQL查询目标SQL的SQL_ID
      plan_hash_value => '高效计划的HASH值'
    );
  END;
  /
  

• 后续执行SQL时，优化器会优先匹配并使用基线中的高效计划。

强制Hint约束

在SQL中添加明确的Hint，锁定执行路径，避免优化器因DOP变化切换计划：

SELECT
/*+ PARALLEL(X,6) FULL(MY_TABLE) NO_REWRITE */ -- 锁定DOP和扫描方式，禁止计划重写
ID,
MAX(COL_AB) OVER (PARTITION BY COL_CD) AS COL_AB,
COL_CD
FROM (
     SELECT
     ID,
     COALESCE(COL_A, COL_B) AS COL_AB,
     COALESCE(COL_C, COL_D) AS COL_CD
     FROM MY_TABLE
     WHERE FLAG = 1.0
);

注：Hint需结合高效执行计划的实际操作（比如是否为全表扫描）调整，确保准确性。

2. 修正统计信息与优化器参数 #

• 更新统计信息：确保优化器能准确计算成本：

  EXEC DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME => '你的用户名',
    TABNAME => 'MY_TABLE',
    CASCADE => TRUE,
    ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE
  );
  

• 调整并行度策略：将parallel_degree_policy设为MANUAL，禁用优化器自动调整DOP的功能，避免意外的计划切换：

  ALTER SYSTEM SET parallel_degree_policy = MANUAL;
  

  若仅针对当前会话生效，可改为ALTER SESSION。

3. 重构SQL逻辑 #

改写窗口函数为聚合+关联的形式，减少并行执行时的数据重分布风险：

SELECT t.ID, m.max_col_ab AS COL_AB, t.COL_CD
FROM (
     SELECT ID, COALESCE(COL_A, COL_B) AS COL_AB, COALESCE(COL_C, COL_D) AS COL_CD
     FROM MY_TABLE
     WHERE FLAG = 1.0
) t
JOIN (
     SELECT COALESCE(COL_C, COL_D) AS COL_CD, MAX(COALESCE(COL_A, COL_B)) AS max_col_ab
     FROM MY_TABLE
     WHERE FLAG = 1.0
     GROUP BY COALESCE(COL_C, COL_D)
) m ON t.COL_CD = m.COL_CD;

这种写法将窗口函数拆分为独立的聚合查询和关联操作，优化器在不同DOP下更易选择稳定的执行计划，避免不必要的数据Shuffle。

内容的提问来源于stack exchange，提问作者Mihail