It is normally OK to flush the shared pool periodically, however there are a few bugs that can bite you under Oracle7. If you do it, consider using something like nice_shared_pool_flush.sql to avoid losing cached sequence numbers.

The impact of flushing is not normally too severe. Pinning packages helps to reduce that impact. The script keeper.sql also keeps sequences and the most useful SQL. Such extensive keeping not only reduces the impact of flushing, it also limits fragmentation which helps to make shared pool management easier.

In a well managed shared pool, flushing should not be needed. However, using literals makes the job that much harder. Often reducing the size of the shared pool is all that's needed. An upgrade to 8.1.6 can do wonders too, even apart from the using cursor_sharing facility.

By the way, the problem is not that the shared pool gets full - it's meant to; it's a cache. Rather the problem is that the fragmentation of the free space in the shared pool causes contention for the shared pool latch because it takes too long find free space relative to the number of times that that operation has to be done.

We had a problem in production today - the shared pool got filled up. We had to bounce the instance.

My question is - is it a recommended DBA activity to flush the shared pool periodically?

FYI, our database is hit by a lot of dynamic SQLs which do not have literals. Also, we have some humongous packages, which are currently not being pinned in the shared pool.