PGA Overview And Tuning PGA Memory in Oracle Database

What is PGA 

A Program Global Area (PGA) is a memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process.

When you use a dedicated server type of Oracle database, the Program Global Area (PGA) is a private memory region containing data and control information for dedicated server process,

For example, runtime area of cursors. Each time a cursor is executed, a new runtime area is created for that cursor in the PGA memory region.

For complex queries (for example, some SR/OFFERING-related queries are always complex and have many JOINs among base tables), a big portion of the runtime area is required because these queries always contain several kinds of memory-intensive operators, such as the following:

• Sort-based operators, such as ORDER BY, GROUP BY, ROLLUP, and window functions
• Hash-join
• Bitmap related operations
• Write buffers used by bulk load operations

 

Configuration

Automatic PGA Memory Management

With automatic PGA memory management, you set a target size only for the total instance PGA PGA_AGGREGATE_TARGET). The database then tunes the size of the instance PGA to your target, and dynamically tunes the sizes of individual PGAs. 

Manual PGA Memory Management

Previous releases of Oracle Database required the DBA to manually specify the maximum work area size for each type of SQL operator (such as sort or hash-join). This proved to be very difficult and inefficient. Although the current release of Oracle Database supports this manual PGA memory management method, we seldom use this method (for example, we use this method to force the database to use a small workarea to simulate one client's database behavior).

When running under the automatic PGA memory management mode, sizing of work areas for all sessions is controlled by Oracle automatically. The *_AREA_SIZE parameters are ignored. At any given time, the total amount of PGA memory available to active work areas in the instance is automatically derived from the PGA_AGGREGATE_TARGET initialization parameter. This amount is set to the value of PGA_AGGREGATE_TARGET minus the amount of PGA memory allocated by other components of the system (for example, PGA memory allocated by sessions). The resulting PGA memory is then assigned to individual active work areas, based on their specific memory requirements.

NOTE: Manual management of PGA memory is not recommended.

 

Setting PGA_AGGREGATE_TARGET Initially

 

The value of the PGA_AGGREGATE_TARGET initialization parameter  should be set based on the total amount of memory available for the Oracle database instance. This value can then be tuned and dynamically modified at the instance level.

illustrates a typical situation.

Let total physical memory available on the system = 4 GB

Memory  left for the OS and other non-Oracle applications running on the same

system = 20% i.e. .8 GB

Memory available for Oracle instance = 80% i.e. 3.2 GB

You must then divide the resulting memory between the SGA and the PGA.

For OLTP systems, the PGA memory = 20% i.e. 3.2 * .2 = 0.64 GB leaving 80% for the SGA.

For DSS systems running large, memory-intensive queries, PGA memory can
typically use up to 70% of that total i.e. 3.2 * 0.7 = 2.2 GB ).

Good initial values for the parameter PGA_AGGREGATE_TARGET might be:

For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%

For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 70%

where total_mem is the total amount of physical memory available on the system.

In this example, with a value of total_mem equal to 4 GB, you can
initially set PGA_AGGREGATE_TARGET to 1600 MB for a DSS system and to
655 MB for an OLTP system.

 

Monitoring and tuning

 

We can use several dynamic performance views to monitor the PGA status. By leveraging the information collected by Oracle underway, we can assess the performance of the Program Global Area:

• V$PGA_TARGET_ADVICE - This view provides information about how the statistics cache-hit percentage and over-allocation count are affected if we change the value of PGA_AGGREGATE_TARGET.

• V$PGA_TARGET_ADVICE_HISTOGRAM - This view provides estimated information of a group of the performance statistics if we change the value of PGA_AGGREGATE_TARGET.

• V$PGASTAT - This view provides information about instance-level statistics on the PGA memory usage and the automatic PGA memory manager.

• V$PROCESS - This view provides information for each process connected to the instance.

• V$PROCESS_MEMORY - This view provides dynamic PGA memory usage by named component categories for each process.

• V$SQL_WORKAREA_HISTOGRAM - This view shows the number of work areas executed with optimal memory size, one-pass memory size, and multi-pass memory size.

• V$SQL_WORKAREA_ACTIVE - This view can be used to display the work areas that are active (or executing) and to determine whether these active work areas spill to a temporary segment.

• V$SQL_WORKAREA - This view can be used to cumulative work area statistics for each loaded cursor whose execution plan uses one or more work areas.

AWR report also provides straightforward information in the PGA Memory Advisory section.

You can also find a list of non-optimal operations information in the histogram section.

To Check size of PGA

 show parameter pga
 show parameter memory

To know total memory used by database

select (sga+pga)/1024/1024 as "sga_pga"
from
(select sum(value) sga from v$sga),
(select sum(pga_used_mem) pga from v$process);

or

select (sga+pga)/1024/1024 as "sga_pga"
from
(select sum(value) sga from v$sga),
(select sum(PGA_ALLOC_MEM) pga from v$process);

SELECT * FROM v$process_memory_detail ORDER BY pid, bytes DESC;

show parameter PGA%TARGET

 

/* PGA */

select sum(PGA_ALLOC_MEM)/1024/1024 "Total PGA Allocated (Mb)" from v$process p, v$session s where p.addr = s.paddr;

 

select PGA_TARGET_FOR_ESTIMATE/1024/1024,BYTES_PROCESSED/1024/1024,ESTD_EXTRA_BYTES_RW/1024/1024 from  V$PGA_TARGET_ADVICE;

SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,

       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,

       ESTD_OVERALLOC_COUNT

FROM   v$pga_target_advice;

 

column name format a40

column value format 999,999,999

select name, value from v$pgastat;

 

Following query can be used to check the processes which are allocating more PGA memory

set lines 500
set pages 1000
col program format a20
col module format a20
col machine format a40

select a.spid OS_PROCESS_ID,b.sid,b.serial#,b.username,b.schemaname,b.osuser,b.machine,b.status,b.server,
b.program,b.module,round(a.PGA_ALLOC_MEM/1024/1024,2) "Currecn PGA Allocated",round(a.PGA_USED_MEM/1024/1024,2)"Currect PGA memory in use" from v$process a,v$session b where  a.addr=b.PADDR
order by a.PGA_ALLOC_MEM;

PGA_USED_MEM in v$process means the memory which has been used by oracle, there is one more column in v$process i.e. PGA_ALLOC_MEM which means the actual memory which has been allocated to use, So try using PGA_ALLOC_MEM instead of PGA_USED_MEM in your query because you are caculating on the basis of physical memory and PGA_ALLOC_MEM is the memory which has not been released to OS.

Note
====
The idea with the PGA Memory Advisory is the following:

Determine if the PGA is already at the PGA_SIZE with the lowest Estd Extra W/A MB Read/ Written to Disk and/or Estd Time. If not, then to get the best performance from the PGA, the PGA_TARGET should be changed to the optimal PGA_SIZE.

Using the results from the AWR report, we see that the smallest Estd Extra W/A MB Read/ Written to Disk is 0, and the smallest Estd Time is 94,412,484. However, the biggest improvement appears to be at PGA_SIZE_FACTOR of 0.5, with any further increase in PGA_SIZE_FACTOR show no diminishing returns. So, using the results above, the PGA_TARGET at 800mb would give the best performance without allocating more memory than may be necessary.



A forecast of the memory consumption is quite difficult as it depends on multiple factors, such as how big the database is, what will be the load and how it will be distributed, if you will use Java objects, and so on.

As a general recommendation, we suggest you to:
1. Use ASMM (i.e. setting SGA_TARGET parameter) to a certain value and preferably set SGA_MAX_SIZE to a larger value, to allow you to increase the memory without database restart. In this way, granules of memory will be moved automatically between the memory components (shared_pool_size, db_cache_size) automatically when the database considers one component needs most of the memory.

2. After a while of database load(a few days), please query the view V$SGA_TARGET_ADVICE, which provides tuning advice for the SGA_TARGET initialization parameter, as described in https://docs.oracle.com/cd/E11882_01/server.112/e25494/memory.htm#ADMIN11202
Please follow the recommendation from the v$sga_target_advice and increase the memory, if recommended.

3. After you have setup a recommended value for the SGA_TARGET, please wait a few days until the database will experience the usual load, and set the SHARED_POOL_SIZE to the maxim TARGET_SIZE value found for the shared pool in either V$SGA_RESIZE_OPS or DBA_HIST_MEMORY_RESIZE_OPS (whichever of the 2 is the largest).
Also, it is recommended to set minimum values for:
- the DB_CACHE_SIZE (usually larger than the SHARED_POOL_SIZE)
- for LARGE_POOL_SIZE, if you use RMAN for backup the database or if you use parallel executions
- for STREAMS_POOL_SIZE, if you use AQ or if you export/import with Datapump regularly (usually 150MB or more)
- for JAVA_POOL_SIZE, if you use JAVA objects in the database

These values you setup will be minimum values for these components, but they can grow to accommodate with database load, when needed. However, their sum will always be equal to SGA_TARGET.

Check LGWR Traces

Even if the average wait for 'log file parallel write' may be in the normal range, there may be peaks where the write time is longer and will therefore influence waits on 'log file sync'. From 10.2.0.4, messages are written in the LGWR trace when a write to the log file takes more than 500 ms. This is quite a high threshold so a lack of messages does not necessarily mean there is no problem. The messages look similar to the following:

*** 2011-10-26 10:14:41.718
Warning: log write elapsed time 21130ms, size 1KB
(set event 10468 level 4 to disable this warning)


*** 2011-10-26 10:14:42.929
Warning: log write elapsed time 4916ms, size 1KB
(set event 10468 level 4 to disable this warning)