Why flush shared pool is required

On systems which use a lot of literal SQL the shared pool is likely to fragment over time such that the degree of concurrency which can be achieved diminishes. Flushing the shared pool will often restore performance for a while as it can cause many small chunks of memory to be coalesced.

After the flush there is likely to be an interim spike in performance as the act of flushing may remove sharable SQL from the shared pool but does nothing to improve shared pool fragmentation. To clear the shared pool, issue the following:

Alter system flush shared_pool;

Real-time scenario

I’ve faced a wired issue during my support for one of the critical customer. Performance of the DB was OK at the beginning of the day and by late of the day performance was totally unacceptable. Investigation showed that application was generating ad hoc SQL without using bind variables. The generation of ad-hoc SQL and the nonexistence of required bind variables were resulting in propagation of non-reusable code fragments in the shared pool. We have asked application team to use more bind variables to resolve such kind of Performance issue for a stable performance.

SGASTAT : Before Flushing the shared pool:

SQL> select * from v$sgastat;
fixed_sga 735504
buffer_cache 3187671040
log_buffer 264192
shared poolerrors 443048
shared poolenqueue 1570544
shared poolKGK heap 33304
shared poolKQR L PO 3083920

SGASTAT : After Flushing the shared pool:

SQL> alter system flush shared_pool;

SQL> select * from v$sgastat;
fixed_sga 735504
buffer_cache 3187671040
log_buffer 264192
shared poolenqueue 1570544
shared poolKGK heap 33304
shared poolKQR L PO 1064000

Note :

Flush shared pool does not clear shared SQL and PL/SQL areas for SQL statements, stored procedures, functions, packages or triggers that are currently being executed or for SQL SELECT statements for which all rows have not yet been fetched.

             As a fix, we have done flushing the shared pool couple of times, resulting that all other query returned again in less than a second. As a permanent fix, we have scheduled a corn job which is firing at the mid night to flush the shared pool.