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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
