2

Is it possible for this wait event to activate/showup even when the PGA has not crossed over PGA_AGGREGATE_LIMIT?

I am continuously seeing this wait event in the ASH, but the PGA allocated stays well within the limit set by PGA_AGGREGATE_LIMIT.

I used the below queries to check.

Total PGA that is currently used:

select sum(pga_alloc_mem) from v$process; 

Parameter value for PGA_AGGREGATE_LIMIT:

select * from v$parameter where name like '%pga%limit';

For what it is worth, the documentation seems to be scarce for this wait event and the only official source doesn't help either: https://support.oracle.com/knowledge/Oracle%20Database%20Products/2138882_1.html

EDIT:

Adding bounty.

More info:

PGA_AGGREGATE_LIMIT is set to 8 GB
PGA_AGGREGATE_TARGET is set to 4 GB

I am getting this wait event with the following values in ASH

P1 (Limit) = 8.3 GB (approx)
P2 (Margin) = 3.9 GB (approx)
P3 (Growth) = 0 or some randomly large number

I am guessing P1 is the PGA_AGGREGATE_LIMIT and P2 is some sort of threshold used before the wait event fires, but I am not sure what these actually are.

4
  • Did you try the solutions provided on the referenced MOS document?
    – atokpas
    Oct 26 '17 at 4:03
  • @JSapkota: I could see the page but I don't have access to that MOS document. Could you please let me know in brief if having this wait event at the top is a problem (or normal) and if it is a problem, what are some suggested solutions? Thanks. Oct 26 '17 at 4:40
  • The parameter is introduced in 12c to hard limit the PGA memory, so if you want to have 11g behavior set it to 0 as ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SID='*' SCOPE=BOTH;.
    – atokpas
    Oct 26 '17 at 4:43
  • @JSapkota: But the PGA_AGGREGATE_LIMIT doesn't even get exceeded in my case and this event is shown when there is still plenty of free PGA space to reach the limit set by PGA_AGGREGATE_LIMIT. I had earlier found the same workaround in some site and used the same and still I am getting this event. I am not even sure if this is the new normal in 12c but I think it should not be, as it is a "Scheduler" class wait event and we need to be waiting on doing useful work rather than scheduling stuff. Oct 26 '17 at 4:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.