Re: Compute statisics

Any stat missing in a CBO situation will be set to the hard-coded default values found in the source code. One example (out of about 40 or so values) is default selectivity for equality predicates. Before 7.3 it was 0.125 (meaning that a where= condition would assume you needed to scan 1/8th of the table). In 7.3 and later it was fortunately set to 0.01 (1%). Another of my favorite default values is number of leaf blocks in an index. It's 25.

Connor McDonald wrote:

> Hi Jared,
>
> My post was to meant to imply that (say) if (in a
> single query - and thats the important bit I missed
> out) there were two tables, one with stats, and one
> without, then the one without will use defaults (as
> quoted in chapter 8 of the same manual)
>
> I was trying to imply that if you've only got half the
> stats, then on a query-by-query basic, you're most
> likely to toggle between
>
> - 'good' CHOOSE (since all the stats are present for
> the objects in this query)
> - 'bad' CHOOSE (since some defaults are chosen for
> object missing statistics)
> - RULE (for queries where all objects have no stats)
>
> Cheers
> Connor
>
> --- Jared Still <jkstill_at_cybcon.com> wrote: > On
> Tuesday 05 June 2001 01:20, Connor McDonald
> > wrote:
> > > Sounds like nonsense to me...If you have "half"
> > > statistics, then the optimizer is going to use
> > > defaults for the others,
> >
> > Conner,
> >
> > Are you sure about that?
> >
> > From the Optimizer chapter in the tuning manual:
> >
> > If the data dictionary contains statistics for at
> > least one of the accessed
> > tables, then the optimizer uses a cost-based
> > approach and optimizes with a
> > goal of best throughput. If the data dictionary
> > contains no statistics for
> > any of the accessed tables, then the optimizer uses
> > a rule-based approach.
> > This is the default value for the parameter.
> >
> > ...
> >
> > If the optimizer uses the cost-based approach for a
> > SQL statement, and if
> > some tables accessed by the statement have no
> > statistics, then the optimizer
> > uses internal information (such as the number of
> > data blocks allocated to
> > these tables) to estimate other statistics for these
> > tables.
> >
> >
> > That coupled with the fact that when viewing the
> > plan table you can see
> > the CBO's use of the statistic would make me think
> > otherwise.
> >
> > Is there some documentation that I missed that
> > supports the CBO using
> > defaults for analyzed tables when some of them are
> > _not_ analyzed?
> >
> > Or did I just misunderstand your post?
> >
> > Jared
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Jared Still
> > INET: jkstill_at_cybcon.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).
>
> =====
> Connor McDonald
> http://www.oracledba.co.uk (mirrored at
> http://www.oradba.freeserve.co.uk)
>
> "Some days you're the pigeon, some days you're the statue"
>
> ____________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
> or your free @yahoo.ie address at http://mail.yahoo.ie
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?Connor=20McDonald?=
> INET: hamcdc_at_yahoo.co.uk
>
> 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).

--
Venlig hilsen

Mogens Nørgaard

Technical Director
Miracle A/S, Denmark
Web: http://MiracleAS.dk
Mobile: +45 2527 7100




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mogens =?iso-8859-1?Q?N=F8rgaard?=
  INET: mno_at_MiracleAS.dk

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