library cache lock &library cache pin

Oracle使用两种数据结构来进行shared pool的并发控制:lock 和 pin.
Lock比pin具有更高的级别.

Lock在handle上获得,在pin一个对象之前,必须首先获得该handle的锁定.
锁定主要有三种模式: Null,share,Exclusive.
在读取访问对象时,通常需要获取Null(空)模式以及share(共享)模式的锁定.
在修改对象时,需要获得Exclusive(排他)锁定.

在锁定了Library Cache对象以后,一个进程在访问之前必须pin该对象.
同样pin有三种模式,Null,shared和exclusive.
只读模式时获得共享pin,修改模式获得排他pin.

Oracle使用两种数据结构来进行shared pool的并发控制:lock 和 pin.
Lock比pin具有更高的级别.

Lock在handle上获得,在pin一个对象之前,必须首先获得该handle的锁定.
锁定主要有三种模式: Null,share,Exclusive.
在读取访问对象时,通常需要获取Null(空)模式以及share(共享)模式的锁定.
在修改对象时,需要获得Exclusive(排他)锁定.

在锁定了Library Cache对象以后,一个进程在访问之前必须pin该对象.
同样pin有三种模式,Null,shared和exclusive.
只读模式时获得共享pin,修改模式获得排他pin.

=================
library cache pin
=================
原理
~~~~~
An Oracle instance has a library cache that contains the description of
different types of objects e.g. cursors, indexes, tables, views, procedures,
... Those objects cannot be changed when they are used. They are locked by a
mechanism based on library locks and pins. A session that need to use an object
will first acquire a library lock in a certain mode (null, shared or exclusive)
on the object, in order to prevent other sessions from accessing the same
object (e.g. exclusive lock when recompiling a package or view) or to maintain
the object definition for a long time. Locking an object is sometimes referred
as the job to locate it in the library cache and lock it in a certain mode.
If the session wants to modify or examine the object, it must acquire after
the lock also a pin in a certain mode (again null, shared or exclusive).

Each SQL statement that want to use/modify objects that are locked or pinned
and whose lock/pin mode is incompatible with the requested mode, will wait
on events like 'library cache pin' or 'library cache lock' until a timeout
occurs. The timeout normally occurs after 5 minutes and the SQL statement
then ends with an ORA-4021. If a deadlock is detected, an ORA-4020 is given
back.


Dealing with slow downs related to "mysterious" library cache pins
and load locks we should look for the reason of the database object
invalidations. They are likely to be triggered by actions causing
changes to "LAST_DDL" attribute of database objects that have other
dependent ones. Typically they are the object maintenance operations -
ALTER, GRANT, REVOKE, replacing views, etc. This behavior is described
in Oracle Server Application Developer's Guide as object dependency
maintenance.

After object invalidation, Oracle tries to recompile the object at the
time of the first access to it. It may be a problem in case when other
sessions have pinned the object to the library cache. It is obvious that
it is more likely to occur with more active users and with more complex
dependencies (eg. many cross-dependent packages or package bodies).
In some cases waiting for object recompilation may even take hours
blocking all the sessions trying to access it.


ORA-04021 timeout occurred while waiting to lock object %s%s%s%s%s".
Cause: While trying to lock a library object, a time-out occurred.
Action: Retry the operation later.

ORA-04020 deadlock detected while trying to lock object %s%s%s%s%s
Cause: While trying to lock a library object, a deadlock is detected.
Action: Retry the operation later.
(see )

2. Which views can be used to detect library locking problems?
----------------------------------------------------------------

Different views can be used to detect pin/locks:

DBA_KGLLOCK : one row for each lock or pin of the instance
-KGLLKUSE session address
-KGLLKHDL Pin/Lock handle
-KGLLKMOD/KGLLKREQ Holding/requested mode
0 no lock/pin held
1 null mode
2 share mode
3 exclusive mode
-KGLLKTYPE Pin/Lock
(created via the $ORACLE_HOME/rdbms/admin/catblock.sql)

V$ACCESS : one row for each object locked by any user
-SID session sid
-OWNER username
-OBJECT object name
-TYPE object type

V$DB_OBJECT_CACHE : one row for each object in the library cache
-OWNER object owner
-NAME object name or cursor text
-TYPE object type
-LOCKS number of locks on this object
-PINS number of pins on this object

DBA_DDL_LOCKS : one row for each object that is locked (exception made of the cursors)
-SESSION_ID
-OWNER
-NAME
-TYPE
-MODE_HELD
-MODE_REQUESTED

V$SESSION_WAIT : each session waiting on a library cache pin or lock is blocked by some other session
-p1 = object address
-p2 = lock/pin address


3. How to find out why an ORA-4021 occurs?
--------------------------------------------

When you execute the statement that generates the ORA-4021, it is possible
during the delay of 5 minutes to detect the reason for the blocking situation.
Following query can be used to find the blocking and waiting sessions:

FYI: You need to run the script called "catblock.sql" first.
=== This script can be found in: $ORACLE_HOME/rdbms/admin/catblock.sql


select /*+ ordered */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
/

The result looks like:

WAITING_SESSION HOLDING_SESSION LOCK ADDRESS MODE_HELD MODE_REQU
--------------- --------------- ---- -------- --------- ---------
16 12 Pin 03FA2270 Share Exclusive

The object that is locked can be found with v$object_dependency and
should be the same as the one mentioned in the ORA-4021 error message.
e.g.
select to_name from v$object_dependency where to_address = '03FA2270';
should give:

TO_NAME
-------------
DBMS_PIPE

You can find which library objects are used by each session via following
queries, e.g.
a. for the blocked session:

select distinct kglnaobj from x$kgllk where
kgllkuse in (select saddr from v$session where sid = 16);

b. for the blocking session

select distinct kglnaobj from x$kgllk where
kgllkuse in (select saddr from v$session where sid = 12);

One of those objects can be the cursor or statement that each session is
executing/trying to execute.

You can also use the $ORACLE_HOME/rdbms/admin/utldtree.sql utility to find out
how the dependency tree looks like and which objects are dependent on e.g.
DBMS_PIPE. One of those objects will be the sql statement of the holding
session. A variant script on utldtree.sql stands in [NOTE:139594.1] and
gives which objects an object depends on.




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.

P1 = Handle address
P2 = Pin address
P3 = Encoded Mode & Namespace

·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.


查找library cache对象
~~~~~~~~~~~~~~
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:
o 2 - Share mode
o 3 - Exclusive mode

Namespace is just the namespace number of the namespace in the library cache in which the required object lives:
o 0 SQL Area
o 1 Table / Procedure / Function / Package Header
o 2 Package Body
o 3 Trigger
o 4 Index
o 5 Cluster
o 6 Object
o 7 Pipe
o 13 Java Source
o 14 Java Resource
o 32 Java Data

====================
library cache lock的成因和解决方法
====================
两种原因
~~~~~~~~~
* a DML operation that is hanging because the table which is accessed is currently
undergoing changes (ALTER TABLE). This may take quite a long time depending on
the size of the table and the type of the modification
(e.g. ALTER TABLE x MODIFY (col1 CHAR(200) on thousands of records).

* The compilation of package will hang on Library Cache Lock and Library Cache Pin
if some users are executing any Procedure/Function defined in the same package.

两种解决办法
~~~~~~~~~~~~~~~
METHOD 1: SYSTEMSTATE ANALYSIS
------------------------------

One way of finding the session blocking you is to analyze the system state dump.
Using the systemstate event one can create a tracefile containing detailed
information on every Oracle process. This information includes all the resources
held & requested by a specific process.

Whilst an operation is hanging, open a new session and launch the following
statement:

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 8';

Oracle will now create a systemstate tracefile in your USER_DUMP_DEST directory.
Get the PID (ProcessID) of the 'hanging' session from the V$PROCESS by matching
PADDR from V$SESSION with ADDR from V$PROCESS:

SELECT PID FROM V$PROCESS WHERE ADDR=
(SELECT PADDR FROM V$SESSION WHERE SID=sid_of_hanging_session);

The systemstate dump contains a separate section with information for each
process. Open the tracefile and do a search for 'PROCESS pid_from_select_stmt'.
In the process section look up the wait event by doing a search on 'waiting for'.

Example output:

PROCESS 8:
----------------------------------------
SO: 50050b08, type: 1, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=8, calls cur/top: 5007bf6c/5007bf6c, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 82 0 4
last post received-location: kslpsr
last process to post me: 5004ff08 1 2
last post sent: 0 0 13
last post sent-location: ksasnd
last process posted by me: 5004ff08 1 2
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 50058ac4
O/S info: user: daemon, term: pts/1, ospid: 15161
OSD pid info: 15161
----------------------------------------
SO: 5005f294, type: 3, owner: 50050b08, flag: INIT/-/-/0x00
(session) trans: 0, creator: 50050b08, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-0008-00000002, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 6, prv: 0, user: 41/LC
O/S info: user: daemon, term: pts/1, ospid: 15160, machine: goblin.forgotten.realms
program: sqlplus@goblin.forgotten.real ms (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
waiting for 'library cache lock' blocking sess=0x0 seq=253 wait_time=0 --等待事件,seq 次数,
!>> handle address=5023ef9c, lock address=5019cad4, 10*mode+namespace=15

Using the 'handle address' you can look up the process that is keeping a lock
on your resource by doing a search on the address within the same tracefile.

Example output:

PROCESS 9:
----------------------------------------
SO: 50050e08, type: 1, owner: 0, flag: INIT/-/-/0x00
(process) Oracle pid=9, calls cur/top: 5007bbac/5007bbfc, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0

....

----------------------------------------
SO: 5019d5e4, type: 34, owner: 5015f65c, flag: INIT/-/-/0x00
!>> LIBRARY OBJECT PIN: pin=5019d5e4 handle=5023ef9c mode=X lock=0 --handle address
user=5005fad4 session=5005fad4 count=1 mask=0511 savepoint=118218 flags=[00]

From the output we can see that the Oracle process with PID 9 has an exclusive
lock on the object we are trying to access. Using V$PROCESS and V$SESSION we can
retrieve the sid,user,terminal,program,... for this process. The actual statement
that was launched by this session is also listed in the tracefile (statements and
other library cache objects are preceded by 'name=').


METHOD 2: EXAMINE THE X$KGLLK TABLE
-----------------------------------

The X$KGLLK table (accessible only as SYS/INTERNAL) contains all the
library object locks (both held & requested) for all sessions and
is more complete than the V$LOCK view although the column names don't
always reveal their meaning.

You can examine the locks requested (and held) by the waiting session
by looking up the session address (SADDR) in V$SESSION and doing the
following select:

select * from x$kgllk where KGLLKSES = 'saddr_from_v$session'

This will show you all the library locks held by this session where
KGLNAOBJ contains the first 80 characters of the name of the object.
The value in KGLLKHDL corresponds with the 'handle address' of the
object in METHOD 1.

You will see that at least one lock for the session has KGLLKREQ > 0
which means this is a REQUEST for a lock (thus, the session is waiting).
If we now match the KGLLKHDL with the handles of other sessions in
X$KGLLK that should give us the address of the blocking session since
KGLLKREQ=0 for this session, meaning it HAS the lock.

SELECT * FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0);

If we look a bit further we can then again match KGLLKSES with SADDR
in v$session to find further information on the blocking session:

SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);

In the same way we can also find all the blocked sessions:

SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ > 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKING SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ = 0)
);

From: Steve Adams
Date: 08-Nov-2000 20:09
Subject: What's the difference between pins and gets?


When an object has been located in (or loaded into) the library cache, the session "remembers" its location by maintaining a null mode lock on the object. When a session needs to use an object, if it already (or still) has the library cache lock, then it can proceed with the pin request. If it does not yet have a lock then a get operation is needed to get a lock before the pin can be requested.

This probably makes little sense unless you understand the distinction between the handle of a library cache object and its heaps. Locks protect the handle. Pins protect the heaps. The heaps are the "body" parts of the object that get loaded as required, except for heap 0 which contains the identifying information about the object. When we speak of an object being aged out and then reloaded, it is actually the heaps other than heap 0 of which we are speaking. Heap 0 is never aged out while any session is holding a lock on its handle.

The fundamental reason for the distinction between locks and pins is that it in theory allows an object's identity to be checked using a shared lock on the handle while another session has an exclusive pin on one or more of its heaps. The distinction is also important for maintaining cache coherence.

Hope this is not going to trigger a flood of follow-up questions ....



how does Oracle determine that a needed object was previously located in the library cache before it would be pinned ? Does Oracle use some information in the library cache or it firstly tries to get the object ?



A get is an attempt to locate an object in the library cache. If it is not found, it is loaded. Database object definitions are loaded from the data dictionary via the dictionary cache. Cursors are loaded by parsing the statement text.

A pin is an attempt to use a previously located object in the library cache. If it has been aged out, then it must be reloaded.



Could anybody please explain about what is the difference between gets and pins and correspondingly between gethits and pinhits in the v$librarycache view?

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45259/viewspace-134846/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45259/viewspace-134846/