参考ORACLE官方文档 ID 122793.1
共享池(Shared Pool)用于缓存最近执行过的SQL语句、PL/SQL程序和数据字典信息,是对SQL语句、PL/SQL程序进行语法分析、编译、执行的区域,其的主要组件有library cache和dictionary cache。library cache 储存了最近的SQL/PLSQL代码的可执行模式(解析或编译版本)。
X$KGLLK ##主要用来查看library cache 的对象的锁(v$session.event=’library cache lock’)
x$kglpn ##主要用来处理library cache pin holder(v$session_wait.event=’library cache pin’)
X$KGLPN--[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s
最简单的 SQL
select * from V$SESSION_BLOCKERS
V$SESSION_BLOCKERS displays the blocker sessions for each blocked session
最常用的SQL
select sid,status,LOGON_TIME,sql_id,blocking_session "死锁直接源",FINAL_BLOCKING_SESSION "死锁最终源",event,seconds_in_wait "会话锁住时间_S",LAST_CALL_ET "会话STATUS持续时间_S" from v$session where event like 'library cache%' and state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID'
查询Cache Pin锁(最准确的一种)
x$kglpn.KGLPNHDL对应v$session_wait中的v$session_wait.P1RAW
x$kglpn.KGLPNUSE对应v$session.saddr
查询DDL pin锁的sql
SELECT s.sid,s.event,s.p1raw,s.seconds_in_wait,kglpnmod "Mode", kglpnreq "Req", s.LOGON_TIME
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr
and kglpnhdl in (SELECT p1raw FROM v$session WHERE event like 'library cache %')
kglpnmod "Mode"表示持有pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
kglpnreq "Req"表示请求pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
查询Cache Lock锁(最准确的一种)
x$kgllk.KGLLKUSE还是x$kgllk.KGLLKSES对应v$session.saddr,好像两者都行
x$kgllk.KGLLKSNM对应 v$session.sid
查询DDL lock锁的sql(含锁住的对象)
select s.sid,s.SERIAL#,s.event,s.seconds_in_wait,kgllkmod Mod,kgllkreq Request, x$kgllk.USER_NAME,kglnaobj Object,s.LOGON_TIME
from x$kgllk ,v$session s where kgllkses=s.saddr and KGLLKHDL in (select p1raw from v$session where event= 'library cache lock') order by s.LOGON_TIME
kgllkmod表示持有pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
kgllkreq表示请求pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
查询锁住的DDL对象
select d.session_id,s.SERIAL#,d.name from dba_ddl_locks d,v$session s where d.owner='MKLMIGEM' and d.SESSION_ID=s.sid
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2126196/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30126024/viewspace-2126196/