Long running process
- From: sol beach <sol.beach@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 20 Nov 2004 09:57:24 -0800
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
Other related posts:
