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

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 from v$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:5000154048035945504::::p27_id,p27_show_header,p27_show_help:525959.996,1,1

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. Received on Thu Jan 24 2008 - 14:13:11 CST