首页 » ORACLE » Scripts: format Library Cache Lock/pin wait event p3 value

Scripts: format Library Cache Lock/pin wait event p3 value

一个简单的转换当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 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

打赏

对不起,这篇文章暂时关闭评论。