Re: ORA-04030 out of process memory

Hi Dicky,

Forget the last piece of advice regarding shared servers. 100 would be the minimum started. You need to reduce this number and set an appropriate value for mts_max_servers.

Cheers

Richard
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:Xuu%8.42890$Hj3.129179_at_newsfeeds.bigpond.com...
> Hi Alistair,
>
> Dropping shared_pool_size to 1M (you're suppose to use a smiley face when
> you're joking :)
>
> Dicky, it's very difficult to suggest what should be appropriate values
for
> these parameters without knowing the tuning statistics on how you're
> database is performing.
>
> I could suggest that the large_pool_size looks large, as does the log
buffer
> and number of shared servers. But I'm only guessing without having a
> statspack report to look at.
>
> As memory is obviously the problem, and the large_pool_size is quite
> significant, let me focus on it first.
>
> Run this query:
>
> select * from v$sgastat where pool = 'large pool';
>
> The free memory value is the amount of free memory in the large pool. If
> this value is significant, then reduce the large_pool_size appropriately.
> That might help a tad.
>
> Also look in v$mts and check out the servers_highwater. This is the
maximum
> number of shared servers used and will give you an idea if 100 is
> appropriate.
>
> It's a start :)
>
> Regards
>
> Richard
> "Alistair Thomson" <thomson_alistair_at_yahoo.co.uk> wrote in message
> news:1027501883.24543.0.nnrp-10.3e311022_at_news.demon.co.uk...
> > Hi
> >
> > Try dropping the shared_pool_size to 1 Mb or so and the
> > shared_pool_reserved_size even lower. Should sort the problem.
> >
> > Alistair
> >
> >
> > "dicky" <dickymok_at_esdlife.com.hk> wrote in message
> > news:fd37ae0f.0207240048.689755ae_at_posting.google.com...
> > > Hi there,
> > >
> > > Recently my Oracle box is encoutering ORA-04030 oracle error while
> > > creating a small-size table without any bolb field (as select
> > > statment) or open a cursors using MTS connection; however, the problem
> > > dose not occur if the connection is using "DEDICATE" rather than
> > > "SHARE". The box was up and running more 50 days without any glitch.
> > > But now (80+ days) I have to run these manipulations on another Oracle
> > > box, which seriously affect the overall system performance. Because
> > > the box is on production, I can't just restart it without any
> > > supportive reason. Any idea would be greatly helpful.
> > >
> > > Below are the MTS and memory parameters of that box:
> > > MTS
> > > ===
> > > mts_max_dispatchers integer 200
> > > mts_max_servers integer 500
> > > mts_multiple_listeners boolean FALSE
> > > mts_servers integer 100
> > > mts_sessions integer 4995
> > >
> > > MEMORY
> > > ======
> > > db_block_buffers integer 20480
> > > log_buffer integer 10485760
> > > java_pool_size string 32768
> > > large_pool_size string 400M
> > > shared_pool_reserved_size string 10M
> > > shared_pool_size string 200M
> > >
> > > Total System Global Area 873509288 bytes
> > > Fixed Size 94632 bytes
> > > Variable Size 695140352 bytes
> > > Database Buffers 167772160 bytes
> > > Redo Buffers 10502144 bytes
> > >
> > > After reviewing the performance log, I couldn't figure out any
> > > contention in the OS/Oracle point of view. No excessive swapping, no
> > > execssive disk activity, no any bdump/udump trace log ... Only thing I
> > > would say is the buffer cache hit ratio is gradually dropping from
> > > 91.2(Day One) to 86.56(Now).
> > >
> > > Please help me to tackle this problem. Thanks in advance.
> >
> >
>
>
Received on Wed Jul 24 2002 - 04:45:56 CDT