What to do if the Library Cache gives you trouble.
In part one of this two part series, we took a look at what
the Library Cache was all about.
To recap, Oracle’s library cache is nothing more than an
area in memory, specifically one of three parts inside the shared pool. The
library cache is composed of shared SQL areas, PL/SQL packages and procedures,
various locks & handles, and in the case of a shared server configuration,
stores private SQL areas. Whenever an application wants to execute SQL or
PL/SQL (collectively called code), that code must first reside inside Oracle’s
library cache. In order for Oracle to use code, it will first search the
library cache to see if that code already exists in memory. If the code already
exists in memory then Oracle can reuse that existing code (also known as a soft
parse). If the code does not exist Oracle must then load the code into memory
(also known as a hard parse, or library cache miss).
The areas that give
occasional trouble are various locks that occur, and subsequently cause the
following wait events:
library cache load lock— As the name states, the library cache load lock is
concerned with obtaining a lock for a database object so that it can load it
into the library cache. When a user is experiencing this form of lock there is
someone else that has the load lock for the object and thus the user must wait
until the lock becomes available.
Library cache lock— Once objects have been loaded
into the library cache this lock controls the concurrent access to objects
between clients. This allows one client to have sole access to an object and
block out other clients, for instance, when two clients want to compile the
same piece of code.
Library
cache pin— The library cache pin event is responsible for concurrent access
within the library cache. When a pin is acquired, it causes the object’s heap
to be loaded into memory if it is not already there. A wait for a "library
cache pin" implies some other session holds that PIN in an incompatible
mode.
Regardless of the type of wait within the library cache, the
diagnosis to determine who is waiting on a resource and what that resource is
can be done through the querying of the V$SESSION_WAIT view. For instance if
you wanted to find those sessions that were waiting for the “library cache pin”
you could execute the following query. For a library cache pine, the key part
of this query is the P1RAW column that gives the handle address of the object
that is blocking this particular session. For other types of waits, you should
reference Oracle documentation to find which P value corresponds to an object
or resource being waited on.
SELECT sid, event, p1raw
FROM sys.v_$session_wait
WHERE event = ‘library cache pin’
AND state = ‘WAITING’;
We can then execute the following query to find the library
cache object being waited for. If you don’t currently have any particular waits
occurring, this query is just fun to execute without the where clause to see
what is in the library cache.
SELECT kglnaown AS owner, kglnaobj as Object
FROM sys.x$kglob
WHERE kglhdadr=’&P1RAW’;
To find those users that are waiting on the object in
question you can use the DBA_WAITERS view and issue the following query. This
is a very simple query but can be used to find the blocking session quite
nicely. I basically try and find the waiting session that matches the above
session we got from the V$SESSION_WAIT query and then see what the holding_session
is. It is also good to note how many other sessions are waiting behind this
blocking session. If there are a lot, you may need to take action quickly.
SELECT waiting_session, holding_session FROM dba_waiters;
If you want to investigate the actual SQL being done by the
holding session, you can issue the following SQL statement.
select sesion.sid,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address;
This is sometimes good to see what the originating issue
really is. It might not be the mix of application code but actually one
particular statement that is giving Oracle a problem. For instance, for me, I
have had problems with SQL that reference objects through a view that had a
database link and was querying an external non-Oracle database. This type of
problem can only be seen and remedied through tracing the originating SQL
statement.
Now that you have determined the actual session and object being
waited on, along with the session causing the problem and its SQL, it is time
to determine what to do. If the wait has been happening for quite some time you
could be experiencing a bug or malfunction within the library cache. I have
seen this happen occasionally and the only way to remedy was to kill off those
processes that where holding the lock. To do this within Oracle you can issue
the alter system kill session command. Depending on the connection type, this
may or may not work. There have been too many times that the connection needed
to be terminated through an operating system kill command, or shutting down a
set of applications. You will have to investigate and see what works best for
your system under a complete lockup in the library cache. The only advice I can
give is always attempt the least obtrusive kills first before bringing down
systems and databases.
When working with the library cache, just remember it is
nothing more than a section of memory that Oracle must load code into before it
can execute. Getting that code into the library cache can be limited and cause
wait events that literally bring a system to a standstill. A quick
determination of the SQL causing the hang is needed to take action through a
kill of session or process or alteration of code, but don’t loose sight of the
fact that this is memory and we might just need to re-allocate some to this
cause to allow Oracle to work more efficiently.
