possibly an Urban Legend, but the theory I've always worked under for why I shouldn't analyze SYS is that Oracle wrote the code that accesses the data dictionary under the RBO, optimized it for RULE and hasn't gone back and redone it.
If Oracle is like any of the companies I've worked for (and I can't see why they wouldn't be in this case), they work first on code that is "visible" to the end user, that will generate income. Then, if and only if there is time, they go back and fix old code. No one ever goes back and fixes old code, especially since the easiest workaround for Oracle on this is "we TOLD you not to analyze SYS" Now, there has been a rumor for years that RBO is going away. If this eventually becomes a reality I would think that Oracle would have to rewrite the kernel code accessing the data dictionary and then the new pronouncement will be "we TOLD you to analyze SYS" Rachel --- Jonathan Lewis <[EMAIL PROTECTED]> wrote: > > You're quite right that many sites vastly over-analyze > their databases; but I'm beginning to wonder if the > "Don't analyze SYS" mantra is heading for Urban > Legend status. > > In theory, CBO is supposed to be at least as good > as RBO for any data acquisition, given suitable > statistics, so why should SYS be a special case ? > > There are, of course, a couple of problems. > > The first being the use of v$ and x$ objects in > dictionary views, and since these can't have > 'normal' statistics Oracle can make terrible mistakes > in the path. > > The second is that the SYS schema make a lot > of use of synthetic keys generated by sequences, > and objects of that type need to have their statistics > handled with care. I haven't thought about it in > detail, but there are probably a couple of column > in the SYS schema which would need histograms > if you chose to run it cost-based. > > My comments about the SYS schema are, however, > biased by the fact that I insist that the data dictionary > is an application created by Oracle Corp. for Oracle Corp, > and end-users and dba should not expect their > personal use of the data dictionary to be optimal > under all conditions. This gives me a bit of a cop-out > for complaints like : "But this report against > user_ind_columns > user_indexes > user_constraints > user_constraint_cols > user_tables > runs perfectly under rule-based and dies under > cost-based". > > > > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Author of: > Practical Oracle 8i: Building Efficient Databases > > Next Seminar - Australia - July/August > http://www.jlcomp.demon.co.uk/seminar.html > > Host to The Co-Operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > > -----Original Message----- > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: 25 April 2002 19:46 > > > |Walter, > | > |You might want to check the following Bug and ML note. I covered > this > in my > |recent paper on the CBO at IOUG - cut and paste below: > | > |MYTH: "ANALYZE THE ENTIRE DATABASE, INCLUDING SYS" > |This is usually the result of an over-enthusiastic move to the CBO. > The > |internal Data dictionary (mostly owned by SYS) is heavily optimized > for the > |RBO, a carry over from the days of Oracle 6 when RBO was the only > child in > |the family. Many Data dictionary views are hinted by RULE, but some > are not. > |Messing around with them is not good for the health of the Database! > On a > |more serious note, Database deadlocks have been known to occur when > |analyzing the SYS schema as rows being inserted into the > Histogram-related > |internal tables lock themselves out. For further details, refer to > Metalink > |Note 35272.1. > |An interesting side note to this myth is the hidden issue with the > |DBMS_UTILITY.ANALYZE_DATABASE procedure. Invocation of this in-built > |package/procedure used to generate statistics for all users, > including SYS. > |Bug 969814, released as late as 8.1.7, fixes ANALYZE_DATABASE so it > does not > |analyze the dictionary tables FET$ and UET$. > | > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jonathan Lewis > 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!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
