Re: batch process runs slower and slower over time

From: Toon Koppelaars <toon.koppelaars_at_rulegen.com>
Date: Thu, 17 May 2012 20:58:31 +0200
Message-ID: <CAA9w=EvN_1wTSnZQm8ok-h7_cxRhgDpfJ94zoGDm9Kgua-11mw_at_mail.gmail.com>

This sounds like a memory leak issue here. Do you see the memory footprint of the dedicated server executing the PL/SQL slowly increasing?
A quick google on "oracle 10.2.0.5 memory leak" finds several potentially interesting posts.

On Thu, May 17, 2012 at 8:49 PM, Ls Cheng <exriscer_at_gmail.com> wrote:

> 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
>
>
>

-- 
Toon Koppelaars
RuleGen BV
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.rulegen.com/am4dp-backcover-text


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

Received on Thu May 17 2012 - 13:58:31 CDT