sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle 分析library cache pin和library cache lock区别

时间:2016-11-20 14:56   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库事件分析笔记,详细介绍对比关于library cache pin和library cache lock使用方法。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: library cache pin&lock (1)

关于library cache pin和library cache lock, 是一个让人比较疑惑的问题.

我这里主要是指的event, 首先来说下其原理:

lock主要有三种模式: Null, share(2), Exclusive(3).
在读取访问对象时, 通常需要获取Null(空)模式以及share(共享)模式的锁定.
在修改对象时,需要获得Exclusive(排他)锁定.

pin操作跟lock一样, 也有三种模式: Null, shared(2)和exclusive(3).
只读模式时获得shared pin, 修改模式获得和exclusive pin.

模式为shared(2)的pin会阻塞任何exclusive(3)的pin请求.
模式为shared(3)的pin也会阻塞任何exclusive(2)的pin请求.

所有的DDL都会对被处理的对象请求排他类型的lock和pin

当要对一个过程或者函数进行编译时,需要在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 lock等待,

可能发生library cache pin和library cache lock的情况:

1. 在存储过程或者函数正在运行时被编译.
2. 在存储过程或者函数正在运行时被对它们进行授权. 或者回收权限等操作.
3. 对某个表执行DDL期间, 有另外的会话对该表执行DML或者DDL
4. PL/SQL对象之间存在复杂的依赖性
   dml: insert, update, delete 等
   dml: modify列, drop列,add列, add主键或约束, grant, revoke等

每个想使用或修改已经locked/pin的对象的SQL语句,将会等待事件library cache pin, library cache lock直到超时.

通常发生在5分钟后,然后SQL语句会出现ORA-4021的错误.如果发现死锁,则会出现ORA-4020错误.
如下所示:
SQL> ALTER PROCEDURE pin compile;

ALTER PROCEDURE pin compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting TO LOCK object SYS.PIN

需要说明一点的是该ora-04021错误不会出现在alert log中.

下面通过实验来进行模拟:

SQL> SHOW USER
USER IS "SYS"

SQL> CREATE OR REPLACE PROCEDURE pin AS
  2    pin_count NUMBER;
  3  BEGIN
  4    SELECT COUNT(*) INTO pin_count FROM roger.ht01;
  5    dbms_lock.sleep(1800);
  6    dbms_output.put_line(pin_count);
  7  END;
  8  /

PROCEDURE created.

SQL> CREATE OR REPLACE PROCEDURE CALL IS
  2  BEGIN
  3    pin;
  4    dbms_lock.sleep(3000);
  5  END;
  6  /

PROCEDURE created.

SQL> GRANT EXECUTE ON pin TO roger;
GRANT succeeded.

SQL> GRANT EXECUTE ON CALL TO roger;
GRANT succeeded.
SESSION 1:

SQL> SHOW USER
USER IS "ROGER"

SQL> EXEC sys.CALL;
SESSION 2:

SQL> REVOKE EXECUTE ON pin FROM roger;

当然我这里SESSION都hang住了.

SQL>  SELECT event,COUNT(*) FROM v$session GROUP BY event;

EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
PL/SQL LOCK timer                                                         1
library cache pin                                                         1
jobq slave wait                                                           1
rdbms ipc message                                                         9
smon timer                                                                1
pmon timer                                                                1
Streams AQ: qmn slave idle wait                                           1
SQL*Net message TO client                                                 1
Streams AQ: waiting FOR TIME management OR cleanup tasks                  1
Streams AQ: qmn coordinator idle wait                                     1

10 ROWS selected.

SQL> SELECT a.SID, a.username, a.program,c.p1raw
  2    FROM v$session a, x$kglpn b,v$session c
  3   WHERE a.saddr = b.kglpnuse
  4     AND b.kglpnmod <> 0
  5     AND b.kglpnhdl = c.p1raw;

       SID USERNAME        PROGRAM                             P1RAW
---------- --------------- ----------------------------------- --------
       143 ROGER           sqlplus@roger (TNS V1-V3)           2673ED04

SQL> SELECT sql_text
  2    FROM v$sqlarea
  3   WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN
  4         (SELECT sql_address, sql_hash_value
  5            FROM v$session
  6           WHERE sid IN (SELECT sid
  7                           FROM v$session a, x$kglpn b
  8                          WHERE a.saddr = b.kglpnuse
  9                            AND b.kglpnmod <> 0
10                            AND b.kglpnhdl IN
11                                (SELECT p1raw
12                                   FROM v$session_wait
13                                  WHERE event LIKE 'library%')));

SQL_TEXT
----------------------------------------------------------------------
BEGIN sys.CALL; END;

模拟library cache lock
SESSION 1:

SQL> EXEC sys.pin;
SESSION 2:

SQL> REVOKE EXECUTE ON pin FROM roger;
SESSION 3:

SQL> ALTER PROCEDURE pin compile;

SQL>  SELECT event,COUNT(*) FROM v$session WHERE event LIKE
  2   '%library%' GROUP BY event;

EVENT                                    COUNT(*)
-------------------------------------- ----------
library cache pin                               1
library cache LOCK                              1

SQL> SELECT a.SID, a.username, a.program,c.p1raw
  2    FROM v$session a, x$kglpn b,v$session c
  3   WHERE a.saddr = b.kglpnuse
  4     AND b.kglpnmod <> 0
  5     AND b.kglpnhdl = c.p1raw
  6     AND c.event IN('library cache lock')
  7  /

       SID USERNAME                       PROGRAM                                          P1RAW
---------- ------------------------------ ------------------------------------------------ --------
       143 ROGER                          sqlplus@roger (TNS V1-V3)                        2673ED04

SQL> SELECT sql_text
  2    FROM v$sqlarea
  3   WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN
  4         (SELECT sql_address, sql_hash_value
  5            FROM v$session
  6           WHERE sid IN (SELECT sid
  7                           FROM v$session a, x$kglpn b
  8                          WHERE a.saddr = b.kglpnuse
  9                            AND b.kglpnmod <> 0
10                            AND b.kglpnhdl IN
11                                (SELECT p1raw
12                                   FROM v$session_wait
13                     Oracleо             WHERE event LIKE 'library cache lock%')));

SQL_TEXT
-------------------------------------------------------
BEGIN sys.pin; END;

SQL> SELECT DISTINCT /*+ ordered*/ w1.sid waiting_session,
  2                  h1.sid holding_session,
  3                  w.kgllktype lock_or_pin,
  4                  od.to_owner object_owner,
  5                  od.to_name object_name,
  6                  oc.TYPE,
  7                  decode(h.kgllkmod,
  8                         0,
  9                         'None',
10                         1,
11                         'Null',
12                         2,
13                         'Share',
14                         3,
15                         'Exclusive',
16                         'Unknown') mode_held,
17                  decode(w.kgllkreq,
18                         0,
19                         'None',
20                         1,
21                         'Null',
22                         2,
23                         'Share',
24                         3,
25                         'Exclusive',
26                         'Unknown') mode_requested,
27                  xw.KGLNAOBJ wait_sql,
28                  xh.KGLNAOBJ hold_sql
29    FROM dba_kgllock         w,
30         dba_kgllock         h,
31         v$session           w1,
32         v$session           h1,
33         v$object_dependency od,
34         V$DB_OBJECT_CACHE   oc,
35         x$kgllk             xw,
36         x$kgllk             xh
37   WHERE (((h.kgllkmod != 0) AND (h.kgllkmod != 1) AND
38         ((h.kgllkreq = 0) OR (h.kgllkreq = 1))) AND
39         (((w.kgllkmod = 0) OR (w.kgllkmod = 1)) AND
40         ((w.kgllkreq != 0) AND (w.kgllkreq != 1))))
41     AND w.kgllktype = h.kgllktype
42     AND w.kgllkhdl = h.kgllkhdl
43     AND w.kgllkuse = w1.saddr
44     AND h.kgllkuse = h1.saddr
45     AND od.to_address = w.kgllkhdl
46     AND od.to_name = oc.Name
47     AND od.to_owner = oc.owner
48     AND w1.sid = xw.KGLLKSNM
49     AND h1.sid = xh.KGLLKSNM
50     AND (w1.SQL_ADDRESS = xw.KGLHDPAR AND w1.SQL_HASH_VALUE = xw.KGLNAHSH)
51     AND (h1.SQL_ADDRESS = xh.KGLHDPAR AND h1.SQL_HASH_VALUE = xh.KGLNAHSH);

WAITING_SESSION HOLDING_SESSION LOCK  OBJECT_OWN OBJECT_NAM TYPE       MODE_HELD MODE_REQU WAIT_SQL                            HOLD_SQL
--------------- --------------- ----  ---------- ---------- ---------- --------- --------- ----------------------------------- -----------------------------------
     159             158        LOCK  SYS        PIN        PROCEDURE  Exclusive Exclusive REVOKE EXECUTE ON pin FROM roger    ALTER PROCEDURE pin compile
     158             143        Pin   SYS        PIN        PROCEDURE  Share     Exclusive ALTER PROCEDURE pin compile         BEGIN sys.pin; END;

在编译或修改对象之前我们可以通过如下SQL语句来查询看该对象是否正在被使用:

SQL> col Owner FOR a15
SQL> col using_Object FOR a25
SQL> SELECT DISTINCT sid using_sid,
  2                  s.SERIAL#,
  3                  kglpnmod "Pin Mode",
  4                  kglpnreq "Req Pin",
  5                  kglnaown "Owner",
  6                  kglnaobj "using_Object"
  7    FROM x$kglpn p, v$session s, x$kglob x
  8   WHERE p.kglpnuse = s.saddr
  9     AND kglpnhdl = kglhdadr
10     AND p.KGLPNUSE = s.saddr
11     AND kglpnreq = 0
12     AND UPPER(kglnaobj) = UPPER('pin')
13  /

USING_SID    SERIAL#   Pin Mode    Req Pin Owner      using_Object
---------- ---------- ---------- ---------- ---------- -------------------------
       143          5          2          0 SYS        PIN

另外如下的查询脚本也不错, 可以收藏:

SQL> SELECT DISTINCT ses.ksusenum sid,
  2                  ses.ksuseser serial#,
  3                  ses.ksuudlna username,
  4                  ses.ksuseunm machine,
  5                  ob.kglnaown obj_owner,
  6                  ob.kglnaobj obj_name,
  7                  pn.kglpncnt pin_cnt,
  8                  pn.kglpnmod pin_mode,
  9                  pn.kglpnreq pin_req,
10                  w.state,
11                  w.event,
12                  w.wait_Time,
13                  w.seconds_in_Wait
14  -- lk.kglnaobj, lk.user_name, lk.kgllksnm,
15  --,lk.kgllkhdl,lk.kglhdpar
16  --,trim(lk.kgllkcnt) lock_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req,
17  --,lk.kgllkpns, lk.kgllkpnc,pn.kglpnhdl
18    FROM x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w
19   WHERE pn.kglpnhdl IN (SELECT kglpnhdl FROM x$kglpn WHERE kglpnreq > 0)
20     AND ob.kglhdadr = pn.kglpnhdl
21     AND pn.kglpnuse = ses.addr
22     AND w.sid = ses.indx
23   ORDER BY seconds_in_wait DESC
24  /

SID    SERIAL# USERNAME   MACHINE     OBJ_OWNER  OBJ_NAME   PIN_CNT   PIN_MODE    PIN_REQ STATE     EVENT               WAIT_TIME SECONDS_IN_WAIT
---- ---------- ---------- ----------- ---------- --------- -------- ---------- ---------- --------- ----------------------------------- ---------- ---------------
143          5 ROGER      oracle      SYS        PIN              3          2          0 WAITING   PL/SQL LOCK timer                            0            1360
159          7 SYS        oracle      SYS        PIN              0          0          3 WAITING   library cache pin                            0             454

关于library cache pin和 library cache lock的 具体是如何进行的,

可以通过event 10049来进行, 下一篇文章将进行介绍.

另外eygle的博客也有篇不错的文章, 里面提到10g中, grant已经不要要获得library cache pin了,
详见:

http://www.eygle.com/archives/2007/04/library_cache_pin_grant.html

如下链接也可以参考:

http://orainternals.wordpress.com/2009/06/02/library-cache-lock-and-library-cache-pin-waits/

http://dbsnake.com/2011/05/lib-cache-lck-and-pin.html

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle 分析library cache pin和library cache lock区别

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1299.html

Oracle研究中心

关键词:

详解library cache pin

详解library cache lock

ORA-04021: timeout occurred while waiting