From oracle-l-bounce@freelists.org Thu Mar 31 09:49:44 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j2VFniT7014333 for ; Thu, 31 Mar 2005 09:49:44 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j2VFnhem014329 for ; Thu, 31 Mar 2005 09:49:43 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 30A008B1F4; Thu, 31 Mar 2005 09:47:44 -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 24352-04; Thu, 31 Mar 2005 09:47:44 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A8D3D8B218; Thu, 31 Mar 2005 09:47:43 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=WWId1x4AhVPiY5z3EO2zXl0tLqAMDSVDvXH3NvdZqRnaHDzFYQLCzx7lPN12hE5g+krijix6lNqJcs5hFEo/1zKMuOQQj6EZU1BwJzbfLAG48e7sUs2JVfhdAWpdLGkrujlXpxZkYck93b6+tWGQNW1enEWILtlq0XjeyayyA5o= Message-ID: <7765c897050331064628655477@mail.gmail.com> Date: Thu, 31 Mar 2005 15:46:00 +0100 From: Niall Litchfield To: sjaffarhussain@gmail.com Subject: Re: reducing LIO's Cc: "Mercadante, Thomas F" , oracle-l@freelists.org In-Reply-To: <97b7fd2f05033106333cc68926@mail.gmail.com> Mime-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit References: <97b7fd2f05033106244c2d1ad7@mail.gmail.com> <97b7fd2f05033106333cc68926@mail.gmail.com> X-archive-position: 17914 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: niall.litchfield@gmail.com Precedence: normal Reply-To: niall.litchfield@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.2 required=5.0 tests=AWL,UPPERCASE_25_50 autolearn=no version=2.60 X-Spam-Level: Jaffar wrote On Thu, 31 Mar 2005 17:33:18 +0300, The Human Fly wrote: > Here is the query and its execution plan, > > Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value > --------------- ------------ -------------- ------ -------- --------- ---------- > 18,305,779 1,771 10,336.4 9.4 176.37 251.04 2163204450 > Module: JDBC Thin Client > BEGIN PROC_APP_OW_ORD(); END; > > 18,168,431 1,768 10,276.3 9.3 170.39 240.53 3569511138 > Module: JDBC Thin Client > SELECT ORDT_APPR_DT, ORDT_ORD_NB, ORDT_TRN_NB, ORDT_MKT_ID , ORDT_STS, > ORDM_STS, ORDT_EXCH_ID, ORDM_SUB_ID, ORDM_ORDT_NB > FROM ORDT,ORDM > WHERE ORDM_ORD_NB=ORDT_ORD_NB > AND ORDT_STS='K' AND ORDM_STS IN ('C','G','L') > ORDER BY ORDT_APPR_DT,ORDT_ORD_NB well it looks like you might get some benefit from a compound index on (ORDM_ORD_NB,ORDM_STS) - especially of ORDM_ORD_NB is unindexed. However your query appears to be executing (on average) in 0.14s (251/1771), rather than focus on tuning the query, I'd be interested in why you are executing it so frequently, just as in life if you can avoid doing the work at all and still meet your requirements so much the better. -- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.com -- http://www.freelists.org/webpage/oracle-l