Re: _PGA_MAX_SIZE limit not being honored

Hello Amir,
seems like you have a lot of issues lately ;-)

1. _PGA_MAX_SIZE limits the maximum amount of memory for all work areas per process. However there are more than enough PGA memory allocation reasons that are not related to work areas.
2. There are a lot of little quirks about the pga_aggregate_limit implementation (e.g. unpublished bug #20992304). I tested / researched several PGA limit implementations and if you want to force a global limit on some specific value you have to use the resource manager with 12.2 (check out page 10 here: http://www.soocs.de/public/talk/161115_DOAG2016_Hacking_Oracles_Memory_About_Internals_Troubleshooting_PPT.pdf)

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK
Upcoming online seminar: http://tinyurl.com/17-06-13-Shared-Pool-Internals  

> "Hameed, Amir" <Amir.Hameed_at_xerox.com> hat am 8. Juni 2017 um 04:47 geschrieben:
>
> 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
 

--
http://www.freelists.org/webpage/oracle-l