Thursday, February 26, 2009

library cache pin

The library cache pin wait event is associated with library cache concurrency.
It occurs when the session tries to pin an object in the library cache to modify or examine it.
The session must acquire a pin to make sure that the object is not updated by other sessions at the same time.
Oracle posts this event when sessions are compiling or parsing PL/SQL procedures and views.

What actions to take to reduce these waits depend heavily on what blocking scenario is occurring.
A common problem scenario is the use of DYNAMIC SQL from within a PL/SQL procedure where the PL/SQL code is recompiled and the DYNAMIC SQL calls something that depends on the calling procedure.
If there is general widespread waiting, the shared pool may need tuning.
If there is a blocking scenario, the following SQL can be used to show the sessions that are holding and/or requesting pins on the object that are given in P1 in the wait:
SELECT S.SID, KGLPNMOD "Mode", KGLPNREQ "Req"
  FROM X$KGLPN P, V$SESSION S
 WHERE P.KGLPNUSE = S.SADDR AND KGLPNHDL = '&P1RAW';

Kill the session with Req=0, Mode=3 or 2.

You can also find out the object's owner and name:
SELECT KGLNAOWN AS OWNER, KGLNAOBJ AS OBJECT
  FROM SYS.X$KGLOB
 WHERE KGLHDADR = '&P1RAW';

You can get P1RAW from v$session_wait view.

No comments:

Post a Comment