PGA and SGA sizing
Table of contents
Preamble
- PGA (Program Global Area)
- SGA (System Global Area)
- UGA (User Global Area)
In oracle 9i and 10g when using ASMM you set SGA_TARGET (and so SGA_MAX_SIZE) and PGA_AGGREGATE_TARGET (sort, hash, …). Starting with 11g you now only set MEMORY_TARGET (and so MEMORY_MAX_TARGET). Oracle has extended the V$SGA_DYNAMIC_COMPONENTS view to V$MEMORY_DYNAMIC_COMPONENTS (same for other SGA typical views).
How to allocate the physical memory available on your server:
| Processing Mode | SGA | PGA | UNIX (kernel) |
|---|---|---|---|
| OLTP | 65% | 15% | 20% |
| DSS | 30% | 50% | 20% |
In dedicated server connection mode UGA is allocated in the PGA, in shared server connection mode UGA is allocated in the SGA (in the large pool, LARGE_POOL_SIZE).
First thing to note is that the PGA_AGGREGATE_TARGET parameter is a upper non-hard limit of your PGA i.e. at one point in time your database server may allocate more than this parameter. Contrary to SGA_MAX_SIZE with ipcs, PGA memory is not pre-allocated on your server and you will not see it in use when your database is not doing anything..
PGA used by processes
How to know which processes are using PGA, can be achieve using two tables, second one is also providing information if operation has been spilled on disk:
SELECT
s.sid,
p.spid,
DECODE(s.program, NULL, p.program, s.program) AS "Program",
pga_used_mem,
pga_alloc_mem,
pga_max_mem
FROM v$process p, v$session s
WHERE s.paddr = p.addr
ORDER BY s.sid;
SID SPID Program PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
---------- ------------ ------------------------------------------------ ------------ ------------- -----------
2 18352 JDBC Thin Client 1086341 2104093 3218205
4 28963 udi@server1 (TNS V1-V3) 2729773 3825013 4611445
5 5090 udi@server1 (TNS V1-V3) 2729773 3825013 4611445
7 9039 JDBC Thin Client 818461 1776413 3218205
8 24115 busobj.exe 558437 1776413 3218205
9 5094 oracle@server1 (DM00) 2845085 6101789 6298397
10 5074 oracle@server2 (TNS V1-V3) 541173 1841949 3218205
12 4285 JDBC Thin Client 672981 1345141 3218205
13 4277 JDBC Thin Client 672981 1345141 3218205
14 21665 busobj.exe 493005 1776413 10296093
16 28995 oracle@server1 (DM02) 2673397 5970717 6167325
23 12350 oracle@server3 (TNS V1-V3) 548437 1841949 3218205
25 10637 emagent@server1 (TNS V1-V3) 1785205 3180149 30770805
26 21283 oracle@server3 (TNS V1-V3) 502277 1776413 3480349
27 1604 toad.exe 623869 2038557 3283741
28 1703 sqlplus@server1 (TNS V1-V3) 509917 1793397 3235189
30 14432 sqlplus@server1 (TNS V1-V3) 1498133 3638901 3956085
31 18122 SQL Developer 1085269 2197109 7964277
35 3557 oracle@server1 1213861 1214069 3218205
37 19061 JDBC Thin Client 672981 1607285 3218205
43 14640 oracle@server3 (TNS V1-V3) 515909 1776413 3218205
44 5142 sqlplus@server1 (TNS V1-V3) 509917 1793397 3235189
46 14129 emagent@server1 (TNS V1-V3) 1418125 2852469 5539445
49 8581 sqlplus@server1 (TNS V1-V3) 2126029 2131573 3235189
50 14646 oracle@server3 (TNS V1-V3) 515909 1776413 3218205
56 13575 oracle@server1 (q001) 425173 662301 662301
62 14644 oracle@server3 (TNS V1-V3) 515909 1776413 3218205
65 21268 oracle@server4 (TNS V1-V3) 725373 1672821 3218205
69 14642 oracle@server3 (TNS V1-V3) 515909 1776413 3218205
73 24600 sqlplus@server1 (TNS V1-V3) 742797 1607285 3235189
74 25998 sqlplus@server1 (TNS V1-V3) 509917 1793397 3235189
75 1380 busobj.exe 493005 1776413 3218205
76 14638 oracle@server3 (TNS V1-V3) 515909 1776413 3218205
77 6155 JDBC Thin Client 818461 1776413 3218205
78 28748 emagent@server1 (TNS V1-V3) 1649733 2655861 16746101
79 4964 JDBC Thin Client 818461 1776413 3218205
80 5097 oracle@server1 (DW01) 67317837 104995613 104995613
81 3802 oracle@server1 (q000) 770829 1824893 1824893
89 28702 emagent@server1 (TNS V1-V3) 1768805 2983541 29956893
91 3500 oracle@server1 (QMNC) 327757 531229 531229
93 8614 TOAD.exe 886949 2235165 3218205
95 3367 oracle@server1 (ARC1) 10872781 22747165 22747165
96 3365 oracle@server1 (ARC0) 10830437 22747165 22747165
97 29031 oracle@server1 (DW03) 57068173 87366429 87366429
100 3332 oracle@server1 (MMNL) 319925 465693 465693
101 3330 oracle@server1 (MMON) 1388013 2152501 3004469
102 3328 oracle@server1 (CJQ0) 702557 5905181 7543581
103 3323 oracle@server1 (RECO) 586285 1121053 1121053
104 3321 oracle@server1 (SMON) 681613 2218109 2939005
105 3319 oracle@server1 (CKPT) 598301 1234853 1234853
106 3317 oracle@server1 (LGWR) 10878205 22861157 22878213
107 3315 oracle@server1 (DBW3) 1741389 57333493 143709941
108 3313 oracle@server1 (DBW2) 2216573 59889397 183031541
109 3311 oracle@server1 (DBW1) 1742469 35510005 144758517
110 3307 oracle@server1 (DBW0) 2219669 68676629 169536533
111 3301 oracle@server1 (MMAN) 317621 465693 465693
112 3299 oracle@server1 (PSP0) 314421 465693 465693
113 3297 oracle@server1 (PMON) 315189 596765 596765
182 7799 oracle@server3 (TNS V1-V3) 323141 465693 465693
190 26443 busobj.exe 1600109 2655861 100801309
209 13579 oracle@server3 (TNS V1-V3) 548437 1841949 3218205
61 rows selected.
Remark:
V$PROCESS_MEMORY can provide the breakdown per area for all processes.
SELECT
sid,
operation_type,
work_area_size,
expected_size,
actual_mem_used,
max_mem_used,
number_passes,
DECODE(NVL(tempseg_size,0),0,'Memory','Disk') AS operation_location
FROM v$sql_workarea_active
ORDER BY 1,2;
SID OPERATION_TYPE WORK_AREA_SIZE EXPECTED_SIZE ACTUAL_MEM_USED MAX_MEM_USED NUMBER_PASSES OPERAT
---------- -------------------- -------------- ------------- --------------- ------------ ------------- ------
10 HASH-JOIN 2330624 2811904 1242112 1762304 0 Memory
10 IDX MAINTENANCE (SOR 133120 133120 112640 112640 0 Memory
18 SORT (v2) 7033856 7033856 6046720 6046720 0 Memory
36 BUFFER 28496896 28496896 28496896 28496896 0 Memory
36 HASH-JOIN 245343232 245343232 231864320 231864320 0 Disk
36 HASH-JOIN 177768448 177768448 242925568 445222912 1 Disk
39 BUFFER 26774528 26774528 22432768 22432768 0 Memory
39 HASH-JOIN 149932032 149932032 4218880 4218880 0 Memory
39 HASH-JOIN 10195968 10194944 8990720 8990720 0 Memory
55 GROUP BY (SORT) 1027072 1027072 950272 950272 0 Memory
55 HASH-JOIN 71752704 71751680 70891520 70891520 0 Memory
55 HASH-JOIN 25001984 25000960 24011776 24011776 0 Memory
80 BUFFER 692224 692224 692224 692224 0 Memory
80 HASH-JOIN 4621312 4620288 3908608 3908608 0 Memory
14 rows selected.
How to increase PGA per process
When monitoring your PGA you may see that not all PGA is used while you have processing that are spilling on disk sort operations (pga_aggregate_target = 4G in this chapter):
SELECT
sum(pga_used_mem),
sum(pga_alloc_mem),
sum(pga_max_mem)
FROM v$process p;
SUM(PGA_USED_MEM) SUM(PGA_ALLOC_MEM) SUM(PGA_MAX_MEM)
----------------- ------------------ ----------------
197659894 494454494 1157399998
How processes can used PGA is bounded by following hidden parameters:
- _pga_max_size: Maximum usable PGA memory per process (in bytes). On contrary of 2 below parameter this is the sum of all active work area of a single process. See exmaple above, one session car perform two hash-joins at the same time…
- _smm_max_size: Maximum individual work area size in auto mode (serial), expressed in KBytes.
- _smm_px_max_size: Maximum individual work area size in auto mode (global), expressed in KBytes. Maximum work area of a all PX process. So each process must have a work area of _smm_px_max_size / DOP (DOP = Degree Of Parallelism).
Remark:
For parallel execution, slaves must so verify two conditions for their work areas, that should be below _smm_max_size and below _smm_px_max_size / DOP.
To display them:
SELECT
a.ksppinm AS parameter,
c.ksppstvl AS value,
a.ksppdesc AS description,
b.ksppstdf AS "Default?"
FROM
x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm IN ('pga_aggregate_target','_pga_max_size','_smm_max_size','_smm_px_max_size')
ORDER BY a.ksppinm;
PARAMETER VALUE DESCRIPTION Default?
-------------------- -------------------- ----------------------------------------------------------------- ---------
_pga_max_size 858992640 Maximum size of the PGA memory for one process TRUE
_smm_max_size 419430 maximum work area size in auto mode (serial) TRUE
_smm_px_max_size 2097152 maximum work area size in auto mode (global) TRUE
pga_aggregate_target 4294967296 Target size for the aggregate PGA memory consumed by the instance FALSE
_pga_max_size default value (was 200MB in 9i):
- pga_aggregate_target <= 1GB, _pga_max_size is 200 MB.
- pga_aggregate_target > 1GB, _pga_max_size is 20% of pga_aggregate_target.
_smm_max_size default value (was min(5% pga_aggregate_target, 50% _pga_max_size) in 9i):
- pga_aggregate_target <=500MB, _smm_max_size = 20% of pga_aggregate_target.
- pga_aggregate_target between 500MB and 1GB, _smm_max_size = 100MB.
- pga_aggregate_target > 1GB, _smm_max_size = 10%* pga_aggregate_target.
_smm_px_max_size default value is 50% of pga_aggregate_target (was 30% in 9i):
On a DSS system with large PGA allocated (multiple tens of GBytes) it could be interesting to increase those parameters if you have too many operations on disk and plenty of PGA available. But, as usual, be very caution when modifying them, ideally this should be on the advice of Oracle support.
References
- How To Super-Size Work Area Memory Size Used By Sessions? [ID 453540.1]
- PGA_AGGREGATE_TARGET Assigned Memory Is Left Unconsumed When Set High [ID 844542.1]

