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).
Received on Wed Oct 31 2001 - 17:54:11 CST