Re: DBMS_STATS cannot analyze clusters

From: onedbguru <onedbguru_at_yahoo.com>
Date: Thu, 7 Apr 2011 19:20:34 -0700 (PDT)
Message-ID: <b63f52a5-f4aa-47f1-90ad-dc71679ef7e6_at_s9g2000yqm.googlegroups.com>

On Apr 7, 11:54 am, Mladen Gogala <n..._at_email.here.invalid> wrote:
> 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

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,    <<<<<------------------??????????

   cascade => TRUE);
end;
/ Received on Thu Apr 07 2011 - 21:20:34 CDT