CODEWITHSUNDEEP
oracle-databaseperformanceawr
oradbanj
oradbanj

Reputation: 563

Tuning PGA_AGGREGATE_TARGET in 11.2

I have attached output of AWR report from our server which has been experiencing performance issues.

Attached is PGA Advisory from AWR report.

Does it indicates that there is shortage of PGA?

enter image description here

Upvotes: 1

Views: 947

Answers (1)

Jon Heller
Jon Heller

Reputation: 36912

The PGA Memory Advisor report implies two things:

  1. Changing the value of PGA_AGGREGATE_TARGET will not improve performance.
  2. There is a memory problem worth investigating.

Improve Performance

According to the "Estimated Time" column, there is less than a 1% performance impact of changing the PGA_AGGREGATE_TARGET from the current value of 9600 MB. The total DB Time, or "Estd Time", will barely change if the memory is shrunk to 13% of the current value or increased to 800% of the current value.

Normally, if Oracle doesn't think the DB time will improve there's no point in changing something. But, in this case there is something strange going on.

Estimated PGA Overallocation Count

Something on your database is using up too much PGA memory.

The parameter PGA_AGGREGATE_TARGET is meant to control how much memory is available for operations like sorting and hashing. There are other uses of PGA that may force the system to exceed that target. The report column "Estd PGA Overalloc Count" implies that something on the database uses up over 28800 MB of memory. You might want to find that memory hog; eliminating it may free up memory that can indirectly improve performance in some other way.

Use the below two queries to look for current and historical PGA memory problems. My guess is that either the system has too many connections or the sessions are doing something foolish, like loading huge tables into memory with a BULK COLLECT. You'll want to find the offending sessions and then find out exactly what they're doing that's using up so much memory.

--PGA in current sessions:
select inst_id, sid, round(value/1024/1024) mb
from gv$sesstat
join v$statname on gv$sesstat.statistic# = v$statname.statistic#
where name = 'session pga memory'
order by value desc, sid;

--PGA allocated in historical sessions.
select round(pga_allocated/1024/1024/1024, 1) gb, gv$active_session_history.*
from gv$active_session_history
where pga_allocated is not null
order by pga_allocated desc;

Upvotes: 1

Related Questions