Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: ora-4030 pga memory allocation running wild/pga_aggregate_tar

RE: ora-4030 pga memory allocation running wild/pga_aggregate_tar

From: Jeroen van Sluisdam <jeroen.van.sluisdam_at_vrijuit.nl>
Date: Tue, 30 Dec 2003 06:14:25 -0800
Message-ID: <F001.005DB43A.20031230061425@fatcity.com>




















Hi,

 

I have a workaround for my problem now working with:

- workarea_size_policy=manual and

Pga_aggregate_target=0

 

Oracle is advising to install all latest patches, We just bought this box and we installed all patches up till a few weeks

ago.  My production environment is connected to a San and we have a critical bussinessperiod going on now such that

I cannot take the risk to install patches now.

 

Bottom line is now that everything is back manual pga management due to 1 batch program. In the current workaround

the program runs normally and the system also. I definitely think this is a bug. Any advice on to continue on manual pga

management or try other workarounds will be appreciated?

 

 I have seen other discussion about pga_aggegrate_target going on the list but limiting the program by for example a

profile with private_sga is no option for me because the program must run regularly and was running smoothly

on oracle 7.

 

Regards,

 

Jeroen

 

-----Oorspronkelijk bericht-----
Van:
Jeroen van Sluisdam
Verzonden
: Wednesday, December 24, 2003 13:12
Aan:
'ORACLE-L@fatcity.com'
Onderwerp: RE: ora-4030 pga memory allocation running wild

 

Problem seems to be that both  workarea_size_policy=manual and

Pga_aggregate_target=0 must be in place in order to have effect.

I couldn't change pga_aggregate_target online, so I moved to a testbox

Where I could rebout and then the program was successful.

 

This kind of worries me that I have to change the database behaviour for just one program. I studies the docs a bit more and now it's on manual I have to reconsider all kind of *area_size again. Anyone seen this behaviour

Before and are there any special considerations to make  now, for example

Sort_area_size smaller?

 

startvalues v$pgastat with workarea_size_policy manual and pga_aggregate_target=0

 

NAME                                                                  VALUE UNIT

---------------------------------------------------------------- ---------- ------------

aggregate PGA target parameter                                            0 bytes

aggregate PGA auto target                                                 0 bytes

global memory bound                                                       0 bytes

total PGA inuse                                                    48437248 bytes

total PGA allocated                                              1201760256 bytes

maximum PGA allocated                                            1206069248 bytes

total freeable PGA memory                                                 0 bytes

PGA memory freed back to OS                                               0 bytes

total PGA used for auto workareas                                         0 bytes

maximum PGA used for auto workareas                                       0 bytes

total PGA used for manual workareas                                       0 bytes

 

NAME                                                                  VALUE UNIT

---------------------------------------------------------------- ---------- ------------

maximum PGA used for manual workareas                                     0 bytes

over allocation count                                                     0

bytes processed                                                           0 bytes

extra bytes read/written                                                  0 bytes

cache hit percentage                                                      0 percent

 

NAME                                                                  VALUE UNIT

---------------------------------------------------------------- ---------- ------------

aggregate PGA target parameter                                            0 bytes

aggregate PGA auto target                                                 0 bytes

global memory bound                                                       0 bytes

total PGA inuse                                                  1105702912 bytes

total PGA allocated                                              1194745856 bytes

maximum PGA allocated                                            1206069248 bytes

total freeable PGA memory                                                 0 bytes

PGA memory freed back to OS                                               0 bytes

total PGA used for auto workareas                                         0 bytes

maximum PGA used for auto workareas                                       0 bytes

total PGA used for manual workareas                                       0 bytes

 

NAME                                                                  VALUE UNIT

---------------------------------------------------------------- ---------- ------------

maximum PGA used for manual workareas                                     0 bytes

over allocation count                                                     0

bytes processed                                                           0 bytes

extra bytes read/written                                                  0 bytes

cache hit percentage                                                      0 percent

 

16 rows selected.

 

12:47:56 SQL> /

16 rows selected.

 

As you can see in total pga inuse increasing this program consumes about 1Gb. I have the source available and this program is definitely running without global variables. It's a package with functions and procedures

declaring cursors locally inside the functions or procedures. Could this amount of pga consuming be due to the program or might oracle have some problems here (also because of the error below instantiation space leak)?

 

Is there any way to run these kind of programs in a protected way that they may even go to disk if they need more memory than available?

 

Regards,

 

Jeroen

-----Oorspronkelijk bericht-----


Van: Jeroen van Sluisdam
Verzonden: Tuesday, December 23, 2003 23:19
Aan: 'ORACLE-L@fatcity.com'
Onderwerp: RE: ora-4030 pga memory allocation running wild

 

Hi,

 

I'm using oracle 9.2.0.4. I put it off tonight with the statement

You mentioned and unfortunately no success.

 

Maybe interesting to know that I started without the event 4030 set

And I get the following ora-600 in my alert file:

Tue Dec 23 16:46:42 2003

Errors in file /var/opt/oracle/product/admin/VU_2/udump/vu_2_ora_15251.trc:

ORA-00600: internal error code, arguments: [17271], [instantiation space leak], [], [], [], [], [],

This one is reproducible without the event set and a pga_aggregate_target set either 250Mb or 160Mb

 

With the event set I got the following error

Errors in file /var/opt/oracle/product/admin/VU_2/udump/vu_2_ora_10264.trc:

ORA-04030: out of process memory when trying to allocate 2464 bytes (cursor work he,rworalo : rwordops)

Tue Dec 23 14:24:40 2003

Errors in file /var/opt/oracle/product/admin/VU_2/udump/vu_2_ora_10249.trc:

ORA-00600: internal error code, arguments: [17271], [instantiation space leak], [], [], [], [], [], []

ORA-04030: out of process memory when trying to allocate 32 bytes (callheap,allocator state)

This second tracefile lead me to the sql-statement which explained with a very nice result

 

When I issued the statement to set off auto handling I did not get any such error in my alert file but my batch returned again after an hour

With

 

ERROR at line 1:

ORA-04030: out of process memory when trying to allocate 56 bytes (callheap,PESBLT space)

 

Could a UX kernel parameter be of any influence here, like max data segment?

Could it help to increase this to say 3Gb. Note that we have 4Gb physical memory and 4Gb swap configured.

 

I used to run this in an oracle 7 enviroment on hpux 10.20 and now we moved

To 64bit hpux11.11. I can imagine oracle is using more memory here than compared to oracle 7 with the same program such that in the old environment we might stayed below 2Gb and now we are exceeding this.

 

For what it might be worth, this batch is quite big. Sofar this seems to be the only program having memory problems. I have put back workare_size_policy=auto back to be on the safe default side.

 

I hope you can give some more leads because this is quite confusing

And causing me headaches because it is causing troubles in my production environment. By the way we tested the migration ofcourse but this batch was not included in the test.

 

Regards,

 

Jeroen

-----Oorspronkelijk bericht-----

Van: Jared Still [mailto:jkstill@cybcon.com]

Verzonden: dinsdag 23 december 2003 18:34

Aan: Multiple recipients of list ORACLE-L

Onderwerp: Re: ora-4030 pga memory allocation running wild

 

I'm using auto pga allocation on 9.2.0.3 without any problem.

 

You don't mention which version.

 

You can turn it off with 'alter system set workarea_size_policy=manual;

 

Jared

 

On Tue, 2003-12-23 at 07:24, Jeroen van Sluisdam wrote:

> 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

 

 

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net

--

Author: Jared Still

  INET: jkstill@cybcon.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@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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jeroen van Sluisdam
  INET: jeroen.van.sluisdam_at_vrijuit.nl

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 30 2003 - 08:14:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US