PGA and SGA sizing

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 ModeSGAPGAUNIX (kernel)
OLTP65%15%20%
DSS30%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]

About Post Author

Share the knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>