How to pin objects in the Oracle's Shared Pool

Hi guys,

I've decided to stop TFS posts, which seems to have most adherents, for a while and go back to the other topics.
That's why in this post I'm going to write about Oracle's Shared Pool and the way it is being used and also introduce a package named "DBMS_SHARED_POOL".
So if you already know what it is that I’m going to talk about this time, you better don't get your mind dirty here.
Every Oracle Instance has one big memory structure called SGA (System Global Area). It can vary in size depending on the server's memory capabilities.
SGA is divided into various pools and one of its critical pieces is Shared Pool. Critical in term of performance. A wrong sized Shared Pool can easily make the system looks like dead.
There are many pieces of program data that Oracle keeps in Shared Memory. That's what it is.
For example, as you might know, executing a query is not a very simple process as database engine has to parse the query and find the most efficient way to execute it and do IO stuff and retrieve actual data.
So to not parse every single query, Oracle keeps the parsed queries in the Shared Pool. The next time that it needs to parse a query, it first looks at the Shared Pool to find out whether that query has been parsed or not. If it has, it reads the data from Shared Pool and continues.
Let's say we have 100 users all executing a same piece of program. The first execution, caches the parsed query and others just use it. So Shared Pool not only caches data but also shares them.
As you could imagine, Shared Pool can not keep programs' data for ever. Sometimes it needs to take some of them out to make space for new data. Under those circumstances Oracle uses a LRU algorithm to erase data. LRU stands for Least Recently Used and it means if Oracle needs to make space, it will take those parts that have been used least out. That's what is called Aging Out. That peace of memory has been aged out.
But sometimes it happens you need to keep a piece of memory in the Shared Pool and make sure that it won't be aged out.
When? Well, if you have a large procedure that will be used frequently you might want to load it up at database startup time and keep it in Shared Pool.
Another example can be a shared sequence that you don't want to lose it. Sequences are objects that are used to create autonumber fields. So if there is a table with an autonumber field in it, that users inserting records there (a log table for example), you can create a sequence and pin it to the Shared Pool to prevent the loss of sequence number.
You are probably asking how to do that? That's what I wanted to say and introduce the DBMS_SHARED_POOL package. This package has a procedure called KEEP. Using KEEP, you can make an object to stick to the Shared Pool. But not all kind of objects. Only Packages, Functions, Procedures, Sequences, Triggers and Types.
If you want to undo the KEEP, you need to use UNKEEP procedure from the same package.
If you want to know more about that package, here is a link.
By the way, if you don't have this package in your database, you can create it by running the
DBMSPOOL.SQL. You can find it at $ORACLE_HOME/rdbms/admin.
As you saw, this post unlike the others was quiet simple. But the idea of controlling objects' aging in Shared Pool was the thing that made me to write it down.

Comments are welcomed. Please leave some.