Performance Problems with large inserts in Oracle 7.3 on AIX

I am an Data Warehouse Consultant at a large oil company working on a small data warehouse (45-60Gb). We have written some quite complex routines in PL/SQL to load data into the warehouse.

These routines have been working for about three months, and they have been tuned to run very efficiently (in terms of the select statements underneath the INSERTS).

We have recently moved to a new environment with the same table

structure, and the routines have not changed at all. Suddenly, the
performance has dropped dramatically on the inserts. When we run the
queries on their own, they come back in two minutes.

The CPU utilisation increases to almost 50% on the machine, and the amount of processor utilisation is about 50%, but the I/O's are 0.

We have found a workaround by putting the final result set into a cursor, and inserting into the table row by row from the output of the cursor. This seems to be a treatment of a symptom, rather than a solution.

Has anybody out there ever experienced anything like this?

We are running Oracle 7.3 on an IBM RS 6000 with AIX.

Please could you email me on jkotsaft_at_iafrica.com with your reponses.

Thanks in advance
John Kotsaftis Received on Sat Apr 19 1997 - 00:00:00 CDT