DBMS_STATS cannot analyze clusters

From: Mladen Gogala <no_at_email.here.invalid>
Date: Thu, 7 Apr 2011 15:54:55 +0000 (UTC)
Message-ID: <pan.2011.>

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:

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

Received on Thu Apr 07 2011 - 10:54:55 CDT

Original text of this message