Re: DBMS_STATS cannot analyze clusters
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 12 Apr 2011 17:41:21 +0100
Message-ID: <MdednTqRYrc6HTnQnZ2dnUVZ8vGdnZ2d_at_bt.com>
"Mladen Gogala" <no_at_email.here.invalid> wrote in message news:pan.2011.04.07.15.54.55_at_email.here.invalid...
>
> 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.
>
Date: Tue, 12 Apr 2011 17:41:21 +0100
Message-ID: <MdednTqRYrc6HTnQnZ2dnUVZ8vGdnZ2d_at_bt.com>
"Mladen Gogala" <no_at_email.here.invalid> wrote in message news:pan.2011.04.07.15.54.55_at_email.here.invalid...
>
> 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.
>
Are you sure it was left out ?
gather_schema_stats will happily gather stats on a cluster - although it
has the oddity that the only way to collect stats on the cluster index of
the index cluster really IS 'analyze index ...'.
All the other indexes, and all the tables in the cluster, should be subject
to the ordinary staleness measure.
Is it possible that something strange happens to all the tables in cluster
if any one of the tables doesn't register as stale perhaps ?
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.comReceived on Tue Apr 12 2011 - 11:41:21 CDT