Reset Of Kept Packages
Many times, for one reason or another, databases
are periodically shutdown and restarted. The shutdown of a database
releases the kept objects and this kept object information is not
retained. Thus, upon restart, the objects must be re-kept. For those
not sure what this process entails, let me explain. Each PL/SQL
object, whether it be a procedure, function, package or trigger, is
stored in the shared pool library caches after it is used. If the
objects in the shared pool aren’t re-used, then they are eventually
aged out by means of a modified least recently used (LRU) algorithm.
Sometimes, objects that shouldn’t get aged out do, resulting in a
performance hit when they must be recalled from disk. In order to
prevent aging of objects out of the shared pool Oracle has provided
a package called DBMS_SHARED_POOL that contains a procedure KEEP
that is used to flag an object as KEPT preventing it from being aged
out of the pool.
By creating a table that holds the names of kept
objects, we can create a PL/SQL procedure that periodically wakes up
and if required, re-issues the command to keep various objects in
the shared pool. Figure 4 shows the structure of an example table.
