RE: shared_pool_reserved_size and sga

Shared_pool_reserved_size needs to be 25%-30% of shared_pool_size I believe.  
If you get the the "Unable to allocate "x" bytes ... shared_pool" messages 
typically you've exhausted contiguous space in the shared_pool.  (There's a 
pretty good rule of thumb out there somewhere on the ratio)

There's all kinds of things that fragment the shared_pool - exports, queries 
etc.  (The exports bit me one time as I was running one every night and by the 
end of the week I was getting the unable to allocate 'x' bytes of space in 
shared_pool messages)

Just thought I'd throw that out there.


Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: chris.taylor@xxxxxxxxxxxxxxx<mailto:chris.taylor@xxxxxxxxxxxxxxx>

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and 
may also be privileged. If you are not the named recipient, please notify the 
sender immediately and delete the contents of this message without disclosing 
the contents to anyone, using them for any purpose, or storing or copying the 
information on any medium.

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Ram Raman
Sent: Thursday, August 19, 2010 3:34 PM
To: ORACLE-L
Subject: shared_pool_reserved_size and sga

Hi

We have an application running on 10.2 with ASMM which was recently upgraded to 
a newer version of the application, still running 10.2. After the upgrade we 
got some errors related to shared pool. We tried flushing shared pool. The 
error would go away temporarily but would be back after a while. Last week we 
bumped up the shared_pool_reserved size to 260MB from the default size. It was 
ok for a while, but then we ran into errors with shared pool again, which 
prevented us from logging into the database. We had to bounce the instance. It 
seems ok for now. Is it alright to increase the SPRS further or do I have to 
worry about latch contention.

Querying shared_pool_reserved view after the bounce an hour ago shows us that 
the request failures is 28. Our sga target is 4.5Gb now.

Thanks.

Other related posts: