Re: batch process runs slower and slower over time

From: Riyaj Shamsudeen <>
Date: Thu, 17 May 2012 14:08:50 -0500
Message-ID: <>

Hello Cheng
  You might want to write a small loop and take pstack of that process during those two scenarios. Then compare the top calls to see if there are any differences between those two calls. If PL/SQL function calls are in the top, then you know that problem is in the PL/SQL code. BTW, Tanel has OStackProf exactly for this purpose, but I think, that tool uses oradebug short_stack.
  A guess, only guess: Is it possible that PL/SQL program is using some sort of arrays and never initializing them properly after each customer? I encountered a very similar problem with similar stats.


Riyaj Shamsudeen
Principal DBA,
Ora!nternals - - Specialists in Performance, RAC and EBS
OakTable member and Oracle ACE Director

Co-author of the books: Expert Oracle
Practices<>, Pro Oracle SQL, Expert PL/SQL

Join me for next RAC training in March


On Thu, May 17, 2012 at 1:49 PM, Ls Cheng <> wrote:

> Hi all
> My developers have a pretty complex batch process (written entirely in
> PL/SQL packages & functions) running in 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... :-?
> PD: I ran dtrace ( in the
> last test run but the only call which had sustantial variation between a
> fast execution and slow execution is pollsys
> --

Received on Thu May 17 2012 - 14:08:50 CDT

Original text of this message