Whenever you experience performance problems relating to space consumption in the shared pool, you can issue the
ALTER SYSTEM FLUSH SHARED_POOL
command. If you are experiencing slow performance due to shared pool fragmentation, this can dramatically improve performance.
The first time you flush the shared pool, Oracle flushes all "first-tier" (i.e. non-pinned) items from library cache. If you re-flush, Oracle will flush the second-tier
memory structures, including all pinned packages. In short, if you pin all frequently-used items in packages they will not be flushed by the FLUSH command, and this will have minimal overhead.
If the SHARED_POOL_SIZE is large enough, most ORA-04031 errors are a result of dynamic sql fragmenting the shared pool This can be caused by:
- Not sharing SQL
- Making unnecessary parse calls (soft)
- Setting session_cached_cursors too high
- Not using bind variables
To reduce fragmentation you will need to address one or more of the causes described before.
In general to reduce fragmentation you must analyze how the application is using the shared pool and maximize the use of sharable cursors.
The following views will help you to identify non-sharable versions of SQL/PLSQL text in the shared pool: