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@xxxxxxxxx
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@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.
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.
--
//www.freelists.org/webpage/oracle-l
|