Re: DBMS_STATS cannot analyze clusters
Date: Wed, 20 Apr 2011 15:33:59 -0700 (PDT)
Message-ID: <3f284943-a810-425e-aff9-5b5047fc81ef_at_l36g2000vbp.googlegroups.com>
On 7 Apr., 17:54, Mladen Gogala <n..._at_email.here.invalid> wrote:
> The plain, old "ANALYZE" works like a charm. It seems that our weekly
> stats, which runs dbms_stats.gather_database_stats_job_proc doesn't do a
> swell job with clusters. It seems that all clusters are simply left out
> from the list of objects for which the statistics is collected. I did
> have a problem with one plan, because of the incorrect statistics. So,
> ladies and gentlemen, you'll have to set up a job that will analyze your
> clusters manually.
Can you tell us your database version? I couldn't reproduce on 10.2.0.5.
Both GATHER_SCHEMA_STATS as well as the GATHER_DATABASE_STATS_JOB_PROC happily analyzed all relevant objects including the cluster index. The staleness of the cluster index is determined by the staleness of any of the member tables of the cluster, so if at least one table is determined to be stale then the cluster index is also re-analyzed.
Note that DBMS_STATS actually leaves out the CLUSTER object itself which is analyzed by ANALYZE but the CBO does not seem to use the statistics generated by ANALYZE on the cluster itself. The relevant statistics for some operations like NESTED LOOP joins using the cluster index in the inner row source are taken from the cluster index itself, so as long as this is analyzed that should be fine.
You've used some non-default options for your ANALYZE CLUSTER example like generating histograms on all indexed columns - could it be that something like that made a difference to the statistics automatically generated by DBMS_STATS?
Have you evidence from the STALE_STATS info that the statistics of the cluster index are not maintained properly - I would think that this is the most relevant part of the statistics for the index cluster. Note that it is not covered by a manual DBMS_STATS.GATHER_TABLE_STATS even when using CASCADE=>TRUE for one of the member tables of the cluster.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Wed Apr 20 2011 - 17:33:59 CDT