From oracle-l-bounce@freelists.org Thu Apr 29 23:54:49 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3U4sN314914 for ; Thu, 29 Apr 2004 23:54:33 -0500 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 i3U4sD614898 for ; Thu, 29 Apr 2004 23:54:23 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9733072CA59; Thu, 29 Apr 2004 23:45:57 -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 11525-20; Thu, 29 Apr 2004 23:45:57 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D70BA72C6E2; Thu, 29 Apr 2004 23:45:56 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 29 Apr 2004 23:44:44 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6AED272C9F5 for ; Thu, 29 Apr 2004 23:44:43 -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 09707-57 for ; Thu, 29 Apr 2004 23:44:43 -0500 (EST) Received: from relay2.iflexsolutions.com (unknown [202.144.91.162]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0D61472C9F4 for ; Thu, 29 Apr 2004 23:44:42 -0500 (EST) Received: from fmgrt.rt.i-flex.com (pppserver [192.168.50.2]) by relay2.iflexsolutions.com (8.12.3/8.12.3) with ESMTP id i3UAPdJk025105 for ; Fri, 30 Apr 2004 10:25:39 GMT Received: from fmgrt.rt.i-flex.com ([192.168.50.2]) by fmgrt.rt.i-flex.com with Microsoft SMTPSVC(5.0.2195.6713); Fri, 30 Apr 2004 10:31:18 +0530 X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1 content-class: urn:content-classes:message MIME-Version: 1.0 Content-type: text/plain Subject: RE: a pl/sql question Date: Fri, 30 Apr 2004 10:31:17 +0530 Message-ID: <10898BE7CA96D611988B000802255AAF03427DED@FMGRT.rt.i-flex.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: <10898BE7CA96D611988B000802255AAF03427DED@FMGRT.rt.i-flex.com> Thread-Topic: a pl/sql question Thread-Index: AcQuJZfEY5+RgrceScCv7rqVDW/bEgASKp7O From: To: X-OriginalArrivalTime: 30 Apr 2004 05:01:18.0609 (UTC) FILETIME=[2C34D010:01C42E70] X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 4081 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: k.sriramkumar@iflexsolutions.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Hi Guang, Can you pls trace the session running this code and post the relevant sections of the output?. The code you have posted here lacks details .I suggest the problem could be well stated with supporting details like the volume of data in the tables/selectivity of columns/trace output. Pls elaborate this statement.. I am thinking is that the sub pl/sql block within this function is creating some "overhead" pls explain what ovearhead this statement is causing..Is the SQL statement causing the overhead on CPU/Disk/Memory/You? Cheers Sriram Kumar -----Original Message----- From: oracle-l-bounce@freelists.org on behalf of Guang Mei Sent: Fri 4/30/2004 1:26 AM To: Oracle-L-freelists Cc: Subject: a pl/sql question Hi: I have the following pl/sql function code that gets called a lot in our application. I have looked all the querys and they are running fine (have proper indexes, etc). I am now trying to see if I could optimize this code a bit. One thing I am thinking is that the sub pl/sql block within this function is creating some "overhead". But if I put the code "select abbrev into ..." in the function directly (instead of in pl/sql block) then how could I capture it's exception ("when others then select name into jname from journal where id=jid;")? Please let me know you have some ideas. Thanks. Guang --- here is the code snip: begin select title, volume, pages, year, journalid into ttl, vol, pg, yr, jid from ref where id=rn; begin select abbrev into jname from journal2abbrev where id=jid and medline='Y'; exception when others then select name into jname from journal where id=jid; end; str := str || ' ' || getAuthorString(rn,maxAuthLen) || ' ' || ttl || ' '; str := str || jname || ' ' || to_char(vol) || ', ' || pg; str := str || ' (' || to_char(yr) || ').'; return str; exception when others then return null; end RefString; ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- -- Binary/unsupported file stripped by Ecartis -- -- Type: application/ms-tnef -- File: winmail.dat ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------