Re: batch process runs slower and slower over time

From: Rakesh Tikku <rakesh.tikku_at_gmail.com>
Date: Thu, 7 Jun 2012 03:01:55 -0700
Message-ID: <CAE5B=WN5eyvpj=Au4ff3fjy-nvSDXjSs1y6-hz8RbLoE-gRDyQ_at_mail.gmail.com>

I suggest you get a sql trace for at least the first 3 hours of the program run. Look at the raw trace file and compare the sql execution time/logical IO between the first and the third hour and see if you can spot a degradation there. If yes, you need to do further analysis into what might be causing it (read from undo is a very common cause of such behavior, but you have already ruled that out), but at least you have isolated the problem to be SQL related.
If there is no difference at the sql level, then the culprit is probably plsql. Try to find out if this program is making use of a lot of plsql collections and if it might be doing linear scans on them in a loop.  (plsql profiler should be able to catch that... look at the line level execution counts)

Rakesh Tikku

On Wed, Jun 6, 2012 at 11:36 PM, Ls Cheng <exriscer_at_gmail.com> wrote:

> Hi
> Finally I run several dbms_profiler against the process, now I have doubts
> about the profiler output.
>
> The process runs against several systems, other Oracle Databases using db
> link, AS400 DB2 through gateways and calls some web services (uses some
> java) and total clock elapsed time for a process says 31237 seconds however
> when I sum total_time from PLSQL_PROFILER_DATA for the run it says 12777
> seconds, does this mean the 31237 - 12777 seconds are time taken in other
> systems?
>
> I used the freshly refreshed profiler report written by Carlos Sierra :-)
>
>
> Thanks
>
> --
> LSC
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Thu Jun 07 2012 - 05:01:55 CDT