The Shared Pool Lock Percentage alarm is raised when one session holds a lock on object(s) in the Shared Pool for a prolonged period, and causes other sessions to wait on the same resource.
This "blocking lock" happens rarely. When it does occur, use a SQL query to find out which sessions are affected. In the case of a Library Cache Lock, use the query below:
SELECT s.sid,
p.kglpnmod LockMode,
p.kglpnreq LockRequest,
b.kglnaown ObjectOwner,
b.kglnaobj ObjectName
FROM x$kglpn p, x$kglob b, v$session s
WHERE s.saddr = p.kglpnuse
AND p.kglpnhdl = b.kglhdadr
AND p.kglpnhdl IN (SELECT p1raw FROM v$session_wait
WHERE event LIKE '%library cache lock%')
Resolution
A database administrator may either:
© ALL RIGHTS RESERVED. 利用規約 プライバシー Cookie Preference Center
