Hi John,
If the CBO needs to access a table that has no statistics, it does not actually
estimate the statistics. It just gets
the high water mark by reading the segment header block and uses its default
assumption of an average row length of 100
bytes to estimate the cardinality of the table.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/ - For DBAs
@ http://www.christianity.net.au/ - For all
-----Original Message-----
Sent: Thursday, 1 November 2001 10:10
To: Multiple recipients of list ORACLE-L
Greg,
I may be way off here but FIRST_ROWS will not only force the CBO to be used
on SYS objects, it will *estimate* statistics on all related objects. This
will result in large elapsed times and I/O during the parse phase (for the
ESTIMATE part) as well as the incorrect path and resultant extra I/O during
the execute phase.
By extension, if you see large values in the parse phase of a tkprof output,
it may indicate that stats are being estimated and thus some objects have
not been analyzed. (Can someone validate this? Hint, hint: Steve :)
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
Listen to great commercial-free christian music 24x7 at www.klove.com
- The opinions and statements above are entirely my own and not
those of my employer or clients **
> -----Original Message-----
> From: A. Bardeen [mailto:abardeen1_at_yahoo.com]
> Sent: Wednesday, October 31, 2001 3:30 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Synonyms can be VERY bad for performance
>
>
> Greg,
>
> Hey! I resemble that remark ;)
>
> The data dictionary views are optimized to use the RBO
> or are heavily hinted to force a specific access path.
> ALL_ROWS and FIRST_ROWS force the CBO to be used so a
> different access path may be taken resulting in poor
> performance.
>
> Obligatory notes:
>
> Note: 35272.1 "Is ANALYZE on the Data Dictionary
> Supported (TABLES OWNED BY SYS)?"
>
>
> Note: 35934.1 "TECH: Cost Based Optimizer - Common
> Misconceptions and Issues"
>
> Note: 66484.1 "Which Optimizer is used"
>
> HTH,
>
> -- Anita
>
> --- Greg Moore <sqlgreg_at_pacbell.net> wrote:
> > > Because we were in first_rows, queries
> > > against the data dictionary were
> > > optimized in first_rows mode rather than rule.
> > > This was despite us not having any statistics
> > > on system or sys objects.
> >
> > Is this standard behavior?
> >
> > What about the warnings not to analyze SYS because
> > it's optimized for Rule?
> > Is it true that simply invoking first_rows means
> > that's out the window and
> > you get the CBO used on SYS anyway, even if there
> > are no statistics?
> >
> > I get the feeling we're going to see an email from
> > Anita saying this is bug
> > number XXX, fixed in patch YYY, and we can read all
> > about it in Note ZZZ.
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Greg Moore
> > INET: sqlgreg_at_pacbell.net
> >
> > 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!?
> Make a great connection at Yahoo! Personals.
> http://personals.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: John Kanagaraj
INET: john.kanagaraj_at_hds.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: Steve Adams
INET: steve.adams_at_ixora.com.au
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 Wed Oct 31 2001 - 23:17:20 CST
