Progressively slower inserts

[Quoted] I have a package that reads rows from one table, and loads it into rows in several tables (conversion program), and exhibiting a strange behavior. I run a script from SQLPlus that processes 50 rows from the original table, does a commit, and stops. The cursor is something like select rows from old_table where id not in new_table so I can run it repeatedly to process the entire table. The insert process uses various stored procedures and triggers, PL/SQL tables, etc to do all the required processing and insert data into 4 or 5 different tables. Now for the strange behavior...

If I run the script to load 50 rows, it takes a average of 3 or 4 seconds per row (200 sec total). If I run the script again, it takes 5 or 6 seconds per row. If I do it again, it might take 10 seconds, and so forth. At one point, it was taking 40 sec/row.

However, if I run the script to load 50 rows, then disconnect and reconnect between successive runs, the time per row stays constant at about 4 seconds/row.

The time per row in a batch of 50 might go from 2 sec for the first one to 4 seconds for the last one. If I do not disconnect, the time interval per row for the next interation starts near 4 and ends near 6 or 7. If I disconnect, the 2nd pass goes back to the 2 sec to 4 sec range.

Any one got any ideas?

Dan Hekimian-Williams Received on Sat Sep 19 1998 - 04:51:28 CEST