The problem is fragmentation in the SGA. There is an undocumented init
parameter that I used to get around
some of these issues :
#### Work around for BAMIMA Buffer (4031) errors ####
#_db_handles_cached = 0

You should also pin some of the pl/sql that is getting reloading a lot. I
would also look to see that you are
using bind variables in the sql and not reissuing the same sql over and over
again.

Here's a couple of statements to help you find the sql that is a good
candidate for pinning

To determine what large PL/SQL objects are currently loaded in the shared
pool
and are not marked 'kept' and therefore may cause a problem, execute the
following:

COLUMN OWNER FORMAT A10
COLUMN NAME FORMAT A32
COLUMN sharable_mem FORMAT 9999999
COLUMN LOADS FORMAT 9999999
COLUMN EXECUTIONS FORMAT 9999999
select substr(owner,1,10) OWNER ,substr(name,1,32) NAME ,
sharable_mem,loads,executions
from v$db_object_cache
where sharable_mem > 100
and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
or type = 'PROCEDURE')
and kept = 'NO'
order by owner,loads desc;


--- Objects that have been reloaded ---
COLUMN OWNER FORMAT A10
COLUMN NAME FORMAT A32
COLUMN sharable_mem FORMAT 9999999
select substr(owner,1,10) OWNER ,substr(name,1,32) name,
sharable_mem,loads,executions
from v$db_object_cache
where loads > 1
and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
or type = 'PROCEDURE')
and kept = 'NO'
order by owner,loads desc;

You can continue to increase the size of the shared_pool, but that is just
masking the problem.

Gregg

----- Original Message -----
From: "dbaguru" <oracledba-ezmlmshield-x8159818.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Wednesday, January 03, 2007 6:24 PM
Subject: Re: Error message in the Alert Log


> Frank,
>
> Your issue is identified in the ORA-04031 error message. Your SGA needs
to be adjusted in the shared pool by at least 20,204 bytes. I'd recommend
adjusting up beyond that, change your pfile/spfile and bounce your instance
if required.
>
> Nick
>
> -------------- Original message --------------
> From: "Frank Cavaliero " <oracledba-ezmlmshield-x41839474.[Email address
protected]
>
> > Hi all,
> >
> > I received the following error message in my alert_log (and I do have
> > the trace file that contains the SQL statement that apprately caused
> > this issue):
> >
> > Errors in file c:\oracle\admin\whit\udump\whit_ora_7992.trc:
> > ORA-00600: internal error code, arguments: [736], [3], [86705544],
> > [128], [128], [], [], []
> >
> > Tue Jan 02 13:59:00 2007
> > Errors in file c:\oracle\admin\whit\udump\whit_j000_6616.trc:
> > ORA-04031: unable to allocate 35336 bytes of shared memory ("shared
> > pool","unknown object","sga heap(1,0)","trace buf hdr xtend")
> >
> > Tue Jan 02 13:59:02 2007
> > Process J000 started up but failed with error = 447
> > Tue Jan 02 13:59:04 2007
> > Errors in file c:\oracle\admin\whit\udump\whit_j000_5860.trc:
> > ORA-04031: unable to allocate 20284 bytes of shared memory ("shared
> > pool","unknown object","sga heap(1,0)","session param values")
> >
> > This SQL statement runs very frequently and was hoping someone could
> > shed some light as to why this may be happening.
> >
> > Thanks,
> >
> > Frank Cavaliero
> > Technologist
> > CrunchTime! Information Systems
> >
> >
> >
> > ---------------------------------------------------------------------
> > TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> > To post a dba job: http://jobs.lazydba.com
> > To Subscribe : http://www.LazyDBA.com
> > To unsubscribe: http://www.lazydba.com/unsubscribe.html
> >
> >
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>


Oracle LazyDBA home page