From oracle-l-bounce@freelists.org Wed Oct 13 12:39:34 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i9DHdY427374 for ; Wed, 13 Oct 2004 12:39:34 -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 i9DHdYI27369 for ; Wed, 13 Oct 2004 12:39:34 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B0FEF72CD91; Wed, 13 Oct 2004 12:45:31 -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 29552-86; Wed, 13 Oct 2004 12:45:31 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1691972CD60; Wed, 13 Oct 2004 12:45:31 -0500 (EST) Message-ID: Date: Wed, 13 Oct 2004 10:43:48 -0700 From: Jared Still To: breitliw@centrexcc.com Subject: Re: Sincere Advice on Sql Plan - Thanks Cc: smishra_97@yahoo.com, oracle-l@freelists.org In-Reply-To: <6.1.2.0.2.20041013110611.025d4478@pop.centrexcc.com> Mime-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit References: <20041012184238.88393.qmail@web51305.mail.yahoo.com> <6.1.2.0.2.20041013110611.025d4478@pop.centrexcc.com> X-archive-position: 11030 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jkstill@gmail.com Precedence: normal Reply-To: jkstill@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org On Wed, 13 Oct 2004 11:14:52 -0600, Wolfgang Breitling wrote: > Actually it looks as if 1 row is returned per execution: 3262 parses, 3262 > executes, 3262 fetches and 3262 rows returned. If it is correct that every D'oh! Yup, that was pretty obvious. Jared > At 10:45 AM 10/13/2004, Jared Still wrote: > >Yup, lots of parsing. > > > >If you eliminate all the parsing, you will have saved > >.32 seconds on a 3.78 second query. > > > >Do you think the user will notice? > > > >The query is spending 2.17 seconds retrieving 26472 rows > >of data, only 3262 of which you are using. > > > >26472 rows fetched / 120 fetches = 220 rows per block. > > > >3262 rows retrieved / 120 fetches = 27 rows per block > > > >The data being retrieved is fairly well scattered across the table. > > > >Perhaps a different index is in order? > > > >Or maybe the query is limited by the design of the table? > > > >Or the query is malformed? > > > >Anyway, reducing IO would be in order here if possible. > > > >Lots of maybes. > > > >You might consider running a 10046 trace on the user running > >this form, and find out where and why the time is being used. > > > >Tkprof does not provided sufficient detail other than what is > >needed to drive lots of speculation. :) > > > > Wolfgang Breitling > Oracle7, 8, 8i, 9i OCP DBA > Centrex Consulting Corporation > http://www.centrexcc.com > > > > -- > http://www.freelists.org/webpage/oracle-l > -- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist -- http://www.freelists.org/webpage/oracle-l