查找当前在shared pool中被pin住的对象 (eygle)
查找当前在shared pool中被pin住的对象
–1.获得Library Cache Pin等待的对象的信息
–Note: X$KGLOB–[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject
col KGLNAOWN for a10
col KGLNAOBJ for a20
SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj
FROM x$kglob
WHERE kglhdadr IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE ‘library%’)
ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
——– ——– ——– ———- ——————– ———- ——–
973AA6AC 727BECF0 727BECF0 TAOBAO CHECK_TAB_COL 902274209 727BEAD8
–2.获得持有等待对象的session信息
–Note:X$KGLPN–[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s
SELECT a.SID, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse,
b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq
FROM v$session a, x$kglpn b
WHERE a.saddr = b.kglpnuse
AND b.kglpnmod <> 0
AND b.kglpnhdl IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE ‘library%’)
–3.获得持有对象用户执行的代码
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 v$session a, x$kglpn b
WHERE a.saddr = b.kglpnuse
AND b.kglpnmod <> 0
AND b.kglpnhdl IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE ‘library%’)))
