出现错误Ora-04030,应该如何解决？

[zcs_1]

ORA-04030 out of process memory when trying to allocate string bytes (string,string)

Cause: Operating system process private memory has been exhausted.

Action: See the database administrator or operating system administrator to increase process memory quota. There may be a bug in the application that causes excessive allocations of process memory space.

错误号的帮助我已经看到，就是不知道如何增加进程内存限额，是否要修改某个初始化参数，或者是增加虚拟内存？

[asdf008]

试试看加大 配置文件中processes的值

[jiezhi]

I think you should give more detail

[jiezhi]

http://www.orafaq.net/archive/comp.databases.oracle.server/2002/07/25/155665.htm

[zcs_1]

to jiezhi(西域浪子) :
http://www.orafaq.net/archive/comp.databases.oracle.server/2002/07/25/155665.htm
这个网页好像不存在，能不能帮忙给copy过来，谢谢。

[lynx]

我来帮忙
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%8.43359$[e-mail]>
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 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" <[e-mail]> wrote in message

news:ahme13$[e-mail]...
> 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" <[e-mail]> wrote
in message
> news:eAu%8.42893$[e-mail]...

[lynx]

> > 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" <[e-mail]> wrote
in message
> > news:Xuu%8.42890$[e-mail]...
> > > 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" <[e-mail]> wrote in message
> > > news:[e-mail]...
> > > > 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" <[e-mail]> wrote in
message
> > > > news:[e-mail]...
> > > > > 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.
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[zcs_1]

谢谢各位的帮忙，这个问题我是替别人问的，现在问题已经解决了，方法就是增大sort_area_size参数。不过根据上面各位的回答来看，引起ora-04030错误的原因很多，sort_area_size太小只是一方面，是这样吧？

[Michaelyfj]

重新启动一下数据库试试

[tarky_z]

to zcs_1,引起ora-04030错误原因确实比较多，我曾经遇到这样的语句也导致这个错误 select * from x where id_a in (1,2,3,……，200) and ind_b in (201,202, ……，400)呵呵，够变态吧！