Re: batch process runs slower and slower over time

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Thu, 7 Jun 2012 21:32:06 +0300
Message-ID: <CAMHX9JKQ+h7V2PzzC2uq5iMY-RgjN--QqzByTXLzLorY9enBFQ_at_mail.gmail.com>

I also wrote DStackProf which uses Dtrace (although it doesn't format the output as nicely as OstackProf does):
http://blog.tanelpoder.com/2008/09/02/oracle-hidden-costs-revealed-part2-using-dtrace-to-find-why-writes-in-system-tablespace-are-slower-than-in-others/

Whatever the profilers show should be put into context of how much work got done during the different periods too ...

Tanel.

On Thu, May 17, 2012 at 10:08 PM, Riyaj Shamsudeen < riyaj.shamsudeen_at_gmail.com> wrote:

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

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

Received on Thu Jun 07 2012 - 13:32:06 CDT