Oracle blog

How much shared_pool_reserved_size?

What is shared_pool_reserved_size:

Shared pool memory is allocated in chunks and as shared pool fragments with time you will see more smaller chunks being allocated for all sql statements requiring hard parse.

So, to reduce shared pool fragmentation due to large memory allocation requirements, Oracle reserves a minimum of 5% shared pool size for such allocations.

Any chunk allocation with more than "_shared_pool_reserved_min_alloc = 4400" bytes, would go to reserved shared pool unless such memory allocation can be satisfied from the shared pool memory without aging out any other chunks.

Interestingly all sessions connected to the database require 27224 bytes (in Oracle 9i) each from shared pool, which is called "session param v" chunk, and it has to come out from one chunk.

If you have an application which keeps closing and opening new connections, you would need to set minimum shared_pool_reserved_size as follows:

Maximum of (shared_pool_size*(5/100), "sessions" * 27224)

This is one more reason why it is a good idea to use connection pooling and don't bother much about above equation.

You can check the utilization of shared pool reserved size by querying v$shared_pool_reserved view.

posted by Saibabu Devabhaktuni @ 7:45 AM