RE: Long running process

From: Reidy, Ron <Ron.Reidy_at_arraybiopharma.com>
Date: Sat, 20 Nov 2004 12:09:57 -0700
Message-ID: <17CAB0BF27BCFC47B0E4554A0E2F962B56282E@fiji.arraybp.com>

Sol,

You should look at using the Devel::Prof = (http://search.cpan.org/~jaw/Devel-Profile-1.04/Profile.pm) module to = rule out Perl problems.

You say you see nothing in the trace files that would indicate 150 = minutes of processing; this lead me to believe there may be wait events = missing, like the SQL*Net variety. So, not to seem too sracastic, but = did you find any od these types of waits?

Also, since you are turning on trace for a specific time period, does = this mean this process generates many trace files? If so, this may well = be your problem.

Good luck.

--

Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----

From:	oracle-l-bounce_at_freelists.org on behalf of sol beach
Sent:	Sat 11/20/2004 10:57 AM
To:	oracle-l_at_freelists.org
Cc:=09
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=20 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

This electronic message transmission is a PRIVATE communication which = contains
information which may be confidential or privileged. The information is = intended=20
to be for the use of the individual or entity named above. If you are = not the=20
intended recipient, please be aware that any disclosure, copying, = distribution=20
or use of the contents of this information is prohibited. Please notify = the
sender of the delivery error by replying to this message, or notify us = by
telephone (877-633-2436, ext. 0), and then delete it from your system.

--

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