Re: shared_pool_reserved_size and sga

> 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