PGA Tuning

Here is an attempt to summarize how the PGA_AGGREGATE_TARGET defines the upper limits that Oracle should use for the PGA areas of the processes used to run the database.

The value of PGA_AGGREGATE_TARGET determines several internal parameters, among them are _pga_max_size, _smm_max_size and _smm_px_max_size.
These internal parameters control the maximum amount of memory that can by used a single process (_pga_max_size),
a serial operation resp. "workarea" (_smm_max_size) and the maximum memory available for the operation of a parallel slave in a parallel operation (_smm_px_max_size).

In 10.2 the upper limits are driven by the _smm_max_size which is derived from PGA_AGGREGATE_TARGET and can be larger than 100M if you have a
PGA_AGGREGATE_TARGET greater than 1GB (I think _smm_max_size = 10% of PGA_AGGREGATE_TARGET if this is > 1GB). The _pga_max_size is then two times _smm_max_size.


column name format a40
column value format 999,999,999

select name, value
from
v$pgastat;

NAME VALUE
----------------------------------------------------------------------------------------------
aggregate PGA target parameter 2147483648
aggregate PGA auto target 1849245696
global memory bound 214743040
total PGA inuse 92922880
total PGA allocated 210248704
maximum PGA allocated 1142217728
total freeable PGA memory 56295424
process count 46
max processes count 58
PGA memory freed back to OS 5.1425E+11
total PGA used for auto workareas 0
maximum PGA used for auto workareas 325000192
total PGA used for manual workareas 0
maximum PGA used for manual workareas 1075200
over allocation count 0
bytes processed 1.0435E+12
extra bytes read/written 8.7535E+10
cache hit percentage 92.26
recompute count (total) 1359673


total PGA allocated

Current amount of PGA memory allocated by the instance. The Oracle Database attempts to keep this number below the value of the PGA_AGGREGATE_TARGET initialization parameter.
However, it is possible for the PGA allocated to exceed that value by a small percentage and for a short period of time when the work area workload is increasing very rapidly or
when PGA_AGGREGATE_TARGET is set to a small value.

aggregate PGA target parameter

Current value of the PGA_AGGREGATE_TARGET initialization parameter. If this parameter is not set, then its value is 0 and automatic management of PGA memory is disabled.

total PGA used

Indicates how much PGA memory is currently consumed by work areas. This number can be used to determine how much memory is consumed by
other consumers of the PGA memory (for example, PL/SQL or Java).


col c1 heading 'Workarea|Profile' format a35
col c2 heading 'Count' format 999,999,999
col c3 heading 'Percentage' format 9999
select name c1,count c2,decode(total, 0, 0, round(count*100/total)) c3
from
(
select name,value count,(sum(value) over ()) total
from
v$sysstat
where
name like 'workarea exec%'
);

Workarea
Profile Count Percentage
--------------------------------------------------------------------------------
workarea executions - optimal 5,378,033 100
workarea executions - onepass 845 0
workarea executions - multipass 54 0


--- if workarea executions - optimal > 100%, we need to reduce the value of PGA_Aggregate_Target

-- if workarea executions - multipass > 0 , we need to increase the value of PGA_Aggregate_Target


Note:- OLTP requires more DB_Cache_Size and BATCH jobs require more PGA size
PGA_Aggregate_Target is instance specific but sort_area_size is session specific

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.


PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO.
This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create)
will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA
or 10 MB, whichever is greater.


SELECT
s.value,s.sid,a.username
FROM V$SESSTAT S, V$STATNAME N, V$SESSION A
WHERE N.STATISTIC# = S.STATISTIC# and
N.name = 'session pga memory'
AND s.sid=a.sid
ORDER BY s.value;

You can check session level PGA using V$SESSTAT and V$SESSION view and also you can check the username, who is using that memory.



WHICH SESSION IS USING HOW MUCH PGA

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid FORMAT 999 HEADING 'SID'
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a8 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'

SELECT
s.sid sid
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,8) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory') session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session pga memory max') session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory') session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid and
sn.statistic# = ss.statistic# and
sn.name = 'session uga memory max') session_uga_memory_max
FROM
v$session s
ORDER BY session_pga_memory DESC
/




col SID format 999999999
col name format a40
col memory format 999999
col USERname format a20
col COMMAND format a40
col OSUSER format a20
col OSUSER format a40

select
ssst.sid,
stn.name ,
round(ssst.value/1024/1024,2) memory ,
USERNAME,
COMMAND,
OSUSER,PROGRAM
from v$statname stn,v$sesstat ssst , v$session ses
where stn.STATISTIC# = ssst.STATISTIC# and
ssst.sid = ses.sid and
name like 'session%pga%memory%'
order by 3 asc ;



output
---------
SID NAME MEMORY USERNAME COMMAND OSUSER PROGRAM
---------- ---------------------------------------- ------- -------------------- ---------- ---------------------------------------- ------------------------------------------------
8 session pga memory 0 ########## oracle oracle@xyz (QMNC)
48 session pga memory max 0 ########## oracle oracle@xyz (MMAN)
33 session pga memory max 0 ########## oracle oracle@xyz (PSP0)
8 session pga memory max 0 ########## oracle oracle@xyz (QMNC)
49 session pga memory 0 ########## oracle oracle@xyz (PMON)
48 session pga memory 0 ########## oracle oracle@xyz (MMAN)
33 session pga memory 0 ########## oracle oracle@xyz (PSP0)
49 session pga memory max 0 ########## oracle oracle@xyz (PMON)
44 session pga memory max 1 ########## oracle oracle@xyz (q002)
44 session pga memory 1 ########## oracle oracle@xyz (q002)
14 session pga memory 1 ########## oracle oracle@xyz (MMNL)
14 session pga memory max 1 ########## oracle oracle@xyz (MMNL)
29 session pga memory 1 SYS ########## oracle sqlplus@xyz (TNS