PROGRAM GLOBAL AREA (PGA)

Program Global Area (PGA):

The Program Global Area (PGA) is a private memory (non-shared) that contains the data and control information for a server process. Only a server process can access the PGA. The total PGA memory allocated by each server process attached to an Oracle instance is also referred to as the aggregated PGA allocated by the instance.

One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA. We can query the v$process to see the memory aloocated to each PGA

Select    PNAME,  USERNAME,

               decode(BACKGROUND, '1', 'Y', 'N') “BG”,

                round(PGA_ALLOC_MEM/1024,0) "MemAlloc KB",

               round(PGA_USED_MEM/1024,0)   "MemUsed KB",

                round(PGA_FREEABLE_MEM/1024, 0) "MemFree KB",

                round(PGA_MAX_MEM/1024, 0) "MemMax KB"

from  v$process;

Contents of the PGA:

The contents of the PGA memory varies, depending on the instance is running the shared server option. The PGA memory can be classified as follows.

·         Private SQL Area

·         Session Memory

·         SQL Work Areas

Private SQL Area:

A private SQL area contains data such as bind information and runtime memory structures. When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas. 

The location of a private SQL area depends on the type of connection established for a session such as “Shared” or “Dedicated”. For Dedicated sessions a part of private SQL area resides in the PGA and for the Shared sessions the private SQL area resides in the library cache of the shared pool in SGA.

Each session that issues a SQL statement has a private SQL area and each user that submits the same SQL statement has own private SQL area that uses a single shared SQL area. Many private SQL areas can be associated with the same shared SQL area.

The private SQL area is itself divided into two areas whose lifetimes are different:

·         Run time Area

·         Persistent Area

A run time area is created as part of the query (DML) execution and closed as soon as the statement execution ends. It holds the current state of query execution information such as rows retrieved so far during the full table scans.

A persistent area contains bind variable values that are supplied to a SQL statement at run time, it’s freed when the cursor is closed. The persistent area is a part of the SGA (Large pool if configured) when the shared server model is used.

A client process is responsible for managing private SQL area but its allocation and de-allocation depends on the application and is limited by the server init parameter OPEN_CURSORS. 

A cursor is a name or handle to a specific private SQL area, in a nutshell cursor is nothing but private SQL area.

We can see cursors opened currently in database using following sql:

Show parameter open_cursors

--

select SID, USER_NAME, CURSOR_TYPE, last_sql_active_time

from v$open_cursor;

Session Memory (UGA):

Session memory is the memory allocated to hold logon information and other session details also known as User Global Area (UGA). The PGA is specific to a single process. Therefore, the UGA is stored in the SGA when using shared server connections, enabling any shared server process access to it. When using a dedicated server connection, the UGA is stored in the PGA.

SQL Work Areas:

A work area is a private allocation of PGA memory used for memory-intensive operations such as sorting, joining and merging. The following areas are allocated accordingly to the memory intensive operations.

• sort_area_size: Total memory used to for sorting before swapping to disk
• sort_area_retained_size : Memory that is used to retained data after a sort
• hash_area_size: Memory used to build a hash tables or algorithms (table joins)
• bitmap_merge_area_size: Memory used to merge bitmaps retrieved from a range scan of the index.

 For example, a sort operator uses the sort area to sort a set of rows. A hash join operator uses a hash area to build a hash table from input, whereas a bitmap merge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes.

If the amount of input data for any of the above operation is does not fit into the respected SQL work areas, then oracle divides them into smaller pieces and move it to the disk’s temporary storage (TEMP tablespace) for processing. This causes the increase in response time and reduces the performance, thus the allocation of PGA is key for the query performance.

The processing of above memory intensive operations if performed in memory it’s said to be an optimal mode of operation and if it performed on the disk then it’s one-pass/multi-pass (based on no. of packets sent to the disk for processing). If there are any one-pass/multi-pass operations we might want to increase the PGA memeory based on the frequency of the execution.

We can query the SQL work area views to see where the memory intensive operations are performed.

select   round(LAST_MEMORY_USED/1024/1024, 0) MEMUSEd_MB,

             TOTAL_EXECUTIONS, OPTIMAL_EXECUTIONS, ONEPASS_EXECUTIONS,

             MULTIPASSES_EXECUTIONS, ACTIVE_TIME/100/60 "Active(mins)",

              round(LAST_TEMPSEG_SIZE/1024/1024, 0) disk_MB,

              round(MAX_TEMPSEG_SIZE/1024/1024, 0) totaldisk_MB

from     v$sql_WORKAREA

where  sql_id='&sqlid';        

SELECT  s.sql_id, sum(ONEPASS_EXECUTIONS) onepass_cnt,

                  sum(MULTIPASSES_EXECUTIONS) mpass_cnt,

                  round(sum(LAST_TEMPSEG_SIZE)/1024/1024, 0)disk_MB,

                  round(sum(MAX_TEMPSEG_SIZE)/1024/1024, 0) totaldisk_MB

   FROM   V$SQL s, V$SQL_WORKAREA wa

WHERE   s.address = wa.address

GROUP BY s.sql_id

HAVING sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0;

Oracle automatically manages all the work areas/PGA on the whole by the setting of init parameter “pga_aggregate_target”.