Oracle查询超时及ORA-08103问题求助：优化耗时查询

问题背景 #

原查询运行数小时后触发空闲超时警告，设置空闲时间无限制后，出现以下错误：

ORA-12801: error signaled in parallel query server P007
ORA-08103: object no longer exists

原查询语句：

SELECT proj_id,
       wbs_id,
       task_id,
       rsrc_id,
       taskrsrc_id,
       SUM(commit_amt) commit_amt,
       SUM(oblig_amt) oblig_amt
FROM (
SELECT   pt.proj_id,
         pt.wbs_id,
         pt.task_id,
         ptr.rsrc_id,
         pli.taskrsrc_id,
         SUM(NVL(pli.certified_us_amt,0)) 
       + SUM(NVL(pli.obli_excess_commit_amt,0)) 
       - SUM(NVL(pli.deob_amt,0)) commit_amt,
         CASE WHEN pli.moa_code LIKE 'I%' THEN SUM(NVL(pli.certified_us_amt,0)) 
                                             + SUM(NVL(pli.obli_excess_commit_amt,0)) 
                                             - SUM(NVL(pli.deob_amt,0)) 
                                             - SUM(NVL(pli.unoblig_us_bal_amt,0))
              ELSE SUM(NVL(oli.oli_approved_amt,0)) 
         END AS oblig_amt
FROM     pr_line_item pli
LEFT OUTER JOIN obligation_line_item oli ON (pli.etl_foa_code = oli.etl_foa_code
                                                  AND pli.prac_no = oli.prac_no
                                                  AND pli.prac_line_no = oli.prac_line_no)
INNER JOIN resource_codes rc ON (pli.etl_foa_code = rc.etl_foa_code
                                      AND pli.resource_code = rc.resource_code)
LEFT OUTER reorg_xref prx ON (prx.old_org_code = pli.labor_receive_org_code)
INNER JOIN taskrsrc ptr ON (ptr.taskrsrc_id = pli.taskrsrc_id)
INNER JOIN task pt ON (ptr.task_id = pt.task_id)
WHERE rownum < 100 and NVL(pli.taskrsrc_id,-1) > 0
AND NVL(pli.certified_us_amt,0) + NVL(pli.obli_excess_commit_amt,0) - NVL(pli.deob_amt,0) > 0
GROUP BY pt.proj_id,
         pt.wbs_id,
         pt.task_id,
         ptr.rsrc_id,
         pli.taskrsrc_id,
         pli.moa_code
)
GROUP BY proj_id,
         wbs_id,
         task_id,
         rsrc_id,
         taskrsrc_id;

错误原因分析 #

• ORA-08103通常表示查询过程中访问的对象被删除、重命名或分区被维护，结合并行查询的ORA-12801，大概率是并行执行期间目标表发生了DDL操作，或是并行查询生成的临时段被意外清理。
• 也可能是统计信息过期，导致Oracle生成的执行计划不合理，引发并行执行异常。

查询优化及问题解决建议 #

1. 移除无效连接 #

原查询中LEFT OUTER JOIN reorg_xref prx未在SELECT或WHERE子句中使用任何字段，直接删除该连接可减少数据扫描量。

2. 简化聚合逻辑，避免重复计算 #

将子查询中重复的SUM计算合并，减少数据库计算开销：

-- 合并commit_amt的计算逻辑
SUM(NVL(pli.certified_us_amt, 0) + NVL(pli.obli_excess_commit_amt, 0) - NVL(pli.deob_amt, 0)) commit_amt,
-- 复用commit_amt结果简化CASE分支
CASE WHEN pli.moa_code LIKE 'I%' THEN commit_amt - SUM(NVL(pli.unoblig_us_bal_amt,0))
     ELSE SUM(NVL(oli.oli_approved_amt,0)) 
END AS oblig_amt

3. 优化WHERE条件 #

• 将NVL(pli.taskrsrc_id,-1) > 0改为pli.taskrsrc_id IS NOT NULL AND pli.taskrsrc_id > 0，避免依赖函数索引（无对应索引时可提升过滤效率）。
• 确认rownum < 100是测试需求还是正式逻辑，若为测试则保留，正式环境需移除。

4. 消除双重聚合 #

原查询通过子查询+外层聚合实现结果合并，可直接调整GROUP BY层级，去掉冗余嵌套：

SELECT pt.proj_id,
       pt.wbs_id,
       pt.task_id,
       ptr.rsrc_id,
       pli.taskrsrc_id,
       SUM(NVL(pli.certified_us_amt,0) + NVL(pli.obli_excess_commit_amt,0) - NVL(pli.deob_amt,0)) commit_amt,
       SUM(CASE WHEN pli.moa_code LIKE 'I%' THEN 
                   NVL(pli.certified_us_amt,0) + NVL(pli.obli_excess_commit_amt,0) - NVL(pli.deob_amt,0) - NVL(pli.unoblig_us_bal_amt,0)
                ELSE NVL(oli.oli_approved_amt,0) 
           END) AS oblig_amt
FROM pr_line_item pli
LEFT OUTER JOIN obligation_line_item oli ON (pli.etl_foa_code = oli.etl_foa_code
                                            AND pli.prac_no = oli.prac_no
                                            AND pli.prac_line_no = oli.prac_line_no)
INNER JOIN resource_codes rc ON (pli.etl_foa_code = rc.etl_foa_code
                                AND pli.resource_code = rc.resource_code)
INNER JOIN taskrsrc ptr ON (ptr.taskrsrc_id = pli.taskrsrc_id)
INNER JOIN task pt ON (ptr.task_id = pt.task_id)
WHERE pli.taskrsrc_id IS NOT NULL AND pli.taskrsrc_id > 0
  AND (NVL(pli.certified_us_amt,0) + NVL(pli.obli_excess_commit_amt,0) - NVL(pli.deob_amt,0)) > 0
GROUP BY pt.proj_id,
         pt.wbs_id,
         pt.task_id,
         ptr.rsrc_id,
         pli.taskrsrc_id;

5. 添加针对性索引 #

基于连接和过滤条件创建索引，提升数据检索效率：

• 连接字段索引：pr_line_item(etl_foa_code, prac_no, prac_line_no)、pr_line_item(taskrsrc_id)、taskrsrc(task_id, taskrsrc_id, rsrc_id)、task(task_id, proj_id, wbs_id)
• 过滤条件索引：pr_line_item(certified_us_amt, obli_excess_commit_amt, deob_amt)

6. 排查并行执行问题 #

• 在查询开头添加/*+ NO_PARALLEL */提示，关闭并行执行，验证是否仍出现ORA-12801错误，排除并行机制本身的问题。
• 检查数据库自动维护任务（如分区清理、表重建）的执行时间，避免与查询运行时段冲突。

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