pl/sql problem

From: <carodgers_at_my-deja.com>
Date: 2000/02/16
Message-ID: <88etu7$nkt$1@nnrp1.deja.com>#1/1

I have a large pl/sql program that has about 59 union all statements in it. It should be inserting about 1400 summarized records into another table. It takes forever. I limited the number of rows to run and I get the following results.

100 rows 2.5 minutes
200 rows 2.5 minutes
300 rows 9 minutes
400 rows 48 minutes
1200 rows 5 hours
The entire program took days.

I see the pattern here but I don't know what to tweak. Here is part of the results from the trace on the 400 rows run.

call count cpu elapsed disk query current

    rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.00          0          0          0
       0
Execute    400      0.00       0.00          0          0          0
       0
Fetch    15600      0.00       0.00          0   92606676          0

   15600
------- ------ -------- ---------- ---------- ---------- ----------

---

total 16001 0.00 0.00 0 92606676 0

   15600

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 21 (recursive depth: 1)

---

---

(sorry that came out so ugly)

Notice the 92 million result in the query column.

Any help is appreciated

Thanks,
Chris

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Feb 16 2000 - 00:00:00 CST