Re: DBMS_STATS "Missing expression"

Andreas Sheriff wrote:
>
> <fitzjarrell_at_cox.net> wrote in message
> news:1125007097.164339.168450_at_g14g2000cwa.googlegroups.com...
> >
> > Andreas Sheriff wrote:
> > > "Mark D Powell" <Mark.Powell_at_eds.com> wrote in message
> > > news:1124996740.359404.158710_at_g14g2000cwa.googlegroups.com...
> > > > Only thing I found of interest on metalink was bug 3491127 (document
> > > > id) which appears to be for 9.2 where a multi-columned index on a
> > > > partitioned table could produce this error
> > > >
> > > > Have you verified that no one reran catproc under the wrong id? If
> > > > some of the sys owned objects were invalid or the public synonyms
> > > > pointed to the wrong owner then cleaning up the mess should resolve
> > > > this, but only if someone messed up.
> > > >
> > > > That is all I can think of other than trying to get around the
> problem,
> > > > assuming it is reproducing, by generating gather_table_stats calls for
> > > > the schema.
> > > >
> > > > HTH -- Mark D Powell --
> > > >
> > >
> > > No one re-ran catproc under any id.
> > >
> > > We are not using partitions.
> > >
> > > Doing a SELECT DISTINCT STATUS FROM DBA_OBJECTS;
> > > returns only
> > > STATUS
> > > ---------
> > > VALID
> > >
> > > The error is reproduced using:
> > > EXEC DBMS_STATS.GATHER_SCHEMA_STATS( ownname=>'SCHEMANAME' );
> > >
> > > However, I do remember
> > > creating SYSTEM.PLAN_TABLE (using the SYSTEM account),
> > > creating a PUBLIC SYNONYM PLAN_TABLE FOR SYSTEM.PLAN_TABLE (using the as
> > > sysdba privileges) and
> > > granting appropriate privileges for PLAN_TABLE to public.
> > >
> > > Querying dba_tab_privs, I see that the privileges granted to PUBLIC for
> > > PLAN_TABLE are ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE, and
> REFERENCES.
> > >
> > > That can't be what's $cr3wing up DBMS_STATS, is it?
> > >
> > > --
> > > Andreas
> > > Oracle 9i Certified Professional
> > > Oracle 10g Certified Professional
> > > Oracle 9i Certified PL/SQL Developer
> > >
> > >
> > > "If you don't eat your meat, you cannot have any pudding.
> > > "How can you have any pudding if you don't eat your meat?!?!"
> > > ---
> > >
> > > WARNING:
> > > DO NOT REPLY TO THIS EMAIL
> > > Reply to me only on this newsgroup
> >
> > You could drop the table and find out. You could also trace the
> > session running dbms_stats.
> >
> >
> > David Fitzjarrell
> >
>
> How silly of me to overlook tracing.
>
> Here's what I found in the trace file after running EXEC
> DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'OBJNAME'); :
>
> =====================
> PARSE ERROR #7:len=433 dep=1 uid=0 oct=3 lid=0 tim=0 err=936
> select /*+ */ count(*) CNT,count(PIN),count(distinct
> PIN),avg(nvl(ceil(length(ltrim(rtrim(rtrim(to_char(PIN,'9.999999999999999999
> 999999999999999999999EEEE'),'+-0123456789'),'E0.')))/2+2),1)),min(PIN),max(P
> IN),count(group),count(distinct
> group),avg(nvl(ceil(length(ltrim(rtrim(rtrim(to_char(group,'9.99999999999999
> 9999999999999999999999999EEEE'),'+-0123456789'),'E0.')))/2+2),1)),min(group)
> ,max(group) from "CLASSIFIEDXXXX"."ABR_PINS"
> EXEC #1:c=0,e=0,p=0,cr=3537,cu=12,mis=0,r=0,dep=0,og=4,tim=0
> ERROR #1:err=936 tim=0
> *** 2005.08.25.23.58.13.000
> =====================
>
> Ooops... I also created another table called ABR_PINS. :-D
>
> But, what the heck is up with that hint?
>
> Here's what I did,
> I tried running
> EXEC DBMS_STATS.GATHER_TABLE_STATS()
> for that table, but still got the missing expression error.
>
> I ran
> ANALYZE [TABLE,INDEX] COMPUTE STATISTICS;
> That ran fine.
>
> Tried again,
> EXEC DBMS_STATS.GATHER_TABLE_STATS()
> Still got the missing expression error.
>
> The table is nothing special. It's defined as:
>
> CREATE TABLE abr_pins (
> pin number(6),
> "group" number(1) CHECK "group" in(1,2,3),
> CONSTRAINT pk_abr_pins PRIMARY KEY (pin,group));
>
> Am I missing something here?
> (Forgive the syntax, and no comments about naming col2 "group". I know, I
> know :)
>
> Or maybe that could be the problem...
>
> I can't test a solution till the table is out of use, though.
>
> In fact, I'm VERY sure that is the problem. DAMN!
>
> "group" is referenced so many times in that select satement.
>
> There is external code depending on the name group.
> I know how to solve this. I'll rename the table to something like
> base_abr_pins, rename the column "group" to "groupno" and create a view
> called abr_pins for existing applications to use. DBMS_STATS won't touch
> views when it's doing its analysis.
>
> Ok, I guess I deserve a little-bit-o-chiding.
> --
>
> Andreas Sheriff
> Oracle 9i Certified Professional
> Oracle 10g Certified Professional
> Oracle 9i Certified PL/SQL Developer
> ----
> "If you don't eat your meat, you cannot have any pudding.
> "How can you have any pudding, if you don't eat your meat?"
>
> DO NOT REPLY TO THIS EMAIL
> Reply only to the group.

The "/*+ */" is a nice little generic thing - if you ask for parallel processing, you'll see the hint "filled" appropriately

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------