Re: batch process runs slower and slower over time

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Thu, 17 May 2012 14:08:50 -0500
Message-ID: <CAA2DszwV98AAhYRHpt4USgv8w+TYjRaiasY-CczfWH0SM1H0Og_at_mail.gmail.com>



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.

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS
Blog: http://orainternals.wordpress.com
OakTable member http://www.oaktable.com and Oracle ACE Director

Co-author of the books: Expert Oracle
Practices<http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL, Expert PL/SQL
Practices<http://tinyurl.com/book-expert-plsql-practices>

Join me for next RAC training in March
2012<http://www.orainternals.com/services/training/advanced-rac-training/>:

<http://tinyurl.com/book-expert-plsql-practices>

On Thu, May 17, 2012 at 1: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
>
>
>

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

Original text of this message