Re: Progressively slower inserts

On 19 Sep 1998 02:51:28 GMT, danhw_at_aol.com (DanHW) wrote:

>
>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.
>
Sounds like you have a buildup of 'global things' in your package. Take a look at the initialization code, will PL/SQL tables grow forever? are loop control reset at start?

-- 
Regards,
Roland