Re: shared memory problem (ORA-04031)

Walter Dorninger wrote:
>
> it is mts...
>
> "Hakan Eren" <heren_at_home.com> wrote in message
> news:3954E11D.468922DB_at_home.com...
> > hi,
> >
> > what do you mean? let me be clear
> > 1. it is mts then it may be a solution or
> > 2. the system is not mts so large_pool_size is not a concern?
> >
> > please clarify.
> >
> > Hakan
> >
> > Connor McDonald wrote:
> > >
> > > Hakan Eren wrote:
> > > >
> > > > hi,
> > > >
> > > > 90% I am sure this problem is not related to shared_pool_size. There
 is
> > > > another
> > > > parameter (related to it though) large_pool_size.
> > > > Probably it is 600K now. Make it a bigger number like 2097152 (or 0)
> > > > in your init<sid>.ora.
> > > >
> > > > then bounce your database with new parameter.
> > > >
> > > > Regards
> > > >
> > > > Hakan
> > > >
> > > > Connor McDonald wrote:
> > > > >
> > > > > Walter Dorninger wrote:
> > > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I'm currently running a Oracle 8.1.5 on Solaris 2.6 ( E450 2GB
 ram) and if i
> > > > > > run some certain statements I get the following error (not very
 large
> > > > > > statements):
> > > > > >
> > > > > > ORA-04031: unable to allocate 131072 bytes of shared memory
 ("shared pool",
> > > > > > "unknown object", "cursor work he", "QERHJ Hash Table")
> > > > > >
> > > > > > I have set the initora parameters listed below to the following
 values:
> > > > > > ...
> > > > > > shared_pool_size = 20000000
> > > > > > sort_area_size = 3000000
> > > > > > sort_area_retained_size = 500000
> > > > > > ...
> > > > > >
> > > > > > Another issue is that the sort operations are extremly slow ...
 (perhaps
> > > > > > this has to do something with this...)
> > > > > >
> > > > > > Is it possible that the error is related to the settings in my
 /etc/system
> > > > > > file ? - The /etc/system file is listed below:
> > > > > > set shmsys:shminfo_shmmax=4294967295
> > > > > > set shmsys:shminfo_shmmin=1
> > > > > > set shmsys:shminfo_shmmni=100
> > > > > > set shmsys:shminfo_shmseg=10
> > > > > > set semsys:seminfo_semmni=100
> > > > > > set semsys:seminfo_semmsl=100
> > > > > > set semsys:seminfo_semmns=200
> > > > > > set semsys:seminfo_semopm=100
> > > > > > set semsys:seminfo_semvmx=32767
> > > > > >
> > > > > > Thanks a lot
> > > > > > Walter
> > > > >
> > > > > Time to crank up your shared pool, but possibly more importantly is
 to
> > > > > look at which cursor, plsql and sequence objects are being used
> > > > > frequently and pin them into the pool.
> > > > >
> > > > > If you're running 24x7, an occasional flush of the SGA may also help
> > > > >
> > > > > HTH
> > > > > --
> > > > > ===========================================
> > > > > Connor McDonald
> > > > > http://www.oracledba.co.uk
> > > > >
> > > > > We are born naked, wet and hungry...then things get worse
> > >
> > > Really depends if they are running MTS or not.
> > >
> > > Cheers
> > > --
> > > ===========================================
> > > Connor McDonald
> > > http://www.oracledba.co.uk
> > >
> > > We are born naked, wet and hungry...then things get worse
> >

If it's MTS, then you want to increase the large pool

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse