Oracle-L: Re: DBMS_STATS.gather_database

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 26 Apr 2002 00:08:25 -0800
Message-ID: <F001.00450445.20020426000825@fatcity.com>

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

Received on Fri Apr 26 2002 - 03:08:25 CDT

Re: DBMS_STATS.gather_database_stats