Re: DBMS_STATS "Missing expression"

From: Andreas Sheriff <spamcontrol_at_iion.com>
Date: Fri, 26 Aug 2005 00:40:26 -0700
Message-ID: <mIzPe.3039$Ix4.2125@okepread03>

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

Received on Fri Aug 26 2005 - 02:40:26 CDT