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: @OracleSK
Upcoming online seminar: http://tinyurl.com/17-06-13-Shared-Pool-Internals
"Hameed, Amir" <Amir.Hameed@xxxxxxxxx> 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
