I'm frequently asked how often to refine the pin list. Personally, I don't like to invoke any database change until I have a very good reason. A good reason for refining your pin list is if your system has suddenly started experiencing shared pool latch contention or has encountered 4031 errors. This is very important: From a more proactive posture, refine the pin list if application functionality is added, an application upgrade occurs, or application usage significantly changes.

While not on the top of anyone's list, simply flushing the shared pool will bring some immediate shared pool latch contention relief. This is especially true for pre-Oracle9i systems, when subpools did not yet exist. This is obviously not an optimal solution, because every object not pinned in the shared pool will be removed and its memory deallocated. The initial result may seem counterproductive because it will likely result in immediate and massive hard parsing, which as we know, consumes significant CPU resources and forces an unnatural amount of latching. However, this unfortunate situation will soon subside.

There are times when the combination of shared pool size, Oracle release (pre-Oracle 9i), and application usage will leave the DBA with no choice but to plan periodic shared pool flushes. That is simply the reality of the situation.