There are also times when we may want to pin small objects. For example, suppose an object has a pattern of intense activity, a long pause causing the object's memory to be deallocated, and then another period of intense activity. To ensure there is no application delay and to reduce memory management, we can simply pin the object. When we force objects to remain in the shared pool, keep in mind that in a very real sense, we are gaming Oracle's shared pool LRU algorithm. But sometimes this is what it takes.

Most large Oracle applications provide a script containing the objects to be pinned in the shared pool, and they will recommend it be run immediately after the instance has started. It's important to know that even if your application vendor provides such a list, you can refine this list by understanding how your organization actually uses the objects. It is common for the vendor application developers to create the pin list. However, most application developers think their objects are the most important and should always be pinned. But in reality, many times no one really knows how your organization will use the application until it is operational in a production environment. So if 4031 errors are occurring, it is always a good idea to refine the pin list.

There are four straightforward steps to ensure the objects you want are always kept in the shared pool. While the word pin is commonly used, the dbms_shared_pool package's keep function is used to ensure the object is kept in the shared pool. This package is not loaded by default when the database is created, so your first step is to load it. The following code snippet is an example of how to create the procedure.