You think I'm talking about something different?
Sent from my Windows Phone
________________________________
From: Hameed, Amir<mailto:Amir.Hameed@xxxxxxxxx>
Sent: 08/06/2017 06:56
To: Dominic Brooks<mailto:dombrooks@xxxxxxxxxxx>;
'ORACLE-L'<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: RE: _PGA_MAX_SIZE limit not being honored
Thanks Dominic. However, my question was more along the lines of PGA limits not
being honored.
From: Dominic Brooks [mailto:dombrooks@xxxxxxxxxxx]
Sent: Thursday, June 08, 2017 1:37 AM
To: Hameed, Amir <Amir.Hameed@xxxxxxxxx>; 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
Subject: RE: _PGA_MAX_SIZE limit not being honored
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@xxxxxxxxx>
Sent: 08/06/2017 03:49
To: 'ORACLE-L'<mailto:oracle-l@xxxxxxxxxxxxx>
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:
1. Given that _PGA_MAX_SIZE is set to 2GB (2147483648), what is the
reason that a process’s PGA can grow above 2GB?
2. 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
