Spotlight on Oracle 10.9 - Getting Started Guide

Shared Pool Lock Percentage Alarm

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:

• Ask the offending session to log off, OR
• Kill the session to release the lock.