Chat now with support
Chat with Support

Spotlight on Oracle 10.4 - Getting Started Guide

Welcome to Spotlight Install Spotlight Start Spotlight Spotlight on Oracle Spotlight on Oracle Data Guard Spotlight on Oracle RAC Spotlight on Unix Spotlight on Windows Spotlight on MySQL Troubleshooting: Connection Problems

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.

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating