RE: _PGA_MAX_SIZE limit not being honored
Date: Thu, 8 Jun 2017 05:37:12 +0000
Message-ID: <DB6P190MB04540AF1C8EA9612EA372322A1C90_at_DB6P190MB0454.EURP190.PROD.OUTLOOK.COM>
For one generic example of what can do such a thing, see plsql and collections. If I were to populate a big enough collection, I could use up all the memory available on the server.
Cheers
Dominic
Sent from my Windows Phone
From: Hameed, Amir<mailto:Amir.Hameed_at_xerox.com> Sent: ý08/ý06/ý2017 03:49
To: 'ORACLE-L'<mailto:oracle-l_at_freelists.org> Subject: _PGA_MAX_SIZE limit not being honored
Hi,
We are working on troubleshooting a performance issue with a Contact Billing job of Oracle’s E-Business Suite (EBS). The Contact Billing module of EBS has a profile option that enables BULK operations for this particular job. Oracle Support’s recommendation is to set the BULK option profile to YES to enable BULK operations. However, what I have seen is that when we enable this option, the PGA utilization goes through the roof. Our PGA settings are shown below:
pga_aggregate_limit big integer 32G pga_aggregate_target big integer 16G
We do not allow more than two instances of the job run in parallel to speed up the processing time. While the job is running, the V$PROCESS shows that each process is consuming a PGA of up to 12GB! The V$PGASTAT also shows that the maximum PGA usage was a little over 32GB, I have even seen it go as high as 38G. When we turn the BULK option off, the PGA utilization of those processes does not exceed over 100M per process.
I have the following questions:
- Given that _PGA_MAX_SIZE is set to 2GB (2147483648), what is the reason that a process’s PGA can grow above 2GB?
- The information from V$PGASTAT shows that the maximum PGA allocated statistic was around 38GB for a few runs. This seems to show that PGA does not really honor the limit set by pga_aggregate_limit?
Thank you,
Amir
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 08 2017 - 07:37:12 CEST