PL/SQL在執行過程中,對其依賴的表,函式,過程都加lock和pin嗎?
sys@CRMG>create table wxh_tbd as select * from dba_objects;
Table created.
-----------建立兩個過程,互相依賴,其中一個過程需要訪問之前建立的表。
create or replace procedure pinning
is
begin
null;
end;
/
create or replace procedure calling
IS
c NUMBER;
BEGIN
SELECT object_id INTO c FROM wxh_tbd WHERE object_id=20;
pinning;
dbms_lock.sleep(3000);
end;
/
---------會話一執行CALLING,它依賴了PINNING和WXH_TBD
sys@CRMG>exec calling;
這個時候DUMP共享池。
--------檢視錶wxh_tbd的library cache內容。
發現LMD和PMD都是0,即沒有任何的LOCK和PIN.
BUCKET#34765 mtx=0x86e7e4e8(0, 44, 0):
LIBRARY HANDLE:0x7a12da90 bid=34765 hid=b3f287cd lmd=0 pmd=0 sta=VALD
name=SYS.WXH_TBD
hash=fbcfe03c612dd530e44f397fb3f287cd idn=0
tim=02-17-2011 12:57:07 kkkk-dddd-llll=0000-0705-0705
exc=0 ivc=0 ldc=3 slc=0 lct=20 pct=22
cbb=6 rpr=3 kdp=0 kep=0 bus=19 hus=19 dbg=0
dmtx=0x7a12db38(0, 9, 0) mtx=0x7a12dba0(1583, 171, 0)
nsp=TABL(01) typ=TABL(02) llm=0 flg=KGHP/TIM/[00006800]
lwt=0x7a12db18[0x7a12db18,0x7a12db18]
pwt=0x7a12daf8[0x7a12daf8,0x7a12daf8]
ref=0x7a12db28[0x728cf978,0x706ec5a8]
HANDLE REFERENCES:
reference handle flags
--------- --------- -------------------
728cf978 7ec7c580 DEP[01]
timestamp=02-17-2011 12:57:07 whr=0
78473e90 656369a0 DEP[01]
timestamp=02-17-2011 12:57:07 whr=0
6c373f20 741fdf08 DEP[01]
timestamp=02-17-2011 12:57:07 whr=0
ad258558 7e55a008 DEP[01]
timestamp=02-17-2011 12:57:07 whr=0
6733d810 659fc9d0 DEP[01]
timestamp=02-17-2011 12:57:07 whr=0
81bf99d8 741d6410 DEP[01]
timestamp=02-17-2011 12:57:07 whr=0
706ec5a8 7ab7b1f0 DEP[01]
timestamp=02-17-2011 12:57:07 whr=0
LIBRARY OBJECT: 0xadf36350
flg=EXS/LOC[0005] pfl=[0000] ssta=VALD
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 7e774078 adf36430 adf36320 I/-/A/-/- 0 NONE 00
2 6bd791a8 6a5e9f58 6a5e9f28 I/-/A/-/- 0 NONE 00
8 6bd790e0 943634c8 943632b0 I/-/A/-/- 0 NONE 00
--------檢視PINNING的library cache內容
根據LMD和PMD的內容可知道,加了NULL型的LOCK和S型的PIN
並且注意紅色字型部分,清楚的顯示了LOCK和PIN的實現跟enquence的區別,它是透過HANDLER部分來記載的。不過他們相似的地方是,都有排隊機制。
BUCKET#42528 mtx=0x86eca1e0(0, 79, 0):
LIBRARY HANDLE:0x74a4e598 bid=42528 hid=7feaa620 lmd=N pmd=S sta=VALD
name=SYS.PINNING
hash=33438f7c4743cfb2654300177feaa620 idn=0
tim=02-17-2011 12:46:59 kkkk-dddd-llll=0000-001d-001d
exc=0 ivc=0 ldc=1 slc=1 lct=4 pct=4
cbb=3 rpr=2 kdp=0 kep=0 bus=2 hus=2 dbg=0
dmtx=0x74a4e640(0, 1, 0) mtx=0x74a4e6a8(859, 93, 0)
nsp=TABL(01) typ=PRCD(07) llm=0 flg=KGHP/TIM/[00004800]
lwt=0x74a4e620[0x74a4e620,0x74a4e620]
pwt=0x74a4e600[0x74a4e600,0x74a4e600]
ref=0x74a4e630[0x6c373ea8,0x6c373ea8]
HANDLE REFERENCES:
reference handle flags
--------- --------- -------------------
6c373ea8 741fdf08 DEP[01]
timestamp=02-17-2011 12:46:59 whr=0
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
66355668 9e39fdf0 9e39fdf0 1 N PNC/[02]
PIN OWNERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
746620d8 9e39fdf0 9e39fdf0 66355668 3 S 0011
LIBRARY OBJECT: 0x67b6ffd0
flg=EXS/LOC[0005] pfl=NST[0001] ssta=VALD
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 65b2c620 67b700b0 67b6ffa0 I/P/A/-/- 0 NONE 00
2 67b702f8 94f4ee50 94f4ee20 I/-/A/-/- 0 NONE 00
4 7bceb0f0 942515f0 942515c0 I/P/A/-/- 1 NONE 00
--------檢視CALLING自身。
也是加了NULL的LOCK和S型的PIN。
BUCKET#26086 mtx=0x86e298d0(0, 17, 0):
LIBRARY HANDLE:0x741fdf08 bid=26086 hid=eefe65e6 lmd=N pmd=S sta=VALD
name=SYS.CALLING
hash=8c2737be8eaa56203c151d0aeefe65e6 idn=0
tim=02-17-2011 12:47:56 kkkk-dddd-llll=0000-001d-60bf
exc=0 ivc=0 ldc=1 slc=1 lct=4 pct=6
cbb=5 rpr=3 kdp=0 kep=0 bus=2 hus=2 dbg=0
dmtx=0x741fdfb0(0, 1, 0) mtx=0x741fe018(859, 50, 0)
nsp=TABL(01) typ=PRCD(07) llm=0 flg=KGHP/TIM/[00004800]
lwt=0x741fdf90[0x741fdf90,0x741fdf90]
pwt=0x741fdf70[0x741fdf70,0x741fdf70]
ref=0x741fdfa0[0x70e7c838,0x70e7c838]
HANDLE REFERENCES:
reference handle flags
--------- --------- -------------------
70e7c838 6539ae30 DEP[01]
timestamp=02-17-2011 12:47:56 whr=0
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
744692d0 9e39fdf0 9e39fdf0 1 N PNC/[02]
PIN OWNERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
66355858 9e39fdf0 9e39fdf0 744692d0 3 S 0011
LIBRARY OBJECT: 0x70ba08a0
flg=EXS/LOC[0005] pfl=NST[0001] ssta=VALD
DEPENDENCIES: count=4 size=16
dependency# table reference handle position flags
----------- -------- --------- -------- -------- -------------------
0 6c374168 6c373ea8 74a4e598 0 DEP[01]
1 6c374168 6c373f20 8c6eecc0 0 DEP[01]
2 6c374168 6c373f60 7aae1bd8 0 DEP[01]
3 6c374168 6c374040 745c4a58 0 DEP[01]
ACCESSES: count=2 size=16
dependency# types
----------- -----
0 000c
2 000c
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 7aa4d430 70ba0980 70ba0870 I/P/A/-/- 0 NONE 00
2 70ba0bc8 9435cec0 9435ce90 I/-/A/-/- 0 NONE 00
4 6c373fb8 944d6a78 944d6a48 I/P/A/-/- 1 NONE 00
由以上內容可以知道,CALLING執行過程中
1)對其依賴的過程,加了NULL型的LOCK和S型的PIN
2)對其依賴的表,沒有加LOCK和PIN。(其實在PL/SQL解析的時候,也是加S型的LOCK的)
按照上面,不難推斷,CALLING執行過程中
1)表WXH_TBD可以被順利刪除(其他DDL也可以),因為刪除表需要獲取表上(HANDLER)的X型的LOCK跟NULL型的LOCK不衝突。
2)對其依賴的過程PINNING,由於PINNING上存在NULL型的LOCK和S型的PIN,刪除PINNING需要X型的LOCK和X型的PIN,LOCK可以順利獲得,因為NULL與X不衝突,但是PIN卻獲得不了,S和X是衝突的。
看看實際是不是如此:
sysCRMG>drop table wxh_tbd;
Table dropped.
sysCRMG>drop procedure pinning;
會HANG在那。
檢視後臺等待事件,已經產生了pin等待。而且等待是發生在pinning上的。
sysCRMG>select event,p1raw from v$session_wait where wait_class<>'Idle';
EVENT P1RAW
------------------------------ ----------------
library cache pin 0000000074A4E598
sysCRMG>select KGLNAOBJ from x$kglob where kglhdadr = '0000000074A4E598';
KGLNAOBJ
------------------------------
PINNING
還有幾個疑問:
1)那就是PL/SQL執行的時候,到底需要不需要獲得LOCK(此LOCK是PL/SQL物件上的LOCK)。因為上面DUMP出來的LOCK都是NULL的。
答案是執行PL/SQL執行,在進行解析的時候,是需要對PL/SQL的HANDLER加S型的LOCK的。只是時間非常短。
透過如下方法可以驗證:
session 1執行:
exec calling;
session 2 執行:
alter procedure calling compile;
session 3執行:
select sid from v$mystat where rownum=1;
SID
-------
958
exec calling;
這個時候檢視等待。
select sid,event,p1raw from v$session_wait where wait_class<>'Idle';
SID EVENT P1RAW
------- ------------------------------
1291 SQL*Net message to client 0000000062657100
出現了LOCK。而且是會話3產生的。因此它需要獲得S型的LOCK跟SESSION2的X型的LOCK衝突了。
select KGLNAOBJ from x$kglob where kglhdadr = '00000000741FDF08';
KGLNAOBJ
------------------------------
CALLING
2)PL/SQL執行的時候需要不需要獲得依賴物件上的LOCK。
答案是需要,在進行解析的時候是需要獲得S型的LOCK的。
SESSION 1:對一個大表的一個欄位進行耗時的DDL操作。
SQL> alter table wxh_tbd modify gmt_create not null;
SESSION 2:執行CALLING過程(CALLING過程裡呼叫了表WXH_TBD);
EXEC CALLING;
SESSION 3:檢視等待
SQL> select sid,event,p1raw from v$session_wait where wait_class<>'Idle';
SID EVENT P1RAW
---------- ------------------------------ ----------------
3262 SQL*Net message to client 0000000062657100
3263 library cache lock 000000009A36D448
3266 db file scattered read 0000000000000070
SQL> select KGLNAOBJ from x$kglob where kglhdadr = '000000009A36D448';
KGLNAOBJ
--------------------------------------------------------------------------------
WXH_TBD
等待是WXH_TBD上的。
BUCKET#42528 mtx=0x86eca1e0(0, 1350, 0):
LIBRARY HANDLE:0x8c80b288 bid=42528 hid=7feaa620 lmd=X pmd=S sta=VALD
name=SYS.PINNING
hash=33438f7c4743cfb2654300177feaa620 idn=138331
tim=02-17-2011 12:46:59 kkkk-dddd-llll=0000-0011-0011
exc=0 ivc=0 ldc=1 slc=2 lct=2 pct=1
cbb=1 rpr=1 kdp=0 kep=0 bus=1 hus=1 dbg=0
dmtx=0x8c80b330(0, 1, 0) mtx=0x8c80b398(2940, 18, 0)
nsp=TABL(01) typ=PRCD(07) llm=0 flg=KGHP/TIM/[00002800]
lwt=0x8c80b310[0x8c80b310,0x8c80b310]
pwt=0x8c80b2f0[0x7a8bdc18,0x7a8bdc18]
ref=0x8c80b320[0x73b33128,0x73b33128]
HANDLE REFERENCES:
reference handle flags
--------- --------- -------------------
73b33128 7eff6b48 DEP[01]
timestamp=02-17-2011 12:46:59 whr=0
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
7a8bdca0 b26369d8 b26369d8 1 X CNB/[01]
6687bd28 9e63ebb0 9e63ebb0 1 N PNC/[02]
PIN OWNERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
7a6a1c68 9e63ebb0 9e63ebb0 6687bd28 3 S 0011
PIN WAITERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
7a8bdba8 b26369d8 b26369d8 0 0 X 0000
LIBRARY OBJECT: 0x7ba19858
flg=EXS/LOC[0005] pfl=NST[0001] ssta=VALD
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 74e427e8 7ba19978 7ba19828 I/P/A/-/- 0 NONE 00
4 7ba199c0 98b48950 98b48920 I/P/A/-/- 1 NONE 00
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22034023/viewspace-687416/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- PL/SQL 中的儲存過程與函式SQL儲存過程函式
- js函式執行過程的探究JS函式
- sql 執行過程SQL
- 【fosoyo】SQL Server儲存過程/函式加/解密SQLServer儲存過程函式解密
- SQL中儲存過程和函式的區別SQL儲存過程函式
- oracle中取得儲存過程、函式等pl sql原始碼的方法Oracle儲存過程函式SQL原始碼
- sql執行過程分析SQL
- 在Oracle中查詢儲存過程和函式Oracle儲存過程函式
- ORDER BY 在oracle中執行的大概過程Oracle
- 一條Sql的執行過程SQL
- 匯出系統表中的儲存過程和函式儲存過程函式
- SQL server儲存過程函式SQLServer儲存過程函式
- C++建構函式解構函式的執行過程C++函式
- CoreData執行過程的sql語句SQL
- 剖析SQL語句的執行過程SQL
- Oracle SQL 語句的執行過程OracleSQL
- 安裝gcc過程中遇到相互依賴的問題GC
- mysql執行sql語句過程MySql
- 呼叫者許可權過程對OWNER物件的依賴物件
- Oracle Pl/SQL 之 儲存過程OracleSQL儲存過程
- PL/SQL 05 儲存過程 procedureSQL儲存過程
- Javascript中new的執行過程JavaScript
- ORACLE包和過程依賴關係測試(轉)Oracle
- PL/SQL中動態掉用儲存過程SQL儲存過程
- function.procedure函式下的過程執行問題Function函式
- 在.NET中用儲存過程執行SQL語句儲存過程SQL
- MySQL 儲存過程和函式MySql儲存過程函式
- MySQL儲存過程和函式MySql儲存過程函式
- 一條sql語句的執行過程SQL
- 一條 sql 的執行過程詳解SQL
- MySQL 中一條 sql 的執行過程MySql
- 淺談SQL語句的執行過程SQL
- 理解oracle執行sql語句的過程OracleSQL
- SQL語句執行過程詳解SQL
- sql語句執行過程小結SQL
- 指令的執行過程
- maven外掛執行過程中自動執行sql檔案MavenSQL
- 函式呼叫發生在SQL呼叫之前還是過程中函式SQL