Re: _PGA_MAX_SIZE limit not being honored

Hello Amir,

Take a look at this series of articles from Frits Hoogland. I ran into a similar scenario here and they help me a lot to understand:

https://fritshoogland.wordpress.com/2014/12/15/oracle-database-operating-system-memory-allocation-management-for-pga/
https://fritshoogland.wordpress.com/2014/12/16/oracle-database-operating-system-memory-allocation-management-for-pga-part-2-oracle-11-2/
https://fritshoogland.wordpress.com/2014/12/17/oracle-database-operating-system-memory-allocation-management-for-pga-part-3-oracle-11-2-0-4-and-amm-quiz/
https://fritshoogland.wordpress.com/2014/12/18/oracle-database-operating-system-memory-allocation-management-for-pga-part-4-oracle-11-2-0-4-and-amm/

*Kind regards / Cordialmente / Saludos cordiales / Sincères amitiés / Mit freundlichen Grüßen / Cordiali saluti,*

Franky Weber Faust
Oracle DBA
Skype: franky.faust

2017-06-08 2:37 GMT-03:00 Dominic Brooks <dombrooks_at_hotmail.com>:

> 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 <Amir.Hameed_at_xerox.com>
> Sent: ‎08/‎06/‎2017 03:49
> To: 'ORACLE-L' <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
> <(21)%204748-3648>), 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