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]
