batch process runs slower and slower over time

From: Ls Cheng <exriscer_at_gmail.com>
Date: Thu, 17 May 2012 20:49:24 +0200
Message-ID: <CAJ2-Qb-oFLQrkw3dAKxvcoS3B20pbwt1biYCT+2Bfe4uQyf69w_at_mail.gmail.com>

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

Received on Thu May 17 2012 - 13:49:24 CDT