Use P1RAW rather than P1This is the handle of the library cache object which the waiting session wants to acquire a pin on.Pin address
The actual object being waited on can be found usingSELECT kglnaown "Owner", kglnaobj "Object" FROM x$kglob WHERE kglhdadr='&P1RAW' ;
Use P2RAW rather than P2This 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.
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
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.
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.
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
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 Note 62160.1)
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 Finding blockers above and contact Oracle support.
Tracing User sessions Note 62160.1
Shared Pool Tuning Note 62143.1
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.Note 62143.1.
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# inHandle address
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26706/viewspace-64585/，如需转载，请注明出处，否则将追究法律责任。