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 <jonathan_at_jlcomp.demon.co.uk> 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 <ORACLE-L_at_fatcity.com>
> 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: jonathan_at_jlcomp.demon.co.uk
>
> 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!?
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: wisernet100_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).
Received on Fri Apr 26 2002 - 07:23:22 CDT
