"library cache pin" Reference Note

Use P1RAW rather than P1This is the handle of the library cache object which the waiting session wants to acquire a pin on.
The actual object being waited on can be found using

  SELECT kglnaown "Owner", kglnaobj "Object"
    FROM x$kglob
   WHERE kglhdadr='&P1RAW'
  ;

Use P2RAW rather than P2This is the address of the PIN itself.

In Oracle 7.0 - 8.1.7 inclusive the value is 10 * Mode + Namespace.
In Oracle 9.0 - 9.2 inclusive the value is 100 * Mode + Namespace.

Where:

Mode is the mode in which the pin is wanted. This is a number thus:

• 2 - Share mode
• 3 - Exclusive mode

Namespace is just the namespace number of the namespace in the library cache in which the required object lives:

• 0 SQL Area
• 1 Table / Procedure / Function / Package Header
• 2 Package Body
• 3 Trigger
• 4 Index
• 5 Cluster
• 6 Object
• 7 Pipe
• 13 Java Source
• 14 Java Resource
• 32 Java Data

Finding Blockers:

The following SQL can be used to show the sessions which are holding and/or requesting pins on the object that 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'
  ;

An X request (3) will be blocked by any pins held S mode (2) on the object.
An S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request.

Diagnosis of why there is a blocking scenario will usually need help from Oracle support. If you just want to clear the immediate issue then the SID information above should allow you to kill off any blocking sessions. Proper diagnosis will usually require you to collect 3 SYSTEMSTATE dumps at 30 seconds intervals then submit these to Oracle support with full details of the sessions and objects involved. To take a SYSTEMSTATE dump connect to the instance a user with ALTER SYSTEM privilege and issue the command:

  ALTER SESSION SET max_dump_file_size = UNLIMITED;
  ALTER SYSTEM SET EVENTS 'immediate trace name systemstate level 10';

This will produce a trace file in USER_DUMP_DEST (or BACKGROUND_DUMP_DEST if connected to a shared server).
In a Parallel Server or RAC environment SYSTEMSTATE dumps should be taken 3 times on each node.

Systemwide Waits:

Reducing Waits / Wait times: