一个简单的转换当library cache lock 时取相关对象，及模式的方法。
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 lock1) 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
— update 2022-5-19 —
有可能OBJECT_ID 关连DBA_OBJECTS 的object_id对象不存在， 有可能是回收站中的对象， ASH中IS_SQLID_CURRENT=N， 如高并发INSERT SQL 存储空间问题使用回收站复用对象，验证可以使用logminer 分析， 可以看到sql 文件是delete recyclebin$ where obj#=xxxx;