From oracle-l-bounce@freelists.org Fri Apr 1 00:39:50 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j316do4L001754 for ; Fri, 1 Apr 2005 00:39:50 -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 j316dnem001748 for ; Fri, 1 Apr 2005 00:39:49 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CB4C98ACD9; Fri, 1 Apr 2005 00:37:52 -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 26087-09; Fri, 1 Apr 2005 00:37:52 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 51CED89A48; Fri, 1 Apr 2005 00:37:52 -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=Ne6BwHg/kqX2wpzeCtoxeCVoCyU1ci3EbTZzA8PMBtRT7oGqRyINp3DosrD+5L5QKB4sTMg8c186RSI5MKg0Bq8khm2qWDacGGLWLXMZpno5zULb0RJZ4YWL89H+idK0W/G6gl7USvqC7jOnkiStoxv8gh5Fgyh2bU2LeL0jTwY= Message-ID: <7765c89705033121363b1683fb@mail.gmail.com> Date: Fri, 1 Apr 2005 06:36:05 +0100 From: Niall Litchfield To: The Human Fly Subject: Re: reducing LIO's Cc: oracle-l@freelists.org In-Reply-To: <97b7fd2f0503310656498a99eb@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> <7765c897050331064628655477@mail.gmail.com> <97b7fd2f0503310656498a99eb@mail.gmail.com> X-archive-position: 17957 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.0 required=5.0 tests=AWL autolearn=ham version=2.60 X-Spam-Level: comments in-line On Mar 31, 2005 3:56 PM, The Human Fly wrote: > Well, I have done it. I have created combination index on those two > colums and when my query was not using the index, I have even forced > it to use, it reduces the cost but buffer_get were very high. curious, if the cost using the index were really lower than the cost for the plan without the index, then the CBO will pick that execution plan. If however the CBO didn't use an index that I thought it should then my first reaction would not be, lets see if we can get it to use the index, but who is missing some information here me or the optimizer (usually it turns out to be me). in your case however it may be the cbo. The cbo reckons your query will return 2000 rows, but in fact it is only returning 54. (so it is wrong by a factor of 37) - investigating why this is so will - as wolfgang has suggested - likely be helpful. > My > question was, LIO can be reduced by creating indexs, if so, why my > buffer_gets were higher with INDEX hint? lio might be reduced or might be increased by using indexes > Well, we can't avoid this query as it is mandatory query for every > trading transaction. You might still be able to avoid the sort if the order by is not mandatory. -- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.com -- http://www.freelists.org/webpage/oracle-l