Re: ORA-04030 out of process memory

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:40:17 CDT