| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-04030 out of proces memory
romanhodain_at_email.cz wrote:
> 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.
>
Check what Stefan suggested. Remember, ora-04030 is a result of the *operating system* telling Oracle "I haven't got any memory left to give to you." That 3-gb of memory has to be shared by *all* processes running on the box - not just the Oracle instance in question. You have two Oracle instances? That's *two* SGA's and *two* databases worth of PGA's asking for a chunk of that 3-gb. Running anything else? That something else is also asking for memory from that 3-gb. Received on Wed Oct 04 2006 - 16:16:22 CDT
 |
 |