Home > Uncategorized > PGA Tuning

PGA Tuning

PGA Tuning

oracle recommends setting the PGA_AGGREGATE_TARGET to 16% of your physical memory for OLTP systems and 40% for DSS systems.

After setting the parameter and running a test load on your database you can use the following views to find out the optimal setting

and also V$SYSTAT and V$SESSTAT to find the work executions.

SQL> select name,value from v$sysstat where name like 'workarea executions%';


NAME                                                 VALUE
---------------------------------------------------- ----------
workarea executions - optimal                        2660699
workarea executions - onepass                        3877
workarea executions - multipass                      65

optimal executions performed entirely in memory

if the operation was to big to be performed in memory then the operation spills out on to disk

workarea executions – onepass     — One pass was needed on disk
workarea executions – multipass   — Multiple passes on disk

view v$pgastat gives you additonal statitics on how well the  dynamic working area memory management is performing

NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                   1073741824 bytes
aggregate PGA auto target                                         885187584 bytes
global memory bound                                               107366400 bytes
total PGA inuse                                                   547126272 bytes
total PGA allocated                                               739113984 bytes
maximum PGA allocated                                            2551278592 bytes
total freeable PGA memory                                         118030336 bytes
process count                                                           144
max processes count                                                     206
PGA memory freed back to OS                                      5.1879E+11 bytes
total PGA used for auto workareas                                 456928256 bytes

NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
maximum PGA used for auto workareas                              1361600512 bytes
total PGA used for manual workareas                                       0 bytes
maximum PGA used for manual workareas                             816847872 bytes
over allocation count                                                     0
bytes processed                                                  1.1318E+12 bytes
extra bytes read/written                                         4.4989E+11 bytes
cache hit percentage                                                  71.55 percent
recompute count (total)                                               57874


aggregate PGA target parameter — current value of pga_aggregate_target

over allocation count

 if Oracle determines that it cannot honor the setting for PGA_AGGREGATE_TARGET, 
then it needs to allocate additional memory. The number of times Oracle detects 
this condition since instance startup is noted by the over allocation count. ideally this should be zero 

cache hit percentage  

shows a hit ratio on the number of bytes where optimal executions were performed compared the total number 
of bytes for all executions, optimal, one-pass, and multi-pass. If all executions where optimal, 
then this statistic should be 100%.

SELECT ROUND (pga_target_for_estimate / 1024 / 1024) AS target_size_MB,
       estd_extra_bytes_rw AS est_rw_extra_bytes,
       estd_pga_cache_hit_percentage AS est_hit_pct,
       estd_overalloc_count AS est_overalloc
  FROM v$pga_target_advice;
  -------------- --------------- ------------------ ----------- -------------
             128      1.1600E+12         2.0099E+12          37          3952
             256      1.1600E+12         1.5567E+12          43          1864
             512      1.1600E+12         9.1824E+11          56           275
             768      1.1600E+12         4.6237E+11          72            40
            1024      1.1600E+12         3.0320E+11          79            22
            1229      1.1600E+12         2.3139E+11          83            21
            1434      1.1600E+12         2.2868E+11          84            20
            1638      1.1600E+12         2.2269E+11          84            19
            1843      1.1600E+12         2.2207E+11          84            19
            2048      1.1600E+12         2.1717E+11          84            14
            3072      1.1600E+12         2.0501E+11          85             0
  -------------- --------------- ------------------ ----------- -------------
            4096      1.1600E+12         1.9690E+11          85             0
            6144      1.1600E+12         1.9690E+11          85             0
            8192      1.1600E+12         1.9690E+11          85             0


Firstly we need to reduce the EST_OVERALLOC to zero  so we would need to increase the pga_aggregate_target
from 1GB to 3 Gb , Also this will increase  EST_HIT_PCT from 79% to 85% which is the optimal

Also by increasing the pga_aggregate_target to 3Gb should reduce the one and multipass values




Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: