Re: Long running process
- From: "Terry Sutton" <terrysutton@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 20 Nov 2004 12:08:15 -0800
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@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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!
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
