| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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.
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 Wed Dec 24 2003 - 06:14:25 CST
 |
 |