一个简单的转换当library cache lock 时取相关对象，及模式的方法。
使用方法
其它相关SQL
TanelPoder
CREATE OR REPLACE PROCEDURE lbc_p3(P3 number)
is
-- purpose: format p3 value (maybe p3 value large than 100,000,000)
-- author: anbob.com(weejar@gmail.com)
-- date: 2016-5-1
-- note: 11.2 tested
v_hex varchar2(50);
v_hexoid varchar2(50);
v_oid number;
v_namespace number;
v_mode varchar2(50);
begin
select to_char(p3,'xxxxxxxxxxxxxxxxxxxxx') into v_hex from dual;
-- object id
select to_number(substr(v_hex,1,length(v_hex)-8),'xxxxxxxxxxxxxx') into v_oid from dual;
-- namespace
select to_number(substr(v_hex,-8,4),'xxxxxxxxxxxxxx') into v_namespace from dual;
-- mode
select decode(to_number(substr(v_hex,-4),'xxxxxxxxxxxxxx'),3,'exclusive mode',2,'share mode',1,'null mode',0,'no lock/pin held','unknown') into v_mode from dual;
dbms_output.put_line('---------------------------------------------');
dbms_output.put_line(lpad('Library cache P3 value: ',50,'.')||p3);
dbms_output.put_line(lpad('Library cache P3 value HEX: ',50,'.')||ltrim(v_hex));
dbms_output.put_line(lpad('Object id: ',50,'.')||v_oid);
dbms_output.put_line(lpad('Namespace: ',50,'.')||v_namespace );
dbms_output.put_line(lpad('RequestMode: ',50,'.')||v_mode);
dbms_output.put_line('Tip: you can query namespace using SQL command SELECT indx,kglstdsc FROM x$kglst WHERE kglsttyp=''NAMESPACE'' and indx='||v_namespace );
end;
/
使用方法
select sample_time,p1,p2,p3 ,sql_id from dbmt.ash09 where event like 'library cache%';
SAMPLE_TIME P1 P2 P3 SQL_ID
------------------------------ ------------------------- ------------------------------ ------------------------------ ---------------
...
01-SEP-16 07.44.40.477 PM 38464071408 39106137144 1571747577004034 5qbbmnszp2fvr
01-SEP-16 07.44.40.477 PM 38464071408 38959758416 1571747577004035 75vkur43zhwp4
01-SEP-16 07.44.40.477 PM 38464071408 42438808440 1571747577004035 6xb123g11jwj4
01-SEP-16 07.44.40.477 PM 38464071408 42240082392 1571747577004035 05rt274uv456x
01-SEP-16 07.44.46.290 PM 39184952208 41906856840 1571747577004034 0y5029c6sqtb1
01-SEP-16 07.44.46.290 PM 39184952208 39099386016 1571747577004034 ftf4m7q153asw
01-SEP-16 07.44.50.497 PM 38464071408 39106137144 1571747577004034 5qbbmnszp2fvr
01-SEP-16 07.44.50.497 PM 38464071408 38959758416 1571747577004035 75vkur43zhwp4
01-SEP-16 07.44.50.497 PM 38464071408 42240082392 1571747577004035 05rt274uv456x
01-SEP-16 07.44.50.497 PM 38464071408 41436790656 1571747577004034 ftf4m7q153asw
01-SEP-16 07.44.50.497 PM 38464071408 41935247880 1571747577004034 1mqv3965gkn2c
01-SEP-16 07.44.56.320 PM 39184952208 38759958752 1571747577004034 0y5029c6sqtb1
01-SEP-16 07.45.00.517 PM 38464071408 41935247880 1571747577004034 1mqv3965gkn2c
01-SEP-16 07.45.00.517 PM 38464071408 41436790656 1571747577004034 ftf4m7q153asw
01-SEP-16 07.45.00.517 PM 38464071408 38959758416 1571747577004035 75vkur43zhwp4
01-SEP-16 07.45.00.517 PM 38464071408 42240082392 1571747577004035 05rt274uv456x
01-SEP-16 07.45.00.517 PM 38464071408 39106137144 1571747577004034 5qbbmnszp2fvr
01-SEP-16 07.45.06.340 PM 39184952208 38759958752 1571747577004034 0y5029c6sqtb1
01-SEP-16 07.45.10.547 PM 38464071408 42240082392 1571747577004035 05rt274uv456x
01-SEP-16 07.45.10.547 PM 38464071408 41436790656 1571747577004034 ftf4m7q153asw
01-SEP-16 07.45.10.547 PM 38464071408 41935247880 1571747577004034 1mqv3965gkn2c
01-SEP-16 07.45.10.547 PM 38464071408 39106137144 1571747577004034 5qbbmnszp2fvr
01-SEP-16 07.45.10.547 PM 38464071408 38959758416 1571747577004035 75vkur43zhwp4
SQL> set serveroutput on
SQL> exec lbc_p3(1571747577004035);
---------------------------------------------
..........................Library cache P3 value: 1571747577004035
......................Library cache P3 value HEX: 5957f00010003
.......................................Object id: 365951
.......................................Namespace: 1
.....................................RequestMode: exclusive mode
Tip: you can query namespace using SQL command SELECT indx,kglstdsc FROM x$kglst WHERE kglsttyp='NAMESPACE' and indx=1
PL/SQL procedure successfully completed.
SQL> exec lbc_p3(1571747577004034);
---------------------------------------------
..........................Library cache P3 value: 1571747577004034
......................Library cache P3 value HEX: 5957f00010002
.......................................Object id: 365951
.......................................Namespace: 1
.....................................RequestMode: share mode
Tip: you can query namespace using SQL command SELECT indx,kglstdsc FROM x$kglst WHERE kglsttyp='NAMESPACE' and indx=1
PL/SQL procedure successfully completed.
其它相关SQL
select
distinct
ses.ksusenum sid, ses.ksuseser serial#, ses.ksuudlna username,KSUSEMNM module,
ob.kglnaown obj_owner, ob.kglnaobj obj_name
,lk.kgllkcnt lck_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req
, w.state, w.event, w.wait_Time, w.seconds_in_Wait
from
x$kgllk lk, x$kglob ob,x$ksuse ses
, v$session_wait w
where lk.kgllkhdl in
(select kgllkhdl from x$kgllk where kgllkreq >0 )
and ob.kglhdadr = lk.kgllkhdl
and lk.kgllkuse = ses.addr
and w.sid = ses.indx
order by seconds_in_wait desc
/
TanelPoder
If the BLOCKING_SESSION isn't working well enough for you or doesn't exist in your DB version, then
you can use X$KGLLK directly to find sessions blocking/with interest in your lock
1) V$SESSION_WAIT.PARAMETER1 is the lib cache object handle we are trying to lock (@sw.sql)
-> V$EVENT_NAME PARAMETER1 shows that ( @sed "library cache lock" )
2) Query X$KGLLK by matching X$KGLLK.KGLHDADR to V$SESSION_WAIT.PARAMETER1
-> find the "holder" sid
3) Use sw, snapper on the SID holding the lock to see what its doing
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
