Oracle Library Cache�Part I

Gaining an understanding of internal Oracle structures is
essential to becoming better DBAs and servicing our end user community.
Oracle’s library cache is one such internal structure that, after learning about,
can help eliminate some very nasty denial of service requests originating from
application users.

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. When applications run and reference code, Oracle 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). There are various criteria
as to whether code being requested actually matches code already in the library
cache but that is beyond the scope of this article. Just be aware that a
configured library cache area, since it is allocated a specific amount of
memory, can actively only hold so much code before it must age out some to make
room for code that is required by applications. This is not necessarily a bad
thing but we must be aware of the size of our library cache as well as how many
misses or hard parses that are occurring. If there are too many, we may need to
increase the amount of memory allocated to the library cache.

To monitor and gain an understanding of how your current
library cache has performed since your last startup of the Oracle database you
can issue the following SQL. Obviously if you are experiencing immediate
performance problems, you will want to look at the results of this query over a
period of time. Regardless, each of the rows returned represents a specific
type of code (namespace) kept in the library cache and their particular
performance activity.

SQL> select namespace,
pinhitratio * 100 pinhitratio
from v$librarycache;

————— ———- ———- ———- ————- ———–
SQL AREA 9510054 9475115 1920 1731 99.6326099
TABLE/PROCEDURE 2173571 2158422 2525 0 99.3030363
BODY 977001 976796 16 0 99.9790174
TRIGGER 28566 28491 11 0 99.7374501
INDEX 9735 8359 5 0 85.865434
CLUSTER 2294 2281 2 0 99.4333043
OBJECT 0 0 0 0 100
PIPE 0 0 0 0 100
JAVA SOURCE 0 0 0 0 100
JAVA RESOURCE 0 0 0 0 100
JAVA DATA 493 489 0 0 99.188641

The way to look at these results is to first look at how
many times a particular namespace was executed (PINS), then take a look at how
many times something tried to execute but wasn’t in the library cache
(RELOADS). Another very important statistic is the number of INVALIDATIONS that
have occurred for a particular namespace. INVALIDATIONS are those pieces of
code that for some reason, typically through a DDL operation, have become
invalid and required a reparse. All of this can be summarized in the hit ratio
(PINHITRATIO). So, in our example SQL above we can see that our particular
library cache seems to be doing quite well and our applications are reusing SQL
quite effectively.

You can also get an overall feel for the library cache
with the following SQL. Again, we can see that our library cache is performing

select sum(pins) pins,
sum(pinhits) pinhits,
sum(reloads) reloads,
sum(invalidations) invalidations,
100-(sum(pinhits)/sum(pins)) *100 reparsing
from v$librarycache;

———- ———- ———- ————- ———-
12703182 12651415 4479 1731 .407512071

It is easy to say, when code is required by an
application, “just put the code in the library cache”. It is another
thing to actually have it done. There are internal locking mechanisms that must
be adhered to for all this to happen. All in the name of making sure that the
queries and code that are executed are actually valid and are referencing valid
objects. In a nutshell, those locks, and subsequent wait events, are the

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. This lock is always obtained in an
EXCLUSIVE mode so that no one else can load the same object at the same time.
If your session is trying to obtain this lock and someone else has already
obtained the lock, you will wait until they are done loading that object in the
library cache.

Library cache lock

Once objects have been loaded into the library cache this
lock controls the concurrent access to objects between clients. This lock is
acquired on an object for the serialization of access and allows for a single
application to use an object. The length of this lock is dependent on what is
being done by an application or user. This lock is also obtained to simply
locate an object in the library cache.

Library cache pin

The library cache pin event is responsible for concurrent
access within the library cache. The acquisition of a library cache pin is
required to load an object’s heap to be loaded into memory. In addition, if someone
wants to modify or examine an object they must acquire this lock.

In order for Oracle to move objects into the library cache
it uses both locks and pins to access or load object handles and heaps. Locks
are used to manage the serialization or concurrency between different users or
application processes. This means that, if needed, a process can lock out and
prevent other processes from accessing an object. Locks must also be acquired
to locate an object in the cache. After acquiring a lock on the handle of an
object, if the process actually wants to examine or modify the object then it
must acquire a pin on the object. The pinning of an object results in the
objects heap being loaded into memory if it is not already there. Both locks
and pins are required if compilation or parsing of code is ever going to
happen—all for the purpose of making sure that no changes to an object’s
definition occurs. Therefore, for any type of code that needs to be loaded into
the library cache, the session must first acquire a library cache lock on the
objects being queried. After the library cache lock the session must acquire a
library cache pin to pin the object heap into the library cache.

The monitoring of Oracle’s library cache is essential to
making sure that objects and SQL code are executing efficiently and available
for subsequent applications to execute. The library cache could be under stress
if specific wait events are occurring and limiting the concurrent access to
code. Most of the problems that occur in the library cache are application or
user induced—but that will be addressed next time. Until then, run the
fore-mentioned SQL and get an idea of the overall and general health of your
library cache. Next time we will look at where an issue can exist, how to find
them, and obviously offer solutions to fixing them.


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles