RE: Dbms_stats giving OA-06512 error

Mladen,

Thanks for the reply and thanks for the suggestion to set events 979, I was not aware of that. The ORA-06512 was a mistype. I am running 9.2.0.8.0 and the question is that I was gathering stats successfully for some time, it wasn't until I added the auto_sample_size that things began failing. The question then is whether method_opt must be changed to make this work or if something else is wrong. The statement it fails on is...

select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring */ substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+ noparallel(t) noparallel_index(t) cursor_sharing_exact dynamic_sampling(0) no_monitoring
*/TO_DATE(DECODE(SUBSTR("REGISTRARACCEPTDATE",1,1),'-','',"REGISTRARACCE PTDATE"),'MM/DD/YYYY') val,count(*) cnt from "EDRS"."AMENDMENT" t where
TO_DATE(DECODE(SUBSTR("REGISTRARACCEPTDATE",1,1),'-','',"REGISTRARACCEPT DATE"),'MM/DD/YYYY') is not null group by TO_DATE(DECODE(SUBSTR("REGISTRARACCEPTDATE",1,1),'-','',"REGISTRARACCEPT DATE"),'MM/DD/YYYY')) order by val

Which in fact will not run. SO, what has changed so that gather stats now fails?

Thanks.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208
-----Original Message-----
From: Mladen Gogala [mailto:mgogala_at_verizon.net] Sent: Friday, January 05, 2007 4:36 PM
To: William Wagman
Cc: oracle-l_at_freelists.org
Subject: Re: Dbms_stats giving OA-06512 error

On 01/05/2007 02:12:50 PM, William Wagman wrote:
> Greetings,
>
> I have been running dbms_stats.gather_schema_stats against
> test/development database for some time and recently added
>
> estimate_percent => dbms_stats.auto_sample_size
>
> Since which time ORA-06152 errora are generated...
>
> BEGIN dbms_stats.gather_schema_stats( ownname => 'EDRS',
> estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for
all
> columns', cascade => true, options => 'GATHER', degree => 15); END;
>
> *
> ERROR at line 1:
> ORA-00979: not a GROUP BY expression
> ORA-06512: at "SYS.DBMS_STATS", line 10502
> ORA-06512: at "SYS.DBMS_STATS", line 10996
> ORA-06512: at "SYS.DBMS_STATS", line 11183
> ORA-06512: at "SYS.DBMS_STATS", line 11237
> ORA-06512: at "SYS.DBMS_STATS", line 11214
> ORA-06512: at line 1

First of all, your method_opt is erroneous. You should specify something like
'FOR ALL COLUMNS SIZE SKEWONLY' or 'FOR ALL COLUMNS SIZE AUTO' or Wolfgang's
favorite option 'FOR ALL INDEXED COLUMNS SIZE <histogram size>'. Second, the basic error is "ORA-00979 Not a group by expression", not 6512.
You can catch the SQL statement throwing the error by issuing the following
commands:

ALTER SYSTEM SET TRACEFILE_IDENTIFIER=STATS; ALTER SYSTEM SET EVENTS='979 TRACE NAME ERRORSTACK FOREVER, LEVEL 12'; That will catch the statement causing ORA-00979. If that doesn't help, you have to deal with a bug and should contact Oracle support. You didn't
specify the version and the platform, which would expose you the the danger
of sybranding in some other forums, but if you can, I advise you to upgrade
to the highest patch level for your version.

-- 
Mladen Gogala
http://www.mladen-gogala.com

--
http://www.freelists.org/webpage/oracle-l