Re: shared_pool_reserved_size and sga

From: Tim Gorman <tim_at_evdbt.com>
Date: Thu, 19 Aug 2010 20:47:59 -0600
Message-ID: <4C6DECDF.7090707_at_evdbt.com>



  
    
  
  
    Ram,

To my earlier point, there is nothing in this error message which indicates anything involving "_shared_pool_reserved_size".  The Shared Pool Reserved Area is for large objects, typically larger than 4100 bytes (i.e. parameter "_shared_pool_reserved_min_alloc").

So, you're getting ORA-04130 error messages for allocations of 32 bytes.  What happening to SHARED_POOL_SIZE over time?  If you query DBA_HIST_PARAMETER where PARAMETER_NAME = 'shared_pool_size', what is happening to the VALUE over time?  Is it increasing or is it holding steady during the time periods when you're getting the ORA-04031 errors?  If so, why would that be?  Is it possible that you've got all of the other SGA-related parameters (i.e. DB_CACHE_SIZE, LARGE_POOL_SIZE, etc) set so that there is no room for Auto SGA Management to increase SHARED_POOL_SIZE when needed?

Seek out actual supporting information from your database.  There's a lot of it, especially in the DBA_HIST_xxxx views which comprise AWR.  Get to know them and what they contain.

Hope this helps...
Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => P.O. Box 630791, Highlands Ranch CO  80163-0791
website    => http://www.EvDBT.com/
email      => Tim_at_EvDBT.com
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...

On 8/19/2010 3:35 PM, Ram Raman wrote:
Resending:


 
Thanks Chris and Tim.
 
We started getting these kinds of errors suddenly and logging into the production database was not possible. We had to bounce the DB:
 
Thu Aug 19 14:07:42 2010
Errors in file /psoft/oracle10/app/admin/PRD/bdump/PRD1_q000_1339544.trc:
ORA-22303: type "SYS"."AQ$_HISTORY" not found
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select obj# from oid$ where ...","sql area","tmp")
Thu Aug 19 14:07:42 2010
Errors in file /psoft/oracle10/app/admin/PRD/bdump/PRD1_q001_741446.trc:
ORA-22303: type "SYS"."AQ$_HISTORY" not found
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select obj# from oid$ where ...","sql area","tmp")
Thu Aug 19 14:07:42 2010
Errors in file /psoft/oracle10/app/admin/PRD/bdump/PRD1_q000_2068724.trc:
ORA-22303: type "SYS"."AQ$_HISTORY" not found
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select obj# from oid$ where ...","sql area","tmp")
Thu Aug 19 14:07:43 2010
Errors in file /psoft/oracle10/app/admin/PRD/bdump/PRD1_q001_1106142.trc:
ORA-22303: type "SYS"."AQ$_HISTORY" not found
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select obj# from oid$ where ...","sql area","tmp")
 
----------------------------------------------
 
By the way, we altered the shared_pool_res_size, no underscore before that. I am not aware of _shared_pool_res_size parameter.
 
 
Thanks
 

 
On Thu, Aug 19, 2010 at 4:16 PM, Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com> wrote:

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_at_ingrambarge.com

 

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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.

 



-- http://www.freelists.org/webpage/oracle-l Received on Thu Aug 19 2010 - 21:47:59 CDT

Original text of this message