Re: batch process runs slower and slower over time
- From: Rakesh Tikku <rakesh.tikku@xxxxxxxxx>
- To: exriscer@xxxxxxxxx
- Date: Thu, 7 Jun 2012 03:01:55 -0700
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@xxxxxxxxx> 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
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
