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:[EMAIL PROTECTED]] > 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
