From abardeen1@yahoo.com Wed, 31 Oct 2001 15:10:11 -0800 From: "A. Bardeen" Date: Wed, 31 Oct 2001 15:10:11 -0800 Subject: Re: Synonyms can be VERY bad for performance Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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 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@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@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@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@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).