Oracle Library Cache

Oracle Library Cache

Gainingan understanding of internal Oracle structures is essential to becoming betterDBAs and servicing our end user community. Oracle's library cache is one suchinternal structure that, after learning about, can help eliminate some verynasty denial of service requests originating from application users.

Oracle's library cache isnothing more than an area in memory, specifically one of three parts inside theshared pool. The library cache is composed of shared SQL areas, PL/SQL packagesand procedures, various locks & handles, and in the case of a shared serverconfiguration, stores private SQL areas. Whenever an application wants toexecute SQL or PL/SQL (collectively called code), that code must first resideinside Oracle's library cache. When applications run and reference code, Oraclewill first search the library cache to see if that code already exists inmemory. If the code already exists in memory then Oracle can reuse thatexisting code (also known as a soft parse). If the code does not exist, Oraclemust then load the code into memory (also known as a hard parse, or librarycache miss). There are various criteria as to whether code being requestedactually matches code already in the library cache but that is beyond the scopeof this article. Just be aware that a configured library cache area, since itis allocated a specific amount of memory, can actively only hold so much codebefore it must age out some to make room for code that is required byapplications. This is not necessarily a bad thing but we must be aware of thesize of our library cache as well as how many misses or hard parses that areoccurring. If there are too many, we may need to increase the amount of memoryallocated to the library cache.

To monitor and gain anunderstanding of how your current library cache has performed since your laststartup of the Oracle database you can issue the following SQL. Obviously ifyou are experiencing immediate performance problems, you will want to look atthe results of this query over a period of time. Regardless, each of the rowsreturned represents a specific type of code (namespace) kept in the librarycache and their particular performance activity.

SQL> select namespace,

            pins,

            pinhits,

            reloads,

            invalidations,

            pinhitratio * 100                     pinhitratio

       from v$librarycache;

NAMESPACE             PINS    PINHITS    RELOADS INVALIDATIONS PINHITRATIO

--------------- ---------- ---------- ---------- ------------- -----------

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 theseresults 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 butwasn't in the library cache (RELOADS). Another very important statistic is thenumber of INVALIDATIONS that have occurred for a particular namespace.INVALIDATIONS are those pieces of code that for some reason, typically througha DDL operation, have become invalid and required a reparse. All of this can besummarized in the hit ratio (PINHITRATIO). So, in our example SQL above we cansee that our particular library cache seems to be doing quite well and ourapplications are reusing SQL quite effectively.

You can also get an overallfeel for the library cache with the following SQL. Again, we can see that ourlibrary cache is performing well.

select sum(pins) pins,

       sum(pinhits) pinhits,

       sum(reloads) reloads,

       sum(invalidations) invalidations,

       100-(sum(pinhits)/sum(pins)) *100 reparsing

 from v$librarycache;

      PINS    PINHITS    RELOADS INVALIDATIONS  REPARSING

---------- ---------- ---------- ------------- ----------

  12703182   12651415       4479          1731 .407512071

It is easy to say, when code isrequired by an application, “just put the code in the library cache”.It is another thing to actually have it done. There are internal lockingmechanisms that must be adhered to for all this to happen. All in the name ofmaking sure that the queries and code that are executed are actually valid andare referencing valid objects. In a nutshell, those locks, and subsequent waitevents, are the following:

librarycache load lock

As the name states, the librarycache load lock is concerned with obtaining a lock for a database object sothat it can load it into the library cache. This lock is always obtained in anEXCLUSIVE 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 alreadyobtained the lock, you will wait until they are done loading that object in thelibrary cache.

Librarycache lock

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

Librarycache pin

The library cache pin event isresponsible for concurrent access within the library cache. The acquisition ofa library cache pin is required to load an object's heap to be loaded intomemory. In addition, if someone wants to modify or examine an object they mustacquire this lock.

In order for Oracle to moveobjects into the library cache it uses both locks and pins to access or loadobject handles and heaps. Locks are used to manage the serialization orconcurrency between different users or application processes. This means that,if needed, a process can lock out and prevent other processes from accessing anobject. Locks must also be acquired to locate an object in the cache. Afteracquiring a lock on the handle of an object, if the process actually wants toexamine or modify the object then it must acquire a pin on the object. Thepinning of an object results in the objects heap being loaded into memory if itis not already there. Both locks and pins are required if compilation orparsing of code is ever going to happen—all for the purpose of making sure thatno changes to an object's definition occurs. Therefore, for any type of codethat needs to be loaded into the library cache, the session must first acquirea library cache lock on the objects being queried. After the library cache lockthe session must acquire a library cache pin to pin the object heap into thelibrary cache.

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

What todo if the Library Cache gives you trouble.

In partone of this two part series, we took a look at what the Library Cache was all about.

Torecap, Oracle’s library cache is nothing more than an area in memory,specifically one of three parts inside the shared pool. The library cache iscomposed of shared SQL areas, PL/SQL packages and procedures, various locks& handles, and in the case of a shared server configuration, stores privateSQL areas. Whenever an application wants to execute SQL or PL/SQL (collectivelycalled code), that code must first reside inside Oracle's library cache. Inorder for Oracle to use code, it will first search the library cache to see ifthat code already exists in memory. If the code already exists in memory thenOracle can reuse that existing code (also known as a soft parse). If the codedoes not exist Oracle must then load the code into memory (also known as a hardparse, or library cache miss).

Theareas that give occasional trouble are various locks that occur, andsubsequently cause the following wait events:

library cache load lock— As the name states, the library cache load lock is concerned withobtaining a lock for a database object so that it can load it into the librarycache. When a user is experiencing this form of lock there is someone else thathas the load lock for the object and thus the user must wait until thelock  becomes available.

Library cache lock— Once objects have been loaded into the library cache this lockcontrols the concurrent access to objects between clients. This allows oneclient to have sole access to an object and block out other clients, forinstance, when two clients want to compile the same piece of code.

Library cache pin— The library cache pin event is responsible for concurrent accesswithin the library cache. When a pin is acquired, it causes the object’s heapto be loaded into memory if it is not already there. A wait for a "librarycache pin" implies some other session holds that PIN in an incompatiblemode.

Regardlessof the type of wait within the library cache, the diagnosis to determine who iswaiting on a resource and what that resource is can be done through thequerying of the V$SESSION_WAIT view. For instance if you wanted to find thosesessions that were waiting for the “library cache pin” you could execute thefollowing query. For a library cache pine, the key part of this query is theP1RAW column that gives the handle address of the object that is blocking thisparticular session. For other types of waits, you should reference Oracledocumentation to find which P value corresponds to an object or resource beingwaited on.

SELECT sid, event, p1raw 

  FROM sys.v_$session_wait

 WHERE event = 'library cache pin'

   AND state = 'WAITING';

We canthen execute the following query to find the library cache object being waitedfor. If you don’t currently have any particular waits occurring, this query isjust fun to execute without the where clause to see what is in the librarycache.

SELECT kglnaown AS owner, kglnaobj as Object

  FROM sys.x$kglob

 WHERE kglhdadr='&P1RAW';

To findthose users that are waiting on the object in question you can use theDBA_WAITERS view and issue the following query. This is a very simple query butcan be used to find the blocking session quite nicely. I basically try and findthe waiting session that matches the above session we got from theV$SESSION_WAIT query and then see what the holding_session is. It is also goodto note how many other sessions are waiting behind this blocking session. Ifthere are a lot, you may need to take action quickly.

SELECT waiting_session, holding_session FROM dba_waiters;

If youwant to investigate the actual SQL being done by the holding session, you canissue 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 issometimes good to see what the originating issue really is. It might not be themix of application code but actually one particular statement that is givingOracle a problem. For instance, for me, I have had problems with SQL that referenceobjects through a view that had a database link and was querying an externalnon-Oracle database. This type of problem can only be seen and remedied throughtracing the originating SQL statement.

Nowthat you have determined the actual session and object being waited on, alongwith the session causing the problem and its SQL, it is time to determine whatto do. If the wait has been happening for quite some time you could beexperiencing a bug or malfunction within the library cache. I have seen this happenoccasionally and the only way to remedy was to kill off those processes thatwhere holding the lock. To do this within Oracle you can issue the alter systemkill session command. Depending on the connection type, this may or may notwork. There have been too many times that the connection needed to beterminated through an operating system kill command, or shutting down a set ofapplications. You will have to investigate and see what works best for yoursystem under a complete lockup in the library cache. The only advice I can giveis always attempt the least obtrusive kills first before bringing down systemsand databases.

Whenworking with the library cache, just remember it is nothing more than a sectionof memory that Oracle must load code into before it can execute. Getting thatcode into the library cache can be limited and cause wait events that literallybring a system to a standstill. A quick determination of the SQL causing thehang is needed to take action through a kill of session or process oralteration of code, but don’t loose sight of the fact that this is memory andwe might just need to re-allocate some to this cause to allow Oracle to workmore efficiently.