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:
- 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.
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”.
