Automating the Shared Pool Flush
The flush_it procedure accepts one input value,
the percentage full at which to flush the pool. When submitted, the
flush_it procedure calculates the amount of shared pool being used
based on values extracted from the V$SGASTAT view and compares this
to the overall size specified for the shared pool in V$PARAMETER. If
the ratio between these two values exceeds the specified percent
full, the DBMS_SQL package is used to issue the ALTER SYSTEM FLUSH
SHARED_POOL command to the database. Once the pool is flushed an
entry is appended into the Remote DBA_RUNNING_STATS table to show that a
flush was initiated.
The flush_it procedure is scheduled for whatever
frequency is desired, usually on the hour or half hour. The command
to submit the flush_it procedure to flush the pool when it reaches
95%, to be checked hourly, would be:
