Re: _PGA_MAX_SIZE limit not being honored

Only if there was no OS imposed limit on the resident memory size and address space size which would prevent you from doing so. With automatic PGA, one needs decent system administration. Automatic PGA management only shifts the burden to the OS administrator. Regards

On 06/08/2017 01:37 AM, Dominic Brooks wrote:
> 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:
>
> 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
>

-- 
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com


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