DBMS_STATS cannot analyze clusters
Mladen Gogala
key. The common wisdom tells me that an index cluster is adequate for
such situation. The performance of the application did improve as a
result. The problem is with analyzing the cluster. DBMS_STATS cannot do
that:
1 select object_type from dba_objects
2* where owner= 'UAT_INSIGHT4' and object_name='TAG_CLU'
SQL> /
OBJECT_TYPE
-------------------
CLUSTER
Elapsed: 00:00:00.14
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'UAT_INSIGHT4',
tabname => 'TAG_CLU',
estimate_percent => 10,
method_opt => 'FOR TABLE FOR ALL INDEXED COLUMNS SIZE 254',
degree => 4,UE); => 4,
cascade => TRUE);
end;
/
8 9 10 begin
*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "UAT_INSIGHT4"."TAG_CLU", insufficient
privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 15017
ORA-06512: at "SYS.DBMS_STATS", line 15049
ORA-06512: at line 2
Elapsed: 00:00:00.10
SQL> show user
USER is "SYSTEM"
SQL>
SQL> analyze cluster uat_insight4.tag_clu
2 estimate statistics sample 5 percent
3 for table for all indexed columns size 254;
Cluster analyzed.
Elapsed: 00:00:28.92
SQL>
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.
onedbguru
M...
Not sure if your cut/paste was wrong or you actually have a type-o in
your code, but the error points to this line:
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'UAT_INSIGHT4',
tabname => 'TAG_CLU',
estimate_percent => 10,
method_opt => 'FOR TABLE FOR ALL INDEXED COLUMNS SIZE 254',
degree => 4,UE); => 4, <<<<<------------------??????????
Mladen Gogala
> Not sure if your cut/paste was wrong or you actually have a type-o in
> your code, but the error points to this line:
Cut/paste error. The code was OK.
John Hurley
# I have 3 tables which are always queried together and joined on the
same key. The common wisdom tells me that an index cluster is adequate
for such situation. The performance of the application did improve as
a result. The problem is with analyzing the cluster. DBMS_STATS cannot
do that:
I think the phrase "common wisdom" involving creating a cluster is
stretching it a bit. I have been around the block quite a while and
believe me clusters are pretty uncommon. Some authors talk about
using them and do recommend them for certain situations but in the
universe of people designing custom systems ... not often considered
AFAIK.
There is a whole set of history involving stats on clusters that for
the most part I am only vaguely aware of. It is probably release
dependent how well dbms_stats works with clusters and may also have
some issues with stats in parallel?
Do you have any monitoring in place that looks for objects that have
old statistics? Depending on the philosophy that you have in place
for when and if stats are gathered ( or not ) some tweaks to the
monitoring and exception reporting process could be looked at.
Jonathan Lewis
news:pan.2011.04...@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.com
Randolf Geist
> 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
Mladen Gogala
> 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.
John Hurley
# 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.
Mladen:
... I had a query that didn't get the right plan until the cluster was
analyzed with the ANALYZE command.
Can you come up with a reproducible test case?
Something that proves the CBO does use statistics generated by ANALYZE
that dbms_stats apparently does not populate?