0317Library Cache Pin/Lock Wait Events
[20160317]Library Cache Pin and Library Cache Lock Wait Events.txt
--這篇blog主要是測試Library Cache Pin and Library Cache Lock Wait Events,不講解細節,僅僅記錄演示過程.
1.環境:
SCOTT@book(84,45)> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create procedure lcp
is
begin
sys.dbms_lock.sleep(3600);
end;
/
--session 1:
SCOTT@book(84,55)> exec lcp()
--session 2:
SCOTT@book(101,2981)> alter procedure lcp compile;
--掛起
2.出現等待時間library cache pin;
SYS@book> set numw 15
SYS@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- --------------- --------------- --------------- --------------- --------------- --------------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 00 1650815232 1 0 236 595 18 SQL*Net message to client WAITED SHORT TIME 2 0
000000007CE0BE30 000000007B533C10 000166ED00010003 2095103536 2069052432 394643070058499 101 2981 29 library cache pin WAITING 66984739 67
SYS@book> @ &r/lcp
SID SERIAL# W_P1R H_WAIT H_P1R H_P2R H_P2R USERS_BLOCKED SQL_ID HASH_VALUE SQL_TEXT
--------------- --------------- ---------------- -------------------- ---------------- ---------------- ---------------- --------------- ------------- --------------- ------------------------------
84 55 000000007CE0BE30 PL/SQL lock timer 00 00 00 1 bbqu7d410xm43 34524291 BEGIN lcp(); END;
101 2981 000000007CE0BE30 library cache pin 000000007CE0BE30 000000007B533C10 000166ED00010003 1 g4q89x3ttj92q 4086867030 alter procedure lcp compile
C100
---------------------------------------------------
alter system kill session '84,55' immediate ;
alter system kill session '101,2981' immediate ;
--執行alter system kill session '84,55' immediate ;就ok了.
$ cat lcp.sql
column h_wait format A20
column sql_text format a30
SELECT s.SID,s.serial#, waiter.p1raw w_p1r, holder.event h_wait, holder.p1raw h_p1r, holder.p2raw h_p2r, holder.p3raw h_p2r,
COUNT (s.SID) users_blocked, SQL.sql_id, SQL.hash_value, SQL.sql_text
FROM v$sql SQL, v$session s, x$kglpn p, v$session_wait waiter, v$session_wait holder
WHERE s.sql_hash_value = SQL.hash_value
AND p.kglpnhdl = waiter.p1raw
AND s.saddr = p.kglpnuse
AND waiter.event LIKE 'library cache pin'
AND holder.SID = s.SID
GROUP BY s.SID,s.serial#, waiter.p1raw, holder.event, holder.p1raw, holder.p2raw, holder.p3raw, SQL.sql_id, SQL.hash_value, SQL.sql_text;
select 'alter system kill session '''||sid||','||serial#||''' immediate ;' c100 from (
SELECT s.SID,s.serial#, waiter.p1raw w_p1r, holder.event h_wait, holder.p1raw h_p1r, holder.p2raw h_p2r, holder.p3raw h_p2r,
COUNT (s.SID) users_blocked, SQL.sql_id, SQL.hash_value, SQL.sql_text
FROM v$sql SQL, v$session s, x$kglpn p, v$session_wait waiter, v$session_wait holder
WHERE s.sql_hash_value = SQL.hash_value
AND p.kglpnhdl = waiter.p1raw
AND s.saddr = p.kglpnuse
AND waiter.event LIKE 'library cache pin'
AND holder.SID = s.SID
GROUP BY s.SID,s.serial#, waiter.p1raw, holder.event, holder.p1raw, holder.p2raw, holder.p3raw, SQL.sql_id, SQL.hash_value, SQL.sql_text );
3.再開啟第3個會話:
SCOTT@book(123,535)> alter procedure lcp compile;
--掛起
SYS@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- --------------- --------------- --------------- --------------- --------------- --------------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 00 1650815232 1 0 236 595 52 SQL*Net message to client WAITED SHORT TIME 2 0
000000007CE0BE30 000000007D8AFE50 000166ED00010003 2095103536 2106261072 394643070058499 123 535 29 library cache lock WAITING 16444382 16
000000007CE0BE30 000000007B533C10 000166ED00010003 2095103536 2069052432 394643070058499 101 2981 29 library cache pin WAITING 291174802 291
--sid=123(第3個會話) 出現library cache lock. sid = 101(第2個會話) 出現library cache lock.
SYS@book> @ &r/lcl
display library cache lock problem
INST_ID SADDR HANDLE MOD REQ OBJECT SQL_ID HASH_VALUE USER_NAME C50
-------- ---------------- ---------------- --------------- --------------- -------------------- ------------- --------------- -------------------- --------------------------------------------------
1 0000000085ABF040 000000007CE0BE30 1 0 LCP 551ha8v2ngx9s 3309827384 SCOTT alter system kill session '84,55' immediate;
1 0000000085B117E0 000000007CE0BE30 3 0 LCP 551ha8v2ngx9s 3309827384 SCOTT alter system kill session '101,2981' immediate;
display wait library cache lock
SID SERIAL# SPID PID P_SERIAL# EVENT C50
---- --------------- ------ ------- --------------- ------------------- --------------------------------------------------
123 535 65297 35 240 library cache lock alter system kill session '123,535' immediate;
--實際上要殺那個很重要,講先解決library cache pin .
--注意看MOD,REQ欄位. session 1 的mod=1,session 2 的mod=3. 要解決問題應該選擇kill session 1.
--如果kill 其他session並不能解決問題.
4.解決問題:
alter system kill session '84,55' immediate ;
--也就是kill session 1:
SYS@book> @ &r/lcl
display library cache lock problem
no rows selected
display wait library cache lock
no rows selected
SYS@book> @ &r/lcp
no rows selected
no rows selected
--session 2:
SCOTT@book(101,2981)> alter procedure lcp compile;
alter procedure lcp compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
--估計測試時間太長,實際上我重複測試沒有出現這個錯誤.如果kill session 2,session 3 出現library cache pin等待事件.
--session 3:
SCOTT@book(123,535)> alter procedure lcp compile;
Procedure altered.
--補充一些,如果從v$sql查詢SQL_ID=551ha8v2ngx9s,無法查到,這裡的物件是lcp過程名.要查詢x$kglob .
-- select * from x$kglob where kglobt03='551ha8v2ngx9s' or kglnahsh=3309827384;
$ cat sharepool/shp4.sql
column N0_6_16 format 99999999
SELECT DECODE (kglhdadr,
kglhdpar, '父遊標控制程式碼地址',
'子游標控制程式碼地址')
text,
kglhdadr,
kglhdpar,
substr(kglnaobj,1,40) c40,
kglobhd0,
kglobhd6,
kglobhs0,kglobhs6,kglobt16,
kglobhs0+kglobhs6+kglobt16 N0_6_16,
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
kglnahsh,
kglobt03 ,
kglobt09
FROM x$kglob
WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;
SYS@book> @ &r/sharepool/shp4 551ha8v2ngx9s 3309827384
old 17: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new 17: WHERE kglobt03 = '551ha8v2ngx9s' or kglhdpar='551ha8v2ngx9s' or kglhdadr='551ha8v2ngx9s' or KGLNAHSH= 3309827384
TEXT KGLHDADR KGLHDPAR C40 KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
-------------- ---------------- ---------------- ---- ---------------- ---------------- --------------- --------------- --------------- --------- --------------- --------------- ------------- ---------------
父遊標控制程式碼地址 000000007CE0BE30 000000007CE0BE30 LCP 000000007D9B79F0 00 4688 0 0 4688 16976 3309827384 0
--或者查詢select * from V$DB_OBJECT_CACHE where hash_value=3309827384;
Record View
As of: 2016/03/17 10:49:25
OWNER: SCOTT
NAME: LCP
DB_LINK:
NAMESPACE: TABLE/PROCEDURE
TYPE: PROCEDURE
SHARABLE_MEM: 16976
LOADS: 12
EXECUTIONS: 0
LOCKS: 2
PINS: 1
KEPT: NO
CHILD_LATCH: 128312
INVALIDATIONS: 1
HASH_VALUE: 3309827384
LOCK_MODE: EXCLUSIVE
PIN_MODE: SHARED
STATUS: VALID
TIMESTAMP: 2016-03-17/09:33:38
PREVIOUS_TIMESTAMP:
LOCKED_TOTAL: 25
PINNED_TOTAL: 24
PROPERTY:
FULL_HASH_VALUE: 5e855748c192403c52860a46c547f538
-- shareable_mem的計算:
SELECT kglnaobj
,kglobhs0
,kglobhs2
,kglobhs4
,kglobhd0
,kglobhd2
,kglobhd4
FROM x$kglob
WHERE kglobt03 = '551ha8v2ngx9s' OR kglnahsh = 3309827384;
KGLNAOBJ KGLOBHS0 KGLOBHS2 KGLOBHS4 KGLOBHD0 KGLOBHD2 KGLOBHD4
--------- -------- --------------- --------------- ---------------- ---------------- ----------------
LCP 4688 8192 4096 000000007D9B79F0 000000007E521910 000000007E521BA8
--視乎儲存過程還使用堆0,堆2,堆4與sql語句不同.4688+8192+4096 =16976.
--如果看檢視V$DB_OBJECT_CACHE,SHARABLE_MEM對應 kglobhs0 + kglobhs1 + kglobhs2 + kglobhs3 + kglobhs4 + kglobhs5 + kglobhs6 相加.
--補上檢查library cache lock的指令碼,由於要訪問x$,僅僅以sys使用者執行:
$ cat lcl.sql
PROMPT
PROMPT display library cache lock problem
PROMPT
column object format a20
column user_name format a20
SELECT inst_id
,kgllkses saddr
,kgllkhdl handle
,kgllkmod MOD
,kgllkreq REQ
,kglnaobj object
,KGLLKSQLID sql_id
,kglnahsh hash_value
,user_name
, 'alter system kill session '''
|| s.sid
|| ','
|| s.serial#
|| ''''
|| ' immediate;'
c50
--,lock_a.*
FROM x$kgllk lock_a, v$session s
WHERE s.saddr = lock_a.kgllkses and
kgllkmod > 0
AND EXISTS
(SELECT lock_b.kgllkhdl
FROM x$kgllk lock_b
WHERE kgllkses IN (SELECT saddr
FROM v$session
WHERE event like 'library cache lock') /* blocked session */
--WHERE event like 'library cache pin') /* blocked session */
AND lock_a.kgllkhdl = lock_b.kgllkhdl
AND kgllkreq > 0);
PROMPT
PROMPT display wait library cache lock
PROMPT
SELECT s.sid
,s.serial#
,p.spid
,p.pid
,p.serial# p_serial#
,s.event
, 'alter system kill session '''
|| s.sid
|| ','
|| s.serial#
|| ''''
|| ' immediate;'
c50
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.event= 'library cache lock';
--AND s.event= 'library cache pin';
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2058652/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- library cache lock和cursor: pin S wait on X等待AI
- oracle library cache之library cache lock_library cache pin wait event釋義OracleAI
- sql version count引發cursor:pin s wait x及library cache latch library cache lockSQLAI
- Library Cache Pin 及 Library Cache Lock分析
- library cache lock和library cache pin理解
- enq：Library cache lock/pin等待事件ENQ事件
- Library cache lock/pin詳解(轉)
- Buffer Cache I/O-Related Wait EventsAI
- library cache pin和library cache lock的診斷分析
- library cache lock和library cache pin區別總結
- library cache pin/lock的解決辦法
- zt_library cache pin和lock等待分析
- 定位Library Cache pin,Library Cache lock等待的解決方法
- Oracle Enqueues Wait Events 三 enq: TX - row lock contentionOracleENQAI
- library cache lock\pin的查詢與處理
- Oracle RAC Cache Fusion系列十八：Oracle RAC Statisticsand Wait EventsOracleAI
- zt_如何平面解決library cache lock和library cache pin
- wait eventsAI
- Shared pool的library cache lock/pin及硬解析
- Library cache pin/lock 在Oracle 10g的增強Oracle 10g
- latch: row cache objects 和cursor: pin S wait on X共同出現ObjectAI
- zt_如何使用event 10049分析定位library cache lock and library cache pin
- ORACLE LOCK,LATCH,PINOracle
- Oracle RAC Wait EventsOracleAI
- Data Guard Wait EventsAI
- Parallel Query Wait EventsParallelAI
- 由最長SQL想到的Latch Free( Library Cache Pin/Lock)整理~~草稿SQL
- Oracle 之pin和lockOracle
- Oracle Enqueues Wait Events 二OracleENQAI
- Oracle Enqueues Wait Events 一OracleENQAI
- Oracle Enqueues Wait Events 三OracleENQAI
- 【DATAGUARD】Data Guard Wait EventsAI
- 幾個ORACLE wait eventsOracleAI
- cursor pin S wait on XAI
- cursor: pin S wait on XAI
- cursor:pin S wait on XAI
- 共享池之六:shared pool latch/ library cache latch /lock pin 簡介
- oracle11g之v$libcache_locks處理library cache lock及library cache pinOracle