[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';