Oracle 19c中PARALLEL并行提示失效、查询变慢原因及配置咨询

核心成因 #

• 19c并行执行策略调整：19c默认开启自动并行度（Auto DOP）与并行语句排队机制，你指定的/*+PARALLEL 8*/提示会触发优化器优先选择并行计划，但你的SQL通过ACC_ID = 8888888888过滤后实际处理的数据量极小，并行进程调度、数据分片交换的开销远高于串行执行的收益，最终表现为耗时陡增。
• 并行提示滥用：你在最外层查询和内层子查询中重复添加了并行提示，嵌套并行会导致实际申请的并行进程数远高于8，19c对并行进程资源的管控比旧版本更严格，一旦进程不足会触发长时间的资源等待，甚至出现进程调度死锁。
• 多余操作放大并行开销：内层子查询末尾的ORDER BY完全无意义（外层有GROUP BY逻辑，子查询排序不会影响最终结果），并行执行场景下排序需要跨进程做数据分发与归并，开销是串行场景的数倍。
• 统计信息不匹配：升级19c后如果没有重新收集统计信息，优化器会基于旧版本的统计信息错误估算行数，强行生成并行执行计划，和实际数据分布完全不匹配。
• 隐式转换导致索引失效：过滤条件中SUBSTR (A.ACC_ID, 1, 4)的写法如果ACC_ID为数值类型会触发隐式转换，导致索引失效，并行场景下全表扫描的分片总开销远高于串行全表扫描。

19c并行提示正常生效的必要配置 #

• 参数调整
  • 将PARALLEL_DEGREE_POLICY设置为MANUAL，关闭自动并行度，避免系统自动覆盖你指定的并行度，或者对小SQL误触发并行。
  • 合理设置PARALLEL_MAX_SERVERS，根据服务器CPU核心数调整，避免并行进程不足触发排队。
  • 调大PARALLEL_MIN_TIME_THRESHOLD参数（默认10秒），只有预估执行时间超过该阈值的SQL才会触发并行，避免小SQL滥用并行。
• 统计信息更新
  升级完成后必须全库重新收集统计信息，执行命令：

  EXEC DBMS_STATS.GATHER_DATABASE_STATS(GRANULARITY => 'ALL', CASCADE => TRUE);
  

  针对大表可以单独收集直方图信息，保证优化器行数估算准确。
• SQL写法优化
  • 仅在最外层查询加一次并行提示，推荐使用规范写法/*+ PARALLEL(8) */，内层子查询不需要重复添加。
  • 删除内层子查询中多余的ORDER BY语句。
  • 修正过滤条件中的隐式转换，给关联字段、过滤字段建立合适的索引，减少数据扫描量。
  • 对于过滤后数据量极小的SQL，不要强制加并行提示，交给优化器自行选择执行计划即可。

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