Re: ORA-04030 out of process memory

Hi (fellow) Dicky,

I think reducing your memory consumption is the key here. This is my brief, high level, bottom up, firefighting suggestions :)

1. What's your value for mts_dispatchers (ie, how many of these babies have you got running) ? Check in V$dispatchers and see what the busy/idle ratio is. If low, consider reducing the number of dispatchers. Saved memory
2. Have you checked out the value of servers_highwater in V$mts ? If only 100, then 100 is more than sufficient and consider reducing even further. Also monitor the wait statistics is v$queue and check that any changes you make have no detrimental effect here. Saved Memory.
3. large_pool_size. Have you checked out the free memory stat as previously suggested ? If significant, reduce the large_pool_size by this figure, minus "a bit to spare". Saved memory
4. shared_pool_size. If you have a statspack report handy (that compares statistics over a typically busy period), check out the reloads and pinhitratios. Very *simplistically*, if reloads is comparatively low, and if pinhitratio is high, consider reducing (then remonitor and see no detrimental effect). Saved memory.
5. db_block_buffers. Check out the physical reads in statspack report (again ensure this cover a busy period). Very *simplistically*, see if reducing the number of buffers doesn't impact significantly on the physical reads. Saved Memory.
6. What are the values of all the various *.area.size parameters. If high, and enough sessions are using these memory values, might be memory you can't afford. Consider reducing.

It's a start ...

Unfortunately, many of these changes can't be implemented without bouncing the DB. But by effectively reducing any inefficient memory usage, you should reduce such problems occurring.

I hope this heaps a tad. Good luck with it all.

Richard

"Dicky" <ccmdicky_at_netvigator.com> wrote in message news:ahme13$3pv2_at_imsp212.netvigator.com...
> 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 - 21:41:28 CDT