RE: batch process runs slower and slower over time

Do these batch processes do a lot of deletes and then inserts into tables? Are we doing row-by-row processing?

The reason I ask these questions are:

A.) I have encountered situations where deletes and inserts over time in long running queries will slow to single block processing and I *think* it's because Oracle has to check for free space to do the next insert in a data fragmented table (think table data that has deleted rows spread sporadically through it). Oracle will attempt [by default] to insert new data into that existing empty freespace before it will raise the high water mark (I'm over simplifying here but...) To get around the insert penalty, you can use the /*+ APPEND */ hint on all insert statements to get Oracle to append data to the 'end' of the table and raise the HWM. (Again, over simplifying for time). This still leaves that empty space in the table and causes the segment to use too much space in the database due to the empty space.

B.) If you're doing a lot of inserts from cursors select from type statements, DEFINITELY look at BULK COLLECT, FORALL INSERT replacements. These PL/SQL constructs are fairly simple to implement and replace existing cursor for loops. BULK COLLECT is also a good practice for updates as well. As an example, we had a conversion process that was going to take about 12 minutes per month for 48 months of data during an application upgrade two weekends ago. After examining the developer code, I thought it would be a good candidate for BULK COLLECT, FORALL INSERT and the insert took 9 minutes after implementing the change. :)

Chris Taylor

"Quality is never an accident; it is always the result of intelligent effort."
-- John Ruskin (English Writer 1819-1900)

Any views and/or opinions expressed herein are my own and do not necessarily reflect the views of Ingram Industries, its affiliates, its subsidiaries or its employees.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ls Cheng Sent: Thursday, May 17, 2012 1:49 PM
To: Oracle Mailinglist
Subject: batch process runs slower and slower over time

Hi all
My developers have a pretty complex batch process (written entirely in PL/SQL packages & functions) running in 10.2.0.5 RAC database (Solaris 10), this process runs in around 12 hours time, we have noticed that the process gets slower and slower over time.

This process treats customers information, roughly 10000 customers, during the first hour it takes around 2 seconds per customer, after 1 hour it tajes 3, after 2 hours 4 seconds and so on, the funny thing is that if we abort the process and restart again the elapsed time per customer drops again to 2 seconds but after 1 hour it starts increasing again. I have add debug information, after each customer treatment we capture v$sesstat and v$session_event to see what statistics or wait event is increasing when the elapsed time goes up, the only statistics which increases over time is "CPU used by this session", basically the process is burning CPU, the rest of statistics doesnt not vary.

It is very puzzling and I cannot find more information to debug, does anyone know how to tackle this sort of problem... :-?

TIA PD: I ran dtrace (http://www.brendangregg.com/DTrace/procsystime) in the last test run but the only call which had sustantial variation between a fast execution and slow execution is pollsys

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu May 17 2012 - 14:09:36 CDT