|
關(guān)于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
當(dāng)要對一個過程或者函數(shù)進行編譯時,需要在library cache中pin該對象。在pin該對象以前,需要獲得該對象
handle的鎖定,如果獲取失敗,就會產(chǎn)生library cache lock等待。如果成功獲取handle的lock,則繼續(xù)在library
cache中pin該對象，如果pin對象失敗，則會產(chǎn)生library cache pin等待。如果是存儲過程或者函數(shù),存在
library cache lock等待,則一定存在library cache pin等待;反過來則不一定；但如果是表引起的的等待，
通常出現(xiàn)的等待事件都是library cache lock等待，
可能發(fā)生library cache pin和library cache lock的情況：
1、在存儲過程或者函數(shù)正在運行時被編譯。
2、在存儲過程或者函數(shù)正在運行時被對它們進行授權(quán)、或者回收權(quán)限等操作。
3、對某個表執(zhí)行DDL期間，有另外的會話對該表執(zhí)行DML或者DDL
dml：insert,update,delete 等
dml: modify 列,drop列,add 列,add 主鍵或約束,grant,revoke等
4、PL/SQL對象之間存在復(fù)雜的依賴性
每個想使用或修改已經(jīng)locked/pin的對象的SQL語句,將會等待事件library cache pin，library cachelock直到超時.
通常發(fā)生在5分鐘后,然后SQL語句會出現(xiàn)ORA-4021的錯誤.如果發(fā)現(xiàn)死鎖,則會出現(xiàn)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錯誤不會出現(xiàn)在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.
SQL>
session 1:
SQL> show user
USER is "ROGER"
SQL> exec sys.call;
session 2:
SQL> revoke execute on pin from roger;
當(dāng)然我這里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>
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>
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 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語句來查詢看該對象是否正在被使用：
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
SQL>
關(guān)于library cache pin和 library cache lock的 具體是如何進行的，可以通過
event 10049來進行，下一篇文章將進行介紹。
| 
免費注冊
發(fā)表于 2011-12-23 03:55