RE: batch process runs slower and slower over time
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: "'exriscer@xxxxxxxxx'" <exriscer@xxxxxxxxx>, 'Oracle Mailinglist' <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 17 May 2012 14:09:36 -0500
I'm curious about something...
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
