Re: Stalling stored procs?

Going for the easy option first -
check the the alert log for ' checkpoint not complete'.

Your description fits the problem of having log files that are too small for a job that is very busy. Check the time at which log file switches took place and compare with the times at which you 'stalled'.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Greg Weston wrote in message
<151020002212185448%gwestonREMOVE_at_CAPShome.com>...


>Okay, here's the environment

>

>AIX 4.x on an RS6k, Oracle 8.1.6, lots of free disk space and RAM,

>nothing else going on on the system. Under normal circumstances the

>system is primarily OLTP but has a batch cycle that lasts an hour or

>two per day. We can't interrupt the OLTP during the batch processing on

>a regular basis, and one particular phase of batch which calls a stored

>proc a few hundred times in a row was causing a problem periodically

>with live transactions taking took long. I was able to get actual OLTP

>activity suspended and turn on a progress journal built into the stored

>procs during that sub-job one night and what I found was that we were

>apparently stalled for more than 10 of the 27 minutes the job ran (6

>stalls, 1.5-2.5 minutes each). Roughly 1/2 hour is the normal run

>length for the job, so I don't think the addition of the progress info

>played any role. I'm looking for information from anyone on

>circumstances under which they have observed stalling or something that

>might be interpreted that way.

>

>Thanks for any clues.

>

>Greg