分类： Oracle
2011-08-31 11:00:36
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.
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
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$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
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
library cache object在内存中是分成2部分来存储的，第一部分是头信息，叫做handle，头信息里保存了指向具体保存该对象的地址，这个叫做heap.一个对象可能分为很多个heap要访问或者修改一个对象，首先要锁住其头信息，也就是handle，以防止其他session同时访问或者修改该对象，这是library cache lock要做的事情而当实际的访问或者修改对象的内容时，则要根据头信息去访问或者修改具体的heap的，那么也要防止其他session同时访问或者修改这些heap，所以对heap需要加library cache pin来保护（Nigoo的描述）
当要对一个过程或者函数进行编译时，需要在library cache中pin该对象。在pin该对象以前，需要获得该对象handle的锁定，如果获取失败，就会产生library cache lock等待。如果成功获取handle的lock，则继续在library cache中pin该对象，如果pin对象失败，则会产生library cache pin等待。
如果是存储过程或者函数，可以这样认为：如果存在library cache lock等待，则一定存在library cache pin等待；反过来，如果存在library cache pin等待，不一定会存在library cache lock等待；
但如果是表引起的，则一般只有library cache lock等待，则不一定存在library cache pin。
例,有3个session:
session 1:
exec p;
session 2:
alter procedure p compile;
session 3:
alter procedure p compile;
这时候session 1是在pin住p的,session 2是在等library cache pin,session 3:是在等library cache lock
SES1:
执行：exec p_sleep;
假设存储过程p正在运行，且运行时间很长
SES2:
执行：grant execute on p_sleep to system
对p进行编译，如果之前没有其他会话lock存储过程p的handle，则本会话会将获取p的handle锁定；但会话pin p时会失败，此时在SES2上产生library cache pin等待。如果超过5分钟仍然不能完成pin p，则会报错：
ORA-04021: 等待锁定对象 SUK.P_SLEEP 时发生超时。此时，本会话会释放p的handle lock。(也可能是ORA-04020错误)
SES3:
执行：grant execute on p_sleep to system
在这个会话中继续编译p，则该会话在获取p的handle锁定时会失败，在本会话产生library cache lock等待。如果SES2超时，则本会话会获取p的handle lock，v$session_wait上的等待事件也由library cache lock变成ibrary cache pin，直到超时。
library cache pin
查询v$session_wait视图中library cache pin对应的P1、P2、P3
P1 = Handle address
这个就是引起library cache pin等待的对象被pin到library cache中的handle。一般用P1RAW(十六进制)代替p1(十进制)
可以用以下sql查询那个用户下的那个对象正在被请求pin：
SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob
WHERE kglhdadr='&P1RAW';
返回的OBJECT可能是具体的对象，也可能是一段SQL。
P2 = Pin address
自身的pin地址。一般用P2RAW(十六进制)代替P2(十进制)
P3 = Encoded Mode & Namespace
v$access
该视图显示数据库中锁定的数据库对象以及访问这些对象的会话对象（session对象）。
v$access 与 v$locked_object
这两个视图好象都是记录“在用或是在锁的对象”，请问有什么区别？
v$access中只要是在library cache中有pin的对象，就存在，包括存储过程，包等
而v$locked_object则是有TM lock或者TX lock的才会显示
select view_name,view_definition
from v$fixed_view_definition where view_name='GV$ACCESS'
--------------------------------
select distinct s.inst_id,
s.ksusenum,
o.kglnaown,
o.kglnaobj,
decode(o.kglobtyp,
0, CURSOR',
1, INDEX',
2, TABLE',
3, CLUSTER',
4, VIEW',
5, SYNONYM',
6, SEQUENCE',
7, PROCEDURE',
8, FUNCTION',
9, PACKAGE',
10, 'NON-EXISTENT',
11, 'PACKAGE BODY',
12, 'TRIGGER',
13, 'TYPE',
14, 'TYPE BODY',
15, 'OBJECT',
16, 'USER',
17, 'DBLINK',
18, 'PIPE',
19, 'TABLE PARTITION',
20, 'INDEX PARTITION',
21, 'LOB',
22, 'LIBRARY',
23, 'DIRECTORY',
24, 'QUEUE',
25, 'INDEX-ORGANIZED TABLE',
26, 'REPLICATION OBJECT GROUP',
27, 'REPLICATION PROPAGATOR',
28, 'JAVA SOURCE',
29, 'JAVA CLASS',
30, 'JAVA RESOURCE',
31, 'JAVA JAR',
'INVALID TYPE')
from x$ksuse s, x$kglob o, x$kgldp d, x$kgllk l
where l.kgllkuse = s.addr
and l.kgllkhdl = d.kglhdadr
and l.kglnahsh = d.kglnahsh
and o.kglnahsh = d.kglrfhsh
and o.kglhdadr = d.kglrfhdl
select view_name,view_definition
from v$fixed_view_definition where view_name='GV$LOCKED_OBJECT'
--------------------------------
select x.inst_id,
x.kxidusn,
x.kxidslt,
x.kxidsqn,
l.ktadmtab,
s.indx,
s.ksuudlna,
s.ksuseunm,
s.ksusepid,
l.ksqlkmod
from x$ktcxb x, x$ktadm l, x$ksuse s
where x.ktcxbxba = l.kssobown
and x.ktcxbses = s.addr
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
参考来源:
http://crane1585zy.itpub.net/category/38180/59466
http://www.itpub.net/viewthread.php?tid=1094422&extra=&page=2
