| 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/pga_aggregate_tar
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:
Verzonden: Wednesday, December 24, 2003 13:12
Aan:
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.
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
 |
 |