Re: A potential bug (infinite loop) in Oracle: querying v$access

On Jan 24, 2:13 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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 11.1.0.6 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 fromv$access;
>
> > OWNER
> > ----------------------------------------------------------------
> > PUBLIC
> > SYSTEM
> > XDB
> > SYS
>
> > Elapsed: 00:00:00.01
>
> > At least there's a workaround.
> > --
>
> > Regards,
> > 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:https://metalink.oracle.com/metalink/plsql/f?p=200:27:500015404803594...
>
> In the article, Jonathan Lewis mentioned that tab_stats$ could be
> checked.
> Test database on 64 bit Windows:
> SELECT
>   *
> FROM
>   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.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

Oracle has published Metalink Note 549895.1, regarding this same problem. Their solution is different, and doesn't involve fixed object statistics.

David Fitzjarrell Received on Fri Feb 01 2008 - 11:38:17 CST