Re: ORA-04030 out of process memory

Thanks to Richard. In fact, I'm a newbie to use MTS. As mentioned in documentation, a reasonible large of large_pool_size is good for MTS. Since most of our connections are via MTS and leverage the response time in a surge, I set 200 to the value of mts_servers at first. When the problem was becoming more serious, I dynamically altered the value to 100 but didn't help anyway.

Currently my box has 2G phyiscal memory and 6G virtual memory. According to vmstat and top, I couldn't find any memory contention in OS level. And all memory parameters and "ulimit" in OS level should be fine compared to the Oracle's recommandation.

After further diagnosis (running a "create as select" sql statment to create a new temp table containing 10 rows without BOLB field), I got a queer in the following martix:

                                                              MTS
Dedicated
Running on the Oracle box via SQLNet          OK                   OK
Running on remote via SQLNet                 Failed                   OK

• Error Dump ************* SQL> create table tmplwimusr01 as select * from lwim_user; create table tmplwimusr01 as select * from lwim_user * ERROR at line 1: ORA-04030: out of process memory when trying to allocate 270336 bytes (callheap,kllcqgf:kllsltba)

  ---

At first I suspected the problem should somehow relate to MTS, memory leaking ... but now ...

Anyway I'll do more diagnosis to clarifty the root cause. At the same time, finding a way to dynamically redure the large_pool_size without restarting the DB. I do believe the problem will be disappeared after restarting the DB, don't you. BTW, I have statpack data on hand but which section should i focus on?

Any input would be greatly appreciated.

Thank again!!

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:eAu%8.42893$Hj3.129461_at_newsfeeds.bigpond.com...
> 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 - 09:36:33 CDT