Fw: (re): Really Wierd Query tuning issue
- From: "ryan gaffuri" <ryan.gaffuri@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 12 Aug 2004 19:24:49 -0400
----- Original Message -----
From: "Daniel Fink" <Daniel.Fink@xxxxxxx>
To: <ryan.gaffuri@xxxxxxxxxxx>
Sent: Thursday, August 12, 2004 6:21 PM
Subject: Re: (re): Really Wierd Query tuning issue
> 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@xxxxxxx>
> > To: <oracle-l@xxxxxxxxxxxxx>
> > 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@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at //www.freelists.org/archives/oracle-l/
> > FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » Fw: (re): Really Wierd Query tuning issue
