Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Fw: (re): Really Wierd Query tuning issue

Fw: (re): Really Wierd Query tuning issue

From: ryan gaffuri <ryan.gaffuri_at_comcast.net>
Date: Thu, 12 Aug 2004 19:24:49 -0400
Message-ID: <003d01c480c3$902a0ef0$350a5444@ryan2le36ofjce>

> Ryan,
>
> I'd be interested in seeing the output from a 10200 trace for each query.
This trace shows the consistent reads that the session is
> making. By looking at the data block addresses, it should indicate what
data it is actually looking at. If the execution plans are
> the same (run a 10046 level 12 at the same time), I'm at a loss as to how
to explain the change by including the ORDER BY.
>
> You can use the following commands to set the trace
> alter session set max_dump_file_size = unlimited;
> alter session set events '10046 trace name context forever, level 12';
> alter session set events '10200 trace name context forever';
>
> Regards,
> Daniel Fink
>
> ryan gaffuri wrote:
> > yup.. over and over again. made sure it was compiled. Wierd thing is
that I
> > get a reduction in LIOs with the same plan if use an 'order by ' on the
> > value in the index.
> >
> > really wierd.
> >
> > clustering factor should not be a factor in a 56 row table since all the
> > index data should fit in one block. Its just 1 number field in the
index.
> > ----- Original Message -----
> > From: "Daniel Fink" <Daniel.Fink_at_Sun.COM>
> > To: <oracle-l_at_freelists.org>
> > Sent: Thursday, August 12, 2004 3:41 PM
> > Subject: Re: Really Wierd Query tuning issue
> >
> >
> >
> >>Ryan,
> >>
> >>Did you run the second query shortly after the first? What happens if
you
> >
> > run the first query over and over? Are the logical I/Os of
> >
> >>the table/index or could they be related to parsing the statement?
> >>
> >>As for clustering factor, IIRC, that comes into play only when you are
> >
> > dealing with index range or full scans. For a unique scan, it
> >
> >>will return 1 index entry that points to 1 table row. For other scans,
the
> >
> > CBO uses it to calculate how many table blocks will I
> >
> >>have to visit if I read a certain number of index entries. For example,
a
> >
> > high clustering factor tells the CBO that the index
> >
> >>entries returned by the range scan (say 10% of the total) will require
> >
> > visiting a high number of table blocks (say 80%), so a full
> >
> >>table scan may be more efficient.
> >>
> >>Regards,
> >>Daniel
> >
> >
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to: oracle-l-request_at_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
> > -----------------------------------------------------------------
>



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_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
-----------------------------------------------------------------
Received on Thu Aug 12 2004 - 18:20:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US