Re: Long running process

From: Terry Sutton <terrysutton_at_usa.net>
Date: Sat, 20 Nov 2004 12:08:15 -0800
Message-ID: <00bd01c4cf3c$d3e7d5b0$247018ac@TerrySutton>

When you say cumulative SQL runtimes are less than 20 minutes, is that the elasped time? Are you running 9i? If so, the wait events will show all the times of the DB sessions, including SQL*Net Message from Client. And if that's all in the 20 minutes, then it's not Oracle making it take 2 hours.

Of course, this assumes that you are tracing all of the sessions used for the job. Are you taking statspack samples? While a system-wide sample is not as focused as a trace of the actual sessions running slowly, it will give some overall insight. You can see if there's other work happening which your tracing might have missed, or you could possibly rule out Oracle completely (if total CPU and waits are low).

--Terry

Original Message ----- From: "sol beach" <sol.beach_at_gmail.com> To: <oracle-l_at_freelists.org> Sent: Saturday, November 20, 2004 9:57 AM Subject: Long running process

I have one Solaris system called AP4 which has any hourly cron job which invokes Perl code.
This Perl code reads local files & make calls in an Oracle DB on a system called CDB1.
The process really needs to complete in less than 1 hour, but the run that starts just after
midnight takes 2+ hours to complete.
I have enabled SQL_TRACE within CDB1 when SYSDATE hours is less than 03. I recorded a mere 127 sessions from AP4 into CDB1. CDB1 does appears to have plenty of slack resources based upon sar statistics.
TKPROF shows relatively efficient SQL and nothing that would come close to 150 MINUTES worth of processing. The actual cumulative SQL runtimes are under 20 minutes. AP4 is a SPARC V60, and sar shows CPU only about 33% busy & no significant paging.
On the surface neither system appears that it is the bottleneck or resource starved.
What are some options WRT finding where the bottleneck really is? Does PERL have anything close to SQL_TRACE or will I be forced to roll my own instrumentation within the 1200+ line monster?
I inherited this mess and am expected to find & fix the problem. Life is full of unexpected challenges.
TIA & HAND!
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Sat Nov 20 2004 - 14:28:13 CST