Re: ORA-04030 out of proces memory

From: R. Schierbeek <byteNospam_at_gmail.com>
Date: Wed, 4 Oct 2006 08:48:43 +0200
Message-ID: <45235975$0$19635$dbd4f001@news.wanadoo.nl>

"EdStevens" <quetico_man_at_yahoo.com> wrote >

> romanhodain_at_email.cz wrote:

>> EdStevens napsal:
>> > romanhodain_at_email.cz wrote:
>> > > Hi,
>> > > i read several discusion interested in this problem but did not find
>> > > solv my problem.
>> > >
>> > > I have Oracle 10g R2 on windows 2003 with 4G MB RAM.
>> > > My SGA target is set to 700MB
>> > > My PGA target is set to 1GB
>> > > workarea_size_policy is AUTO
>> > >
>> > > I have 2 instance of Oracle. Instance NMCRL and MCAT. In instance
>> > > MCAT
>> > > is created synonym and db link to instance NMCRL. Our aplication is
>> > > connected in MCAT and aplication inserting very mauch items to table
>> > > in
>> > > NMCRL. It is about 40 000 000 items. When the import is in about 50%,
>> > > the anothe queries fall down with error ORA-04030 out of proces
>> > > memory.
>> > > For example:

< snip>

>> > > 11014,7783
>> > > extra bytes read/written
>> > > 742,87207
>> > > cache hit percentage
>> > > ,00008934
>> > > recompute count (total) ,027444839
>> > >
>> > > Where is the problem? Can you help my please?
>> > > Best regards
>> > > Roman Hodain
>> > >
>> > > And sorry my English is not very goot.
>> >
>> > ORA-04030 means the OS has no more memory to give to the process.
>> >
>> > There is a very good discussion of this error in MetaLink
>> > Note:233869.1. Also, required reading is note 46001.1 "Oracle Database
>> > and the Windows NT memory architecture"
>> >
>> > We went round-and-round about this on a previous project. Bottom line
>> > is since Windows is a 32-bit OS, you are faced with a hard 4-gig limit.
>> > And left to its own devices, Windows will take half of that for
>> > itself, leaving on only 2-gb for everything else. You can set an os
>> > parm to tell windows to leave 3-gb for other processes, but that's it.
>> > Your SGA and all of your PGA's (remember, one pga for each connection)
>> > come out of that 2 (or 3) gig. Too many processes and you get
>> > ora-04030, regardless of 'max processes'.

>>

>> Hi,
>> I read some document on metalink. My os is switch to 3GB RAM. But i
>> think that Oracle allocate SGA + PGA Memory and it is in my instance
>> 700MB + 1GB. It is below 2G. Is it my hypothese goot? Or exist any
>> proces with diferent memory?
>> Best regards
>> Roman Hodain

>
> So you've got a 700mb SGA, and a 1-gb PGA.  And how many concurrent
> sessions, each getting up to 1-gb for their PGA?  Remember, the PGA is
> *per session*.  So, if you have 5 concurrent sessions, each with a 1-gb
> PGA, that's
>
> SGA = 0.7 gb
> + session 1, PGA=1-gb
> + session 2, PGA=1-gb
> + session 3, PGA=1-gb
> + session 4, PGA=1-gb
> + session 5, PGA=1-gb
> = 5.7gb

The PGA_target is not *per session*. The pga_session is per session. Roman's PGA_target is set to 1GB. This parameter is just a TARGET for all users cumulative pga memory; and the pga_session memory per user will take a fraction of that - perhaps 5%. There's no way user processes will allocate
100% of this target each ; otherwise you wouldn't be able to run more then 5 or 10 users on any database server.
In the above example the 5 users would allocate a maximum of 50 to 100Mb pga_sessions memory each so a total of 0,5 Gb. PGA memory.

Perhaps 1 user's pga_session memory will grow excessively and experience the ORA-04030 error, but that's another matter.

> Try running this query several times while your 'problem' process runs:
> select sum(bytes)/1024/1024 Mb from
>      (select bytes from v$sgastat
>        union
>        select value bytes from
>             v$sesstat s,
>             v$statname n
>        where
>             n.STATISTIC# = s.STATISTIC# and
>             n.name = 'session pga memory'
>       );

Received on Wed Oct 04 2006 - 01:48:43 CDT