Because we wanted to do some table and index reorganization on production database we decided to install statspack to be able to compare results before and after reorganization.
I have followed the 149113.1 Metalink note and successfully installed the STATSPACK.
After some minutes customer was complaining, that he cannot work with the database. After some investigation I found out, that all sessions are waiting for the Library cache lock or Library cache pin. then I have checked the invalid objects and found out, that 5 sys dbms_* Packages were invalid. I have recompiled one after another. the last invalid object was dbms_util. the recompilation took long time. After all session were disconnected from the DB the pacakage was finally recompiled.
After the problem was resolved I was looking on the metalink to find the reason and found this note:
Database Hangs with Library Cache Lock and Pin Waits [ID 338367.1]
Symptoms
Database-wide hang with sessions waiting for 'library cache lock' and 'library cache pin'.
The issue can be verified from a systemstate dump or v$session_wait output.
The object for which the locks and pins are waited on is DBMS_UTILITY package body.
Pin(s) for DBMS_UTILITY are being held in Shared mode by one or more processes which are waiting for 'pipe get'. These processes are using DBMS_PIPE.
Lock(s) for DBMS_UTILITY are being held by one or more processes which are blocked by the DBMS_PIPE users.
Cause DBMS_UTILITY was invalidated while DBMS_PIPE was being used by sessions working with database pipes.
Sessions waiting on pipes will hold a Library Cache Pin on DBMS_UTILITY in Shared mode.
If DBMS_UTILITY is invalidated, other sessions trying to use it will attempt to automatically recompile it and will be blocked waiting for exclusive mode locks & pins.
To check whether DBMS_UTILITY was invalidated, lookup its LAST_DDL_TIME in DBA_OBJECTS.
Solution The DBMS_PIPE sessions will need to terminate or be killed to resolve the problem.
To avoid this problem make sure DBMS_UTILITY is not invalidated while DBMS_PIPE is being used and processes are waiting (listening) on database pipes.
Last sentence in the note is
A common case where DBMS_UTILITY can be invalidated is when Statspack is being installed.
So I have learned something new today. And now I know, that even the installation of Statspack is not easy task :)
I have followed the 149113.1 Metalink note and successfully installed the STATSPACK.
After some minutes customer was complaining, that he cannot work with the database. After some investigation I found out, that all sessions are waiting for the Library cache lock or Library cache pin. then I have checked the invalid objects and found out, that 5 sys dbms_* Packages were invalid. I have recompiled one after another. the last invalid object was dbms_util. the recompilation took long time. After all session were disconnected from the DB the pacakage was finally recompiled.
After the problem was resolved I was looking on the metalink to find the reason and found this note:
Database Hangs with Library Cache Lock and Pin Waits [ID 338367.1]
Symptoms
Database-wide hang with sessions waiting for 'library cache lock' and 'library cache pin'.
The issue can be verified from a systemstate dump or v$session_wait output.
The object for which the locks and pins are waited on is DBMS_UTILITY package body.
Pin(s) for DBMS_UTILITY are being held in Shared mode by one or more processes which are waiting for 'pipe get'. These processes are using DBMS_PIPE.
Lock(s) for DBMS_UTILITY are being held by one or more processes which are blocked by the DBMS_PIPE users.
Cause DBMS_UTILITY was invalidated while DBMS_PIPE was being used by sessions working with database pipes.
Sessions waiting on pipes will hold a Library Cache Pin on DBMS_UTILITY in Shared mode.
If DBMS_UTILITY is invalidated, other sessions trying to use it will attempt to automatically recompile it and will be blocked waiting for exclusive mode locks & pins.
To check whether DBMS_UTILITY was invalidated, lookup its LAST_DDL_TIME in DBA_OBJECTS.
Solution The DBMS_PIPE sessions will need to terminate or be killed to resolve the problem.
To avoid this problem make sure DBMS_UTILITY is not invalidated while DBMS_PIPE is being used and processes are waiting (listening) on database pipes.
Last sentence in the note is
A common case where DBMS_UTILITY can be invalidated is when Statspack is being installed.
So I have learned something new today. And now I know, that even the installation of Statspack is not easy task :)