Oracle Database Discussions

Hi There,
We were trying to update a package (create and replace) on one of our databases, and we noticed that the create or replace statements was taking a long time.. after few mins, the command returned the following:

ORA-04021: timeout occurred while waiting to lock object

So, we thought of looking at v$access and see whose accessing what, but to our surprise the following take an awful long time to execute:

14:40:17 SYS@ersp>
14:40:17 SYS@ersp> SELECT * FROM v$access WHERE OBJECT ='PROCESST2X;

       SID OWNER                OBJECT                    TYPE
---------- -------------------- ------------------------- ------------------------
      1036 ERSP_APPLICATION     PROCESST2X                PACKAGE

Elapsed: 00:04:18.85
14:44:42 SYS@ersp>

even doing a simple count on v$access takes nearly 7 mins!!!

13:37:35 SYS@ersp> select count(*) from v$access;

  COUNT(*)
----------
      3853

Elapsed: 00:06:51.32
13:44:29 SYS@ersp>

Can anyone shed some light on this please? do we need to gather sys schema stats?

Oracle Version 11.1.0.7 61bit.


Thanks

Edited by: rsar001 on Oct 18, 2011 3:11 PM