Monday, April 03, 2006

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.

5 Comments:

At December 20, 2009 at 1:10:00 AM PST, Anonymous Anonymous said...

Can anyone recommend the best Remote Desktop tool for a small IT service company like mine? Does anyone use Kaseya.com or GFI.com? How do they compare to these guys I found recently: N-able N-central script distribution
? What is your best take in cost vs performance among those three? I need a good advice please... Thanks in advance!

 
At May 8, 2010 at 4:00:00 AM PDT, Anonymous http://www.oracledba.in said...

i like this

 
At September 14, 2011 at 12:41:00 AM PDT, Blogger Sara said...

This comment has been removed by the author.

 
At July 13, 2012 at 11:55:00 PM PDT, Anonymous iPhone4S shell telepon said...

I still thought it had been practical. Excellent post!

 
At January 28, 2013 at 9:05:00 AM PST, Blogger Mike said...

Let me try this out on one of our databases regarding changes of Oracle performance after switching to a new SAN. Looks like we have not taken this into account in our Oracle tuning efforts. Thanks for that.
sap upgrade challenges

 

Post a Comment

Links to this post:

Create a Link

<< Home