Shared Pool Fragmentation !!!

• Shared Pool Fragmentation:
  Every time a SQL or PL/SQL statement needs to be executed the parse representation is loaded in the library cache requiring a specific amount of free contiguous space. The first resource where the database scans is the free memory available in the shared pool. Once the free memory is exhausted, the database looks for reusing an already allocated piece not in use. If a chunk with the exact size is not available, the scan continues looking for space based on the following criteria:

- The chunk size is larger than the required size
- The space is contiguous
- The chunk is available (not in use)

Then that chunk is split and the remaining free space is added to the appropriate free space list. When the database is operating in this way for a certain period of time the shared pool structure will be fragmented.

When the shared pool is suffering fragmentation ORA-04031 errors (when the database cannot find a contiguous piece of free memory) may occur. Also as a concequence , the allocation of a piece of free space takes more time an the performance may be affected (the "chunk allocation" is protected by a single latch called "shared pool latch" which is held during the whole operation). However, ORA-4031 errors don't always affect the performance of the database.

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:

o Not sharing SQL
o Making unnecessary parse calls (soft)
o Setting session_cached_cursors too high
o 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: