DBMS_PARALLEL_EXECUTE执行存储过程耗时异常，求与DBMS_SCHEDULER差异原因

以下是导致两者执行表现差异的核心原因及排查方向：

1. 会话参数与执行环境差异 #

DBMS_PARALLEL_EXECUTE的并行分块会继承任务发起会话的参数配置，而DBMS_SCHEDULER作业或手动测试会话通常使用默认或独立的参数集。常见影响场景：

• 若发起任务的主会话设置了OPTIMIZER_MODE=RULE、PARALLEL_DEGREE_POLICY=MANUAL等非最优参数，会导致存储过程的执行计划退化；而手动执行或SCHEDULER作业可能使用ALL_ROWS或AUTO等默认优化模式。
• 临时表空间、排序区（SORT_AREA_SIZE）等资源参数的差异，也会导致批量并行执行时出现资源瓶颈。

验证方法：在存储过程中添加日志输出关键参数，比如：

DBMS_OUTPUT.PUT_LINE('Optimizer Mode: ' || SYS_CONTEXT('USERENV', 'OPTIMIZER_MODE'));
DBMS_OUTPUT.PUT_LINE('Current Schema: ' || SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'));

对比两种执行环境的参数差异。

2. 资源竞争与锁等待 #

DBMS_PARALLEL_EXECUTE的所有并行分块共享同一任务框架的资源池，当多个分块同时执行目标SELECT语句时，容易出现：

• 行级锁争用：若SELECT语句涉及更新频繁的表，并行会话会相互等待对方释放锁。
• 临时资源冲突：比如多个分块同时使用临时表排序，导致direct path write temp等待。

而DBMS_SCHEDULER的每个作业是独立会话，资源隔离性更好，不会出现集中式的资源争用。

验证方法：查询V$SESSION_WAIT视图，查看并行会话的等待事件：

SELECT sid, event, p1, p2, p3 FROM V$SESSION_WAIT WHERE program LIKE '%DBMS_PARALLEL_EXECUTE%';

3. 执行计划适配性差异 #

单独执行存储过程时，Oracle会针对单个部门ID生成最优执行计划（比如使用索引快速定位）；但DBMS_PARALLEL_EXECUTE的分块是基于部门ID范围批量处理，Oracle可能生成针对大范围数据的执行计划（比如全表扫描），反而不适合单个部门的查询场景。

此外，并行执行框架可能会禁用自适应执行计划或忽略最新统计信息，导致执行计划固化为低效模式。

验证方法：导出两种场景下的实际执行计划对比：

-- 手动执行时获取计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-- 并行执行时，找到对应会话的SQL_ID后获取计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID', NULL, 'ALLSTATS LAST'));

4. 并行框架的额外开销 #

DBMS_PARALLEL_EXECUTE依赖Oracle的并行执行协调器，存在分块调度、任务状态同步等额外开销。若并行度设置过高（超过CPU核心数），会导致调度延迟和上下文切换频繁，进一步拖慢执行速度。

而DBMS_SCHEDULER作业是独立触发存储过程，无并行框架的协调开销，执行流程更直接。

验证方法：降低DBMS_PARALLEL_EXECUTE的并行度（比如从8调整为4），观察执行时间是否改善。

5. 权限与上下文解析差异 #

DBMS_PARALLEL_EXECUTE的执行会话继承任务创建者的权限，而手动测试或SCHEDULER作业使用当前用户权限，可能导致：

• 某些索引、视图或同义词无法被解析，被迫使用低效的执行路径。
• CURRENT_SCHEMA上下文不同，导致存储过程中引用的对象指向错误的表。

1. 在存储过程中添加会话参数和执行日志，定位环境差异。
2. 监控并行会话的等待事件，确认是否存在资源争用。
3. 对比两种场景的执行计划，找出执行路径的差异。
4. 在存储过程开头强制设置最优会话参数（如ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS;），验证是否恢复性能。

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