Re: ORA-04030 out of proces memory

From: <romanhodain_at_email.cz>
Date: 4 Oct 2006 08:15:15 -0700
Message-ID: <1159974915.379583.55040@m73g2000cwd.googlegroups.com>

EdStevens napsal:
> R. Schierbeek wrote:
> > "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'
> > > );

>

> I stand corrected on PGA_TARGET vs. actual individual PGA.

>

> "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."

>

> Actually, on the project I cited, 5 concurrent users was about the
> limit before getting the 4030. Very poorly designed app, trying to
> alllow large number of users to concurrently submit large,
> sort-intensive batch processes. And it should be pointed out that the
> session getting the 4030 may very well NOT be the culprit. Session A
> does massive reads and sorts, getting nearly all of the available
> memory, then someone tries to connect and gets the 4030 because nothing
> is left for it.

Hi,
i traing set pga target to 200MB and my ptoblem is solved. But i think that this is not very good solution Because some select is slower. Do you know how locate my problem? My os hes 4GB and 3GB swat. and in trace file after error is Ph:2716M/3583M, Ph+PgF:5429M/7013M, VA:906M/2047M and if I select from v$pgastat. The value is ok.

Regards Roman Received on Wed Oct 04 2006 - 10:15:15 CDT