From oracle-l-bounce@freelists.org Sat Nov 20 13:14:01 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id iAKJDUu05177 for ; Sat, 20 Nov 2004 13:13:40 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id iAKJDKi05168 for ; Sat, 20 Nov 2004 13:13:30 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9308872CB30; Sat, 20 Nov 2004 14:19:28 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 12398-47; Sat, 20 Nov 2004 14:19:28 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8730D72C0A5; Sat, 20 Nov 2004 14:14:19 -0500 (EST) Priority: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Content-Class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain; charset=Windows-1252 Content-Transfer-Encoding: 8bit Subject: RE: Long running process Date: Sat, 20 Nov 2004 12:09:57 -0700 Message-ID: <17CAB0BF27BCFC47B0E4554A0E2F962B56282E@fiji.arraybp.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Long running process thread-index: AcTPKx/Zk3Z3CCeoRVGs+wLLaCx1twACEbgx From: "Reidy, Ron" To: , X-OriginalArrivalTime: 20 Nov 2004 19:07:58.0203 (UTC) FILETIME=[3F64B4B0:01C4CF34] X-archive-position: 12509 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Ron.Reidy@arraybiopharma.com Precedence: normal Reply-To: Ron.Reidy@arraybiopharma.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@freelists.org on behalf of sol beach Sent: Sat 11/20/2004 10:57 AM To: oracle-l@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