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).
