Re: A potential bug (infinite loop) in Oracle: querying v$access
Date: Thu, 24 Jan 2008 12:13:11 -0800 (PST)
On Jan 24, 2:20 pm, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> Charles Hooper wrote:
> > The fixed object stats must be wrong (I recall having a problem with
> > that a couple years ago when perfoming the following)...
> > SQL> CONNECT / AS SYSDBA
> > SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL');
> > BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL'); END;
> > *
> > ERROR at line 1:
> > ORA-20000: Insufficient privileges to analyze an object in Fixed Ob
> > ORA-06512: at "SYS.DBMS_STATS", line 17951
> > ORA-06512: at "SYS.DBMS_STATS", line 18404
> > ORA-06512: at "SYS.DBMS_STATS", line 18951
> > ORA-06512: at line 1
> > The same error occurs on Oracle 10.2.0.2, 10.2.0.3, and 22.214.171.124 as
> > the internal user, SYS AS SYSDBA, and SYSTEM.
> > There must be another way:
> > SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE)
> > PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
> Charles to the rescue:
> SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE);
> PL/SQL procedure successfully completed.
> SQL> set timing on
> SQL> select distinct owner from v$access;
> Elapsed: 00:00:00.01
> At least there's a workaround.
> Frank van Bortel
Thanks for the feedback Frank.
That call solved the problem on 64 bit Windows, no more ORA-07445, and
the SQL statement executes quickly. A little more testing to
determine why the following failed:
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL'); I found Metalink doc ID 272479.1, which suggests to use: EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL); Then I started to wonder, did I find a work around a couple years ago for this problem when I migrated to Oracle 10.2.0.2? How to determine if I collected statistics on the fixed tables? A search on Metalink found this article from 2004:
In the article, Jonathan Lewis mentioned that tab_stats$ could be
Test database on 64 bit Windows:
SYS.TAB_STATS$ The above returned about 582 rows after running: EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS',GATHER_FIXED=>TRUE); I then checked a production database, and found no rows returned. Using the suggestion from the Metalink article: EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL); The stats collection completed much more quickly, as it did not analyze the full SYS schema, and there were 582 rows returned by the query on SYS.TAB_STATS$. It looks like this simple query may be used to determine if fixed object statistics need to be collected.
I am still looking for the source of the suggestion to use 'ALL' as the parameter for GATHER_FIXED_OBJECTS_STATS - it was apparently not from "Expert Oracle Database 10g Administation", as that book shows nothing after GATHER_FIXED_OBJECTS_STATS.
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Jan 24 2008 - 14:13:11 CST