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.

As the following code snippet shows, flushing the shared pool is very simple, yet the effect is indeed significant.