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 exists 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 your library cache as well as how many misses or hard parses that are occurring. If 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 take a 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,
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 these results are 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 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 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 adheired to for all this to happen. All in the name of making sure that the queries and code that get executed are actually valid and are referencing valid objects. In a nutshell, those locks, and subsequently wait events, are the following:
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 also.
Library cache pin
The library cache pin event is responsible for concurrent access within the library cache. The acquistion of a library cache pin is required to load an object's heap to be loaded into memory. Also, if somone 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 acquireing a lock on the handle of an object, if the process actually wants to examine or modify the objct 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 isn't 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. So for any type of code that needs to be loaded into the library cache, the session must first qcquire a library cache lock on the objects being quiered. After the library cache lock the session must acquire a library cache pin to pin the object heap into the library cache.
There 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 occuring and limiting the concurrent access to code. Most of the problems that occure 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.
The areas that give occasional trouble are various locks that occur, and subsequently are 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 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 also. 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. The 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.
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 take a 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,
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 these results are 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 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 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 adheired to for all this to happen. All in the name of making sure that the queries and code that get executed are actually valid and are referencing valid objects. In a nutshell, those locks, and subsequently wait events, are the following:
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 also.
Library cache pin
The library cache pin event is responsible for concurrent access within the library cache. The acquistion of a library cache pin is required to load an object's heap to be loaded into memory. Also, if somone 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 acquireing a lock on the handle of an object, if the process actually wants to examine or modify the objct 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 isn't 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. So for any type of code that needs to be loaded into the library cache, the session must first qcquire a library cache lock on the objects being quiered. After the library cache lock the session must acquire a library cache pin to pin the object heap into the library cache.
There 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 occuring and limiting the concurrent access to code. Most of the problems that occure 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.
The areas that give occasional trouble are various locks that occur, and subsequently are 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 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 also. 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. The 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.
This entry was posted
on Thursday, November 3, 2011
at 8:48 AM
and is filed under
Points to Ponder
. You can follow any responses to this entry through the
comments feed
.