How to pin objects in the Oracle's Shared Pool

In this post I'd like to write about Oracle's Shared Pool and the way it is being used and also introduce a package named "DBMS_SHARED_POOL".

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 look dead.There are many pieces of program data that Oracle keeps in Shared Memory.
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 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 that 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.DBMS_SHARED_POOL 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.
More information about the package can be found in Oracle documentation.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.