understanding the Oracle Library Cache Pin WaitEvent

understanding the Oracle Library Cache Pin WaitEvent

By Richmond Shee, Kirtikumar Deshpande, K Gopalakrishnan

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

Table 1.LatchEvents in Oracle Database 10g

What actions to take to reduce these waitsdepend heavily on what blocking scenario is occurring. A common problemscenario is the use of DYNAMIC SQL from within a PL/SQL procedure where thePL/SQL code is recompiled and the DYNAMIC SQL calls something that depends onthe calling procedure. If there is general widespread waiting, the shared poolmay need tuning. If there is a blocking scenario, the following SQL can be usedto show the sessions that are holding and/or requesting pins on the object thatare given in P1 in the wait:

Wait Parameters

Wait parameters for library cache pin aredescribed here:

·        P1 Address of the object being examined or loaded

·        P2 Address of the load lock

·        P3 Contains the mode plus the namespace (mode indicateswhich data pieces of the object are to be loaded; namespace is the objectnamespace as displayed in V$DB_OBJECT_CACHE view)

 Wait Time

For the PMON process it is one second; forall others it is three seconds.