RE: Forever running Analyze

From: Toepke, Kevin M <ktoepke_at_cms.cendant.com>
Date: Thu, 14 Jun 2001 05:10:49 -0700
Message-ID: <F001.00329362.20010614051022@fatcity.com>

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).

Received on Thu Jun 14 2001 - 07:10:49 CDT