0

wondering if someone can help me streamline the below query. It will run for hours, and then it give idle timeout warning. We updated the idle time to unlimited and now we get: Error message: ORA-12801: error signaled in parallel query server P007 ORA-08103: object no longer exists

Query:

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;
Larry Cortez
  • 1
  • 1
  • 1
    Your subquery is missing an alias. Is that allowed in oracle? I would imagine that would throw an error immediately though. `WHERE rownum < 100` but no `ORDER BY` feels problematic. The rest of the WHERE clause may be best in a subquery when you select on your `pli` table. I would imagine Oracle's optimizer would decide to use predicate push down to do this step for you, but if it doesn't then that may save you some I/O. At any rate, an EXPLAIN PLAN would likely be helpful for the Oracle experts stumbling into this question, otherwise it's mostly guess work. – JNevill Oct 03 '22 at 18:44
  • Thanks for the feedback. I only added WHERE rownum < 100 to test it and see if it would work with fewer records. It does run without an alias on the subquery, but only when record num is really low, like 40. I'll run an EXPLAIN plan and post soon. – Larry Cortez Oct 03 '22 at 18:50
  • Use [realtime SQL monitoring](https://docs.oracle.com/en/database/oracle/oracle-database/19/tdppt/monitoring-real-time-database-operations.html) to find out a slow operation in the query processing. `object no longer exists` means that some object (partition/table/index) being in use was truncated by another session – astentx Oct 03 '22 at 19:09
  • astentx, thank you. we are currently trying to get our DBA to do this, but they are very slow to respond to requests. – Larry Cortez Oct 03 '22 at 19:24

0 Answers0