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.04.07.15.54.55_at_email.here.invalid>

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

-- 
http://mgogala.byethost5.com

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