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:[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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams 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).
