Hotsos Oracle Expert Tips: Where Did I Leave My Keys (Part 3)

This is a continuation of the Where Did I Leave My Keys memory related tips. You can view the first part here and the second part here.

This article will focus on the Oracle database memory configurations for Program Global Area (PGA).

The Program Global Area (PGA) is a memory region Oracle uses for server processes. Managing this memory structure is a little different than the System Global Area (SGA), discussed in tip #2 in this series.

As with the SGA, Oracle provides advisors for the PGA to help with evaluating the appropriate size. Determining the appropriate PGA memory size requires a little more review. There is an advisor section in the Automatic Workload Repository (AWR) report called PGA Aggr Target Histogram that contains information such as M-Pass Execs which give more detailed information if your PGA size is too small. The PGA Memory Advisory contains the estimated impacts of different pga_aggregate_target sizes.

One difference with the PGA memory is that a process can use PGA memory beyond the target size. As a result of this behavior, the PGA can sometimes use up much more overall server memory than originally anticipated by the pga_aggregate_target setting. On occasion this over allocation can grow to the point where the server encounters performance issues due to lack of physical memory on the server. You can run a query such as the following to get information about the total PGA currently being used:

select round(sum(pga_used_mem)/1024/1024) as PGA_MB from v$process;

In Oracle database version 12c there is a new parameter, pga_aggregate_limit, which will prevent the PGA memory from extending beyond the assigned limit. This was added to help control the issue with excessive PGA growth. If not set explicitly, Oracle will default this value to a system determined value. Review this setting to determine if manually setting the value would be more appropriate for your environment. Note that when a server process exceeds the pga_aggregate_limit, the sessions may be terminated.

When reviewing system memory, be sure to include the PGA memory in the scope of the review. Take the time to review the usage in order to make the best decisions with sizing this memory structure.