Some basics about Oracle Library Cache

What is Library Cache and where it is?

When we run a query, oracle runs the query with the optimization of it. The optimization is nothing but the steps that are needed to understand the query in a better way and prepare a plan to run the query aka, execution plan. This step is necessary to do for the first time but must not be repeated all the times when the query is executed. The first time is hard to avoid as oracle knows nothing about the query , so there is no way out othere than optimizing the query. This first time optimization is called Hard Parsing of the query. The recommendation is that this step must not be repeated all the time when the query gets executed. With the subsequent executions of the same cursor, Oracle must only pick up the already cached cursor and just run it. This step is called Soft parse of the query. To store the already executed cursor, oracle uses a memory area called Library Cache which essentially is part of its parent memory area, Shared Pool. Lookup into the library cache only tells oracle that the cursor is available over there or not for being reused and if its available, whether it can be reused eventually or not( there are conditions when the cached cursor too won’t get reused, atleast not right away).

A little more closer look at Library Cache

As I mentioned, this post is not going to be the place of the interpertation of the dumps of the library cache so we won’t be going too deep but to that extent only which is relevant to make up an base. So keeping that in view, library cache is containing a hash table which is linked to couple of hash buckets further linked to the objects. The hash table, as the name says, is a table prepared from the hash values that oracle prepares from the statements. These hashes values are stamped sort of into their respective buckets. So simply put, each hash bucket is owned by a hash value containing statements which represent or generate the similar hash value.

As it is said, a picture is worth of thousand words, so I tried to put above said in a pictorial format. Now, I don’t know how many words a bad picture is worth of so I won’t take any guarantee of them being of any number ;-) .

hash table
When a statement is submitted for execution, oracle typically does the following.

1) A tranlation check or aka syntax check is done. This is basically used to check that the written statement’s syntax is correct or not. The whole statement is typically check against the stored grammar of the sql, failing to match with it, its immediately stopped.

2) The second check is done to see the privs over the object. This is to ensure that the respective privs needed to look upon the object are there are available or not. If not, there is no point of going ahead with the query. To do this check, another component of the shared pool, data dictionary cache is used.

Once this part is done, optimizer starts performing couple of steps over the query like, Query Transformation, Estimation and finally, Plan Generation. All these steps are explained in the Performance Tuning guide good enough to have a start so I won’t be going into the details of these over here. Once the last step, Plan Generation is complete, Oracle is ready with the optimized plan that would be used to run the query. This plan is stored in the Library Cache.

As I just mentioned above that the library cache is represented with a Hash table linked to the hash buckets. These buckets contain Library Cache objects which for us mortals, are the cursors that we are looking for. When a request to look upon the cursor that whether it is already cached or not is given, server process acquires the Library Cache Latch and using the hashing algorithm points to the bucket which is supposed to contain the similar hash value. In the bucket, exact text match is done for the query( two queies can give a similar hash value) and if found, the cursor is declared to be found as Soft Parsed, otherwise a Hard Parse is reported leading to which, Shared Pool Latch is used and the optimization of the query starts which I wrote above.

The number of buckets and their initial numbers are taken care by oracle by internal parameters. As like always, there is no need to temper them as long as something very crucial doesn’t come up for which, with Oracle Support’s permission, changes to them is required. If we write good queries, use sufficiently sized shared pool, for which Automatic Shared Memory Manangement( ASMM) or Automatic Memory Management(AMM) are quite helpful, its rather unusual that we have to ever fiddle out the hidden parameters.

Terminologies used for Library Cache

To know how well Library Cache is being used, an external view, v$librarycache is used. There are couple of statistics of it which are used as the foremost terminologies to interpret the usage of Library Cache. These terminologies are simple yet at times, for some they sound confusing. This was actually the question that posted in various threads that what these terms basically mean. So before any added flavor, here is what Oracle docs say about them. Below is the reference of the view from 11gr1 docs,
http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_2.htm#REFRN30115

GETS ==== Number of times a lock was requested for objects of this namespace
GETHITS ==== Number of times an object’s handle was found in memory
GETHITRATIO ==== NUMBER Ratio of GETHITS to GETS
PINS ==== Number of times a PIN was requested for objects of this namespace
PINHITS ==== Number of times all of the metadata pieces of the library object were found in memory
PINHITRATIO ==== NUMBER Ratio of PINHITS to PINS
RELOADS ==== Any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the object from disk
INVALIDATIONS=== Total number of times objects in this namespace were marked invalid because a dependent object was modified

Now, though the descriptions are straight forward but to make them simple and more easier to grasp, a more simpler example would be needed. So here it goes. Assume that Library Cache is a big library where there are lots of books placed and we want a respective book(cursor for us) from this library. Now, we can’t just walk into a library and get the book. We need to get through the librarian first right? So we go to the librarian and ask him that we want Cost Based Oracle Fundamentals book, is it there? He searches the catalogs(hash table) and says, yup, we do have the book. Now he would look into the catalog which almirah(hash bucket) is containing the book. This request that we have given just now is called Get, request for the cursor handle. This would come for the first time for sure. Now, the librarian takes us to the respective almirah which contains the book. We check in there and if we find a book, its a Get Hit. Now we were interested to specifically see the page number 100 of the book. If we open the book and find the page there, its going to be called a Pin Hit, which means that we wanted the particular page of the cursor and its there. If not, we may have to reload the page, which would be termed as the Pin Miss alike that the page number 100 if found torn from its place. If this would happen, we may have to get the page from somewhere else. Similarly , for the page ,we may have to get it from the disk and this essentially is called Reload. The last part can be that the book’s copy that is there in the almirah is the first edition and since its release, there is already a new edition which is out. So we surely would need the new edition only. The same can happen that a cursor is already cached and available in the memory. But someone may do some thing which would make its useless for being reused. For example, someone would create an index over a table , leading an assumption for oracle that the initial cursor containing a full table scan is now may not be optimal. Thus oracle would have to reconsider the newly created index as well, leading to the reoptimization of the already cached cursor. This is called Invaliadation.

If we just check the same from the V$librarycache, it would be clear. I ran the following statement couple of times. You can see that the Pins and Gets are increasing but there is no invalidation or reloads.
SQL> select gets, pins, reloads, invalidations from V$librarycache where namespace='SQL ARE

GETS PINS RELOADS INVALIDATIONS
---------- ---------- ---------- -------------
19894 116294 735 136

SQL> /

GETS PINS RELOADS INVALIDATIONS
---------- ---------- ---------- -------------
19896 116296 735 136

SQL> /

GETS PINS RELOADS INVALIDATIONS
---------- ---------- ---------- -------------
19898 116297 735 136

SQL> /

GETS PINS RELOADS INVALIDATIONS
---------- ---------- ---------- -------------
19900 116298 735 136

SQL> /

GETS PINS RELOADS INVALIDATIONS
---------- ---------- ---------- -------------
19902 116303 735 136

SQL> /

GETS PINS RELOADS INVALIDATIONS
---------- ---------- ---------- -------------
19904 116304 735 136

SQL> /

GETS PINS RELOADS INVALIDATIONS
---------- ---------- ---------- -------------
19906 116305 735 136

SQL> /

GETS PINS RELOADS INVALIDATIONS
---------- ---------- ---------- -------------
19908 116306 735 136

SQL> /

GETS PINS RELOADS INVALIDATIONS
---------- ---------- ---------- -------------
19910 116307 735 136

SQL> /

GETS PINS RELOADS INVALIDATIONS
---------- ---------- ---------- -------------
19912 116308 735 136

SQL>

After this, I waited for some time and than ran the same command. In the meanwhile, a memory chunk may had been flushed out and I got a reload.
SQL> /

GETS PINS RELOADS INVALIDATIONS
---------- ---------- ---------- -------------
19912 116308 735 136

SQL> /

GETS PINS RELOADS INVALIDATIONS
---------- ---------- ---------- -------------
19955 116580 736 136

SQL>

After this I gave a command to gather the system stats which invalidated my already cached cursors and I got Reloads as well as Invalidations.
SQL> /

GETS PINS RELOADS INVALIDATIONS
---------- ---------- ---------- -------------
19955 116580 736 136

SQL> exec dbms_stats.gather_system_stats('start');

PL/SQL procedure successfully completed.

SQL> select gets, pins, reloads, invalidations from V$librarycache where namespace='SQL AREA';

GETS PINS RELOADS INVALIDATIONS
---------- ---------- ---------- -------------
21421 126160 1042 138

SQL>

So any thing done like above would cause the already cached cursors to become invalid and thus being reloaded.