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