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.

The next step is to find the large and/or popular objects. Oracle keeps track of shared pool object usage and makes this information easily available via the v$db_object_cache view. Figure 7-14 shows the output from the OSM script dboc.sql, which is used to identify potential objects. You're likely to see a group of packages that are much larger than the rest and also packages that are executed much more frequently than others. There may also be objects that you personally know have unusual execution profiles and you want to have cached.