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@xxxxxxxxxxxxxxx> 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@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. >> >> >> > >