Re: DBMS_STATS cannot analyze clusters
Date: Thu, 21 Apr 2011 17:57:31 +0000 (UTC)
Message-ID: <pan.2011.04.21.17.57.31_at_email.here.invalid>
On Wed, 20 Apr 2011 15:33:59 -0700, Randolf Geist wrote:
> Can you tell us your database version? I couldn't reproduce on 10.2.0.5.
It is 10.2.0.5, 64 bit, with CPU Oct 2010 last applied.
>
> 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.
I had a query that didn't get the right plan until the cluster was analyzed with the ANALYZE command. Database stats is running weekly and it did analyze the tables, but it apparently didn't analyze the cluster.
-- http://mgogala.byethost5.comReceived on Thu Apr 21 2011 - 12:57:31 CDT