Kevin,
It would be very interesting to know if when you have
the explain plan doing the sort/merge if setting
num_rows to the integer value of the current num_rows
(using dbms_stats, see note 130899.1) causes the
explain plan to change to N/L.
- Anita
- "Toepke, Kevin M" <ktoepke_at_cms.cendant.com> wrote:
> Anita:
>
> I am saying that sometimes when the num_rows is not
> an integer oracle will
> sort/merge join 2 multi-million row tables when a
> nested-loop join will
> return much quicker. If a compute stats is done (or
> an estimate stats
> returns integer values for the num_rows) then the
> optimzer chooses the
> nested-loops join.
>
> I've been going through the bugs database and it
> appears that this may be
> related to several other bugs that seem to hint at
> Oracle 8 and 8i
> excessively choosing the sort/merge join operation.
>
> Kevin
>
> -----Original Message-----
> Sent: Thursday, June 14, 2001 4:20 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Kevin,
>
> You might want to check out bug 1540560 "ANALYZE
> TABLE
> ESTIMATE STATISTICS NOT ROUNDING VALUE FOR NUM_ROWS"
> closed as "not a bug."
>
> Are you saying that the CBO produces a different
> explain plan for a table with NUM_ROWS = 5000000.25
> vs. NUM_ROWS= 5000000? This would definitely be a
> bug
> and should be fairly easy to reproduce since one can
> use DBMS_STATS to set a specific value.
>
> HTH,
>
> -- Anita
>
>
> --- "Toepke, Kevin M" <ktoepke_at_cms.cendant.com>
> wrote:
> > That's what the doc says. However, at least with
> > Oracle 8.0.5 through
> > 8.1.7.2.0 on Solaris, if you do not explictily
> > compute statistics, the
> > NUM_ROWS column in DBA_TABLES and DBA_INDEXES has
> a
> > decimal value. (yup the
> > table has 32.345 rows in it.)
> >
> > This may not seem like a big deal until you try to
> > tune the queries that
> > reference these tables/indexes. Man, do the
> > exectution plans get screwed up
> > (how about a FTS against a 5million row table in a
> > nested-loops operation!)
> >
> > I've submitted a TAR on this and am "working with"
> > Oracle support.
> >
> > Kevin
> >
> > -----Original Message-----
> > Sent: Wednesday, June 13, 2001 1:41 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > On Tuesday 12 June 2001 15:05, Kevin Kostyszyn
> > wrote:
> > > Couldn't you try and estimate about 50% of it?
> > It's not as reliable, but
> > > it would probably work.
> >
> > Actually, Oracle will do a 'compute statistics'
> when
> > you attempt to do
> > an 'estimate statistics' on 50% or more.
> >
> > Jared
> > --
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Toepke, Kevin M
> > INET: ktoepke_at_cms.cendant.com
> >
> > Fat City Network Services -- (858) 538-5051
> FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be
> removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
> __________________________________________________
> Do You Yahoo!?
> Spot the hottest trends in music, movies, and more.
> http://buzz.yahoo.com/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: A. Bardeen
> INET: abardeen1_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Toepke, Kevin M
> INET: ktoepke_at_cms.cendant.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Do You Yahoo!?
Spot the hottest trends in music, movies, and more.
http://buzz.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: A. Bardeen
INET: abardeen1_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Jun 14 2001 - 07:52:20 CDT
