matalink中library cache pin的说明

"library cache pin" Reference Note

This is a reference note for the wait event "library cache pin" which includes the following subsections:

• (eg: For waits seen in )
• (eg: For waits seen in )

See for an introduction to Wait Events.

Definition:

• Versions:7.0 - 9.2 Documentation:None
• Library cache pins are used to manage library cache concurrency. Pinning an object causes the heaps to be loaded into memory (if not already loaded). PINS can be acquired in NULL, SHARE or EXCLUSIVE modes and can be considered like a special form of lock. A wait for a "library cache pin" implies some other session holds that PIN in an incompatible mode.

Individual Waits:

  Parameters:

• P1 = Handle address
• P2 =
• P3 =

• Handle address

  Use P1RAW rather than P1
  This 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'
    ;

• Pin address

  Use P2RAW rather than P2
  This is the address of the PIN itself.

• Encoded Mode & Namespace

  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

  Wait Time:

The waiter waits up to 3 seconds (1 second if PMON) for the PIN to become available. If not available then the session waits again, incrementing SEQ# in .

  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:

If "library cache pin" waits form a significant amount of the wait time then it is important to determine if this is one or two sessions waiting long periods of time or a more serious general contention issue among lots of processes.

• Use to see if the waits are across numerous sessions or isolated to one or two.
• Use sampling of to see if the waits are always for a particular object (ie: a particular value of P1RAW)
• Use to see if any statements have high values of LOADS as repeatedly reloading requires the pin in EXCLUSIVE mode.
• Sometime tracing user sessions which encounter waits can help (See )

Reducing Waits / Wait times:

What to do to reduce these waits depends heavily on what blocking scenario is occuring. A common problem scenario is the use of DYNAMIC SQL from within PLSQL procedure where the PLSQL code is recompiled and the DYNAMIC SQL calls something which depends on the calling procedure.

• If there is general widespread waiting then the shared pool may need tuning. See .
• If there is a blocking scenario collect evidence as described in above and contact Oracle support.

Related:

Tracing User sessions
Shared Pool Tuning

Important:

The above text makes reference to some X$ views. These are only visible to the SYS user and are not guaranteed to be available on all versions of Oracle. Customers are advised NOT to have any application code or scripts which rely on any X$ view.