RE: ora-4030 pga memory allocation running wild

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Tue, 23 Dec 2003 07:39:32 -0800
Message-ID: <F001.005DAE78.20031223073932@fatcity.com>

Content-Type: multipart/alternative;
boundary="----_=_NextPart_001_01C3C96A.5ABB46EF" ------_=_NextPart_001_01C3C96A.5ABB46EF
Content-Type: text/plain

This is scary, I'm planning to upgrade 9.2.0.4 from 9.2.0.2.

I don't know how removing pga_aggegrate_target will help reducing memory!!

Does the program have any memory tables, etc?

Did you monitor the PGA size from the Oracle side using v$sesstat?

A sql by itself can't consume this memory except there is a major bug some where, which I doubt!

Please keep us updated.

Thanks

Waleed
-----Original Message-----
Sent: Tuesday, December 23, 2003 10:24 AM To: Multiple recipients of list ORACLE-L

Hi,

I have an ora-4030 problem related to pga memory allocation, at least I have concluded sofar
This program is batch written in pl/sql and after an hour or so it crashes. PGA allocated is slowly exceeding
2Gb and when I monitor with top I see the process size rising uptill 2 Gb somewhere.
Last week we migrated from on oracle 7 environment where this program ran smoothly for years.
At the same time we migrated the OS also and started with new machines. The ux kernel parameter
for max data segment size is 2Gb.

I had an oracle consultant here for migration and he advised to put pga_aggegrate_target on 250M. Box has
4Gb, shared_pool_size is 250Mb, SGA is almost 800Mb

I issued a tar and Oracle advised me to remove pga_aggegrate_target from the init_file, but because this is production I cannot restart that easily (online changes are allowed ony from min. value 10M) I also tested this program with event : alter session set events '4030 trace name errorstack level 3'; I found the so called SQL-statement that might be causing this but explaining this plan gave me an even better plan than on the oracle 7 environment Oracle support still has to get back to me with latest things.

This program is clearly running wild on memory. Based on the docs on metalink I lowered the pga_aggegrate_target to 160M now and I'm testing this right now. Is there any way to protect your system from memory consumption like this case. Are there any other parameters to consider?

Details: oracle 9.2.0.4 HPUX 11.11, 4Gb phys memory

Thanks in advance,

Jeroen

------_=_NextPart_001_01C3C96A.5ABB46EF
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML xmlns=3D"http://www.w3.org/TR/REC-html40" xmlns:o =3D=20 "urn:schemas-microsoft-com:office:office" xmlns:w =3D=20 "urn:schemas-microsoft-com:office:word"><HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3DUS-ASCII">

<META content=3DWord.Document name=3DProgId>
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<META content=3D"Microsoft Word 10" name=3DOriginator><LINK=20

href=3D"cid:filelist.xml_at_01C3C971.3C55CFD0" rel=3DFile-List>
<STYLE>@page Section1 {size: 595.3pt 841.9pt; margin: 70.85pt 70.85pt = 70.85pt 70.85pt; mso-header-margin: 35.4pt; mso-footer-margin: 35.4pt; = mso-paper-source: 0; }
P.MsoNormal {

FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"; = mso-style-parent: ""; mso-pagination: widow-orphan; = mso-fareast-font-family: "Times New Roman" }
LI.MsoNormal {

FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"; = mso-style-parent: ""; mso-pagination: widow-orphan; = mso-fareast-font-family: "Times New Roman" }
DIV.MsoNormal {

FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"; = mso-style-parent: ""; mso-pagination: widow-orphan; = mso-fareast-font-family: "Times New Roman" }
A:link {

COLOR: blue; TEXT-DECORATION: underline; text-underline: single }
SPAN.MsoHyperlink {

COLOR: blue; TEXT-DECORATION: underline; text-underline: single }
A:visited {

COLOR: purple; TEXT-DECORATION: underline; text-underline: single }
SPAN.MsoHyperlinkFollowed {

COLOR: purple; TEXT-DECORATION: underline; text-underline: single }
SPAN.EmailStijl17 {

COLOR: windowtext; FONT-FAMILY: Arial; mso-style-type: = personal-compose; mso-style-noshow: yes; mso-ansi-font-size: 10.0pt; = mso-bidi-font-size: 10.0pt; mso-ascii-font-family: Arial; = mso-hansi-font-family: Arial; mso-bidi-font-family: Arial }
SPAN.SpellE {

mso-style-name: ""; mso-spl-e: yes
}
SPAN.GramE {

mso-style-name: ""; mso-gram-e: yes
}
DIV.Section1 {

page: Section1
}

</STYLE>
<!--[if gte mso 10]>
<style>

/* Style Definitions */=20
table.MsoNormalTable

	{mso-style-name:Standaardtabel;
	mso-tstyle-rowband-size:0;
	mso-tstyle-colband-size:0;
	mso-style-noshow:yes;
	mso-style-parent:"";
	mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
	mso-para-margin:0cm;
	mso-para-margin-bottom:.0001pt;
	mso-pagination:widow-orphan;
	font-size:10.0pt;
	font-family:"Times New Roman";}
</style>

<![endif]--></HEAD>
<BODY lang=3DNL style=3D"tab-interval: 35.4pt" vLink=3Dpurple = link=3Dblue>
<DIV>This is scary,=20
I'm planning to upgrade 9.2.0.4 from 9.2.0.2.</DIV> <DIV> </DIV>
<DIV>I = don't know how=20
removing <SPAN=20 class=3DSpellE>pga_aggegrate_target will help reducing=20 memory!!</DIV> <DIV> </DIV> <DIV>Does the program=20
have any memory tables, etc?</DIV> <DIV> </DIV>
<DIV>Did you monitor=20
the PGA size from the Oracle side using v$sesstat?</DIV> <DIV> </DIV>
<DIV>A = sql by itself=20
can't consume this memory except there is a major bug some where, which = I=20
doubt!</DIV>
<DIV> </DIV>
<DIV>Please keep us=20
updated.</DIV>
<DIV> </DIV>
<DIV>Thanks</DIV>
<DIV> </DIV>
<DIV>Waleed</DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
<DIV class=3DOutlookMessageHeader dir=3Dltr align=3Dleft>-----Original Message----- From: Jeroen van = Sluisdam=20
 [mailto:jeroen.van.sluisdam_at_vrijuit.nl] Sent: Tuesday, = December 23,=20
 2003 10:24 AM To: Multiple recipients of list=20 ORACLE-L Subject: ora-4030 pga memory allocation running=20 wild </DIV>
<DIV class=3DSection1>
<SPAN=20
 style=3D"FONT-SIZE: 10pt; FONT-FAMILY: = Arial">Hi<FONT=20
 face=3DArial size=3D2><SPAN=20
 style=3D"FONT-SIZE: 10pt; FONT-FAMILY: = Arial">,<o:p></o:p>
<SPAN=20
 style=3D"FONT-SIZE: 10pt; FONT-FAMILY: = Arial"><o:p> </o:p>
I have an = ora-4030 problem=20
 related to pga = memory=20
 allocation, at least I have concluded <SPAN=20 class=3DSpellE>sofar<o:p></o:p>
This program is = batch=20
 written in pl/sql and after an hour or so = it=20
 crashes. PGA allocated is slowly=20 exceeding<o:p></o:p>
2Gb and when = <SPAN=20
 class=3DGramE>I  = monitor with=20
 top I see the process size rising uptill = 2 <SPAN=20
 class=3DSpellE>Gb somewhere.<o:p></o:p>
Last week we = migrated from=20
 on oracle 7 environment where this program ran smoothly for=20 years.<o:p></o:p>
At the same time = we <SPAN=20
 class=3DGramE>migrated the OS also and started with new = machines. The=20
ux kernel =
parameter<o:p></o:p>
<SPAN=20
 lang=3DEN-US=20
 style=3D"FONT-SIZE: 12pt; mso-ansi-language: = EN-US">for<SPAN=20
 lang=3DEN-US style=3D"mso-ansi-language: EN-US"> max data segment = size is=20
 2Gb.<o:p></o:p>
<o:p> </o:p>
I had an oracle = consultant=20
 here for migration and he advised to put <SPAN=20 class=3DSpellE>pga_aggegrate_target on 250M. Box=20 has<o:p></o:p>
<SPAN=20
 lang=3DEN-US=20
 style=3D"FONT-SIZE: 12pt; mso-ansi-language: = EN-US">4Gb<SPAN=20
 lang=3DEN-US style=3D"mso-ansi-language: EN-US">, <SPAN=20 class=3DSpellE>shared_pool_size is 250Mb, SGA=20
 is almost 800Mb<o:p></o:p>
<o:p> </o:p>
I issued a tar = and Oracle=20
 advised me to remove pga_aggegrate_target = from the=20
init_file, but because this is production =
I cannot=20
 restart that<o:p></o:p>
<SPAN=20
 lang=3DEN-US=20
 style=3D"FONT-SIZE: 12pt; mso-ansi-language: = EN-US">easily<SPAN=20 lang=3DEN-US style=3D"mso-ansi-language: EN-US"> (online changes are = allowed <SPAN=20
 class=3DSpellE>ony from min. value 10M) <o:p></o:p>
<SPAN=20
 lang=3DEN-US style=3D"FONT-SIZE: 12pt; mso-ansi-language: = EN-US">I<SPAN=20
 style=3D"mso-spacerun: yes">  =
also<SPAN=20 lang=3DEN-US style=3D"mso-ansi-language: EN-US"> tested this program = with event=20
 :<o:p></o:p>
<SPAN=20
 lang=3DEN-US=20
 style=3D"FONT-SIZE: 12pt; mso-ansi-language: = EN-US">alter<SPAN=20 lang=3DEN-US style=3D"mso-ansi-language: EN-US"> session set events = '4030 trace=20
 name errorstack level 3'; I found the so = called=20
 SQL-statement that might be causing this<o:p></o:p>
<SPAN=20
 lang=3DEN-US=20
 style=3D"FONT-SIZE: 12pt; mso-ansi-language: = EN-US">but<SPAN=20
 lang=3DEN-US style=3D"mso-ansi-language: EN-US"> explaining this plan = gave me=20
 an  even better plan = than on the=20
 oracle 7 environment Oracle support still has to get back to me with=20
<o:p></o:p>
<SPAN=20
 lang=3DEN-US=20
 style=3D"FONT-SIZE: 12pt; mso-ansi-language: = EN-US">latest<SPAN=20 lang=3DEN-US style=3D"mso-ansi-language: EN-US"> = things.<o:p></o:p>
<o:p> </o:p>
This program is = clearly=20
 running wild on memory. Based on the docs on <SPAN=20 class=3DSpellE>metalink I lowered the <SPAN=20 class=3DSpellE>pga_aggegrate_target to = 160M<o:p></o:p>
<SPAN=20
 lang=3DEN-US=20
 style=3D"FONT-SIZE: 12pt; mso-ansi-language: = EN-US">now<SPAN=20
 lang=3DEN-US style=3D"mso-ansi-language: EN-US"> and I'm testing this = right now.=20
 Is there any way to protect your system from memory consumption like = this=20
case. Are there any<o:p></o:p>
<SPAN=20
 lang=3DEN-US=20
 style=3D"FONT-SIZE: 12pt; mso-ansi-language: = EN-US">other<SPAN=20 lang=3DEN-US style=3D"mso-ansi-language: EN-US"> parameters to=20 consider?<o:p></o:p>
<o:p> </o:p><= /SPAN>
Details: oracle = 9.2.0.4=20
HPUX 11.11, 4Gb phys=20
 memory<o:p></o:p>
<o:p> </o:p>
Thanks in=20 advance,<o:p></o:p>
<o:p> </o:p>
Jeroen<o:p></o:p></DIV></BLOCKQUOTE></BODY></HT= ML>

------_=_NextPart_001_01C3C96A.5ABB46EF--

------=_NextPartTM-000-4f56fb61-d288-4496-b424-c66a899c74eb--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Dec 23 2003 - 09:39:32 CST