Recently, I ran into this subject here in ORACLE-L.
I also don't understand with the desired depth this subject, but seems to
me that UGA is not part of PGA anymore (despite several bogs articles
saying the opposite).
So, when you see a single process getting from OS more memory than big PGA
pool limit (and much more memory than single process limit, specified
in _pga_max_size) is because this process is not using this memory for PGA,
but for UGA.
And, unfortunately, seems exists no good V$ view (maybe a X$ one...), that
gives you a real overview of a process memory in detail.
Tanel Pöder has a good article (yes, the exact article sent by Dominic
Brooks) about using a special trace to this information detail.
*--*
*Att*
*Luis Santos*
2017-06-08 3:21 GMT-03:00 Dominic Brooks <dombrooks@xxxxxxxxxxx>:
I don't know anything about EBS but it does not seem impossible to me that
switching to a BULK option would move from row-by-row plsql to collections.
Anyway, look at v$process_memory etc to see which direction you should
look next.
http://blog.tanelpoder.com/2014/03/26/oracle-memory-
troubleshooting-part-4-drilling-down-into-pga-memory-
usage-with-vprocess_memory_detail/
Sent from my Windows Phone
------------------------------
From: Dominic Brooks <dombrooks@xxxxxxxxxxx>
Sent: 08/06/2017 07:13
To: Hameed, Amir <Amir.Hameed@xxxxxxxxx>; 'ORACLE-L'
<oracle-l@xxxxxxxxxxxxx>
Subject: RE: _PGA_MAX_SIZE limit not being honored
You think I'm talking about something different?
Sent from my Windows Phone
------------------------------
From: Hameed, Amir <Amir.Hameed@xxxxxxxxx>
Sent: 08/06/2017 06:56
To: Dominic Brooks <dombrooks@xxxxxxxxxxx>; 'ORACLE-L'
<oracle-l@xxxxxxxxxxxxx>
Subject: RE: _PGA_MAX_SIZE limit not being honored
Thanks Dominic. However, my question was more along the lines of PGA
limits not being honored.
*From:* Dominic Brooks [mailto:dombrooks@xxxxxxxxxxx]
*Sent:* Thursday, June 08, 2017 1:37 AM
*To:* Hameed, Amir <Amir.Hameed@xxxxxxxxx>; 'ORACLE-L' <
oracle-l@xxxxxxxxxxxxx>
*Subject:* RE: _PGA_MAX_SIZE limit not being honored
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@xxxxxxxxx>
*Sent: *08/06/2017 03:49
*To: *'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
*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
