library cache lock

library cache

library cache lock/pin原理及模拟

library cache lock及library cache pin模拟

会话1：创建、调用
会话2：编译
会话3：删除
会话4：查看等待事件

定位会话、定位等待事件、定位SQL

 SELECT  DISTINCT decode(kglpnreq, 0, 'holding_session
:'||s.sid, 'waiting_session: '||s.sid) sid, s.SERIAL#, kglpnmod "Pin Mode", kglpnreq "ReqPin", a.sql_text, kglnaown "Owner", kglnaobj "Object"
FROM x$kglpn p, v$session s, v$sqlarea a, v$session_wait sw, x$kglob x
WHERE p.kglpnuse=s.saddr
    AND kglpnhdl=sw.p1raw
    AND kglhdadr=sw.p1raw
    AND sw.event LIKE '%library cache%'
    AND (a.hash_value, a.address)
IN 
  ( SELECT  DECODE (sql_hash_value, 0, prev_hash_value, sql_hash_value ), DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
  FROM v$session s2
  WHERE s2.sid=s.sid ) ;

select sid, event,wait_class, seconds_in_wait from v$session_wait w where w.WAIT_CLASS <> 'Idle';

set lines 200
set pages 200
set long 9999
col username for a15;
col p1text for a20;
col sql_id for a15
col event for a40;
col machine for a30
col sql_text for a60
alter session set  nls_date_format='YYYY-MM-DD HH24:MI:SS';
select inst_id,machine,username,sid,serial#,sql_id,sql_exec_start,event from gv$session where username is not null and status = 'ACTIVE' and wait_class !='Idle' order by sql_exec_start, sid;