to jiezhi(西域浪子) : http://www.orafaq.net/archive/comp.databases.oracle.server/2002/07/25/155665.htm 这个网页好像不存在,能不能帮忙给copy过来,谢谢。
我来帮忙 Re: ORA-04030 out of process memory [ Index ] [ Raw Message ] From: "Richard Foote" Newsgroups: comp.databases.oracle.server References: <1> <2> <3> <4> <5> Subject: Re: ORA-04030 out of process memory Message-ID: <isJ%[email protected]> Date: Thu, 25 Jul 2002 12:41:28 +1000 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 memory2) 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 memory4) 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" <[email protected]> wrote in messagenews:[email protected]... > 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" <[email protected]> wrote in message > news:eAu%[email protected]...
> > 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" <[email protected]> wrote in message > > news:Xuu%[email protected]... > > > 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" <[email protected]> wrote in message > > > news:[email protected]... > > > > 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" <[email protected]> wrote in message > > > > news:[email protected]... > > > > > 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. > > > > > > > > > > > > > > > > > > > >
http://www.orafaq.net/archive/comp.databases.oracle.server/2002/07/25/155665.htm
这个网页好像不存在,能不能帮忙给copy过来,谢谢。
Re: ORA-04030 out of process memory
[ Index ] [ Raw Message ]
From: "Richard Foote"
Newsgroups: comp.databases.oracle.server
References: <1> <2> <3> <4> <5>
Subject: Re: ORA-04030 out of process memory
Message-ID: <isJ%[email protected]>
Date: Thu, 25 Jul 2002 12:41:28 +1000
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 memory2) 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 memory4) 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" <[email protected]> wrote in messagenews:[email protected]...
> 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" <[email protected]> wrote
in message
> news:eAu%[email protected]...
> >
> > 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" <[email protected]> wrote
in message
> > news:Xuu%[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > 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.
> > > >
> > > >
> > >
> > >
> >
> >
>
>