First page Back Continue Last page Graphics
Shared Pool Retention
Parsed SQL remains until:
- Forced out due to memory constraints
- Structural change (ALTER) made to a referenced object
- ANALYZE or DBMS_STATS on a referenced object
- Unless DBMS_STATS NO_INVALIDATE=TRUE used
- ALTER SYSTEM FLUSH SHARED_POOL
- Instance is shut down
Notes:
Keeping parsed SQL statements in memory as long as possible increases the likelihood of reuse. The size of the shared pool influences how likely it will be for us to be able to keep statements in memory for long periods of time. When space cannot be found in the shared pool for a new statement, Oracle uses a LRU method to free up space. The LRU is the least recently used method – the statement that has not been used for the greatest period of time – is flushed from the pool, freeing up memory for another statement. The DBA controls the size of the shared pool with the SHARED_POOL_SIZE initialization parameter.
Other things that affect the retention of parsed queries include:
Altering an object. An ALTER on an object causes all parsed queries in the shared pool to be flushed.
Collecting statistics. Collecting statistics with ANALYZE or DBMS_STATS (except when the NO_INVALIDATE=TRUE option is used) causes all dependent queries to be flushed.
Forcing the shared pool to be flushed with the ALTER SYSTEM FLUSH SHARED POOL command.
Shutting down the instance.