library cache pin等待事件的处理

在后台sys用户下执行：

select s.sid || ',' || s.serial# sid_serial,

       kglpnmod "mode held",

       kglpnreq "request"

  from sys.x$kglpn p, v$session s

where p.kglpnuse = s.saddr

   and kglpnhdl = (select p1raw

                     from v$session_wait

                    where sid = &SID_IN_LIBRARY_CACHE_PIN);

 

或者：

select sid Holder ,KGLPNUSE Sesion , KGLPNMOD Held, KGLPNREQ Req

from x$kglpn , v$session

where KGLPNHDL in (select p1raw from v$session_wait

where wait_time=0 and event like 'library%')

and KGLPNMOD <> 0

and v$session.saddr=x$kglpn.kglpnuse ;

 

或者：

select sql_text from v$sqlarea

  where (v$sqlarea.address,v$sqlarea.hash_value)

      in (select sql_address,sql_hash_value from v$session where sid in (

select sid

from x$kglpn , v$session

where KGLPNHDL in (select p1raw from v$session_wait

where wait_time=0 and event like 'library%')

and KGLPNMOD <> 0

and v$session.saddr=x$kglpn.kglpnuse );

 

查到held>0 的sid，如果local=no ，请沟通是否可以kill掉这个进程