How to determine if PGA is set properly

PGA is a memory region, that contains data and control information for a server process. This is not shared region. For each server process there is one PGA allocated.

In order to determine the proper size of PGA, we need to examine measuring criteria from performance views.

1. v$PGASTAT –displays PGA memory usage statistics.

select name,value
from v$pgastat
where name in ('aggregate PGA target parameter'
               ,'aggregate PGA auto target'
               ,'total PGA inuse'
               ,'total PGA allocated'
               ,'over allocation count'
               ,'extra bytes read/written'
               ,'cache hit percentage')

—My output

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
aggregate PGA target parameter                                   8589934592
aggregate PGA auto target                                         536870912
total PGA inuse                                                 18699225088
total PGA allocated                                             23640664064
over allocation count                                                187532
extra bytes read/written                                       305283407872
cache hit percentage                                                  99.2

7 rows selected
As you can see “total PGA inuse” is greater than “aggregate PGA target parameter” this happens when PGA_AGGREGATE_TARGET is too small and it in turn causes lots of system I/O as indicated “extra bytes read/written”.
2. v$SQL_WORKAREA – displays information about work areas used by SQL cursors. SQL statements, that are stored in the shared pool have one or more child cursor that are shown in V$SQL view. And v$SQL_WORKAREA shows all work areas needed by these child cursors.
The important columns of this view are the followings:

OPTIMAL_EXECUTIONS– number of times, this work area ran optimally without using temporary tablespace.

ONEPASS_EXECUTIONS– number of times, this work area used temporary tablespace only once to get it finished.

MULTIPASSES_EXECUTIONS-number of times, this work area used temporary tablespace in multiple times to get it finished.

Let’s see the total picture, how are they distributed:

select sum(optimal_executions) optimal,
       sum(onepass_executions) onepass,
       sum(multipasses_executions) multipass
from v$sql_workarea

—My output

------- ---------- ----------
0487582         51         16
We have 51 onepass and 16 multipass executions… They are not big numbers but it would be better if all of them would be optimal.
So we should think about properly adjusting PGA size.
3. If we join the following views v$SESSTAT, v$SYSSTAT and v$STATNAME we can see if PGA is properly set or not:

select,sum(a.value) value from v$sesstat a, v$statname b where a.STATISTIC#=b.STATISTIC# and like '%workarea executions – %' group by;

—My output

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
workarea executions - onepass                                            19
workarea executions - multipass                                           0
workarea executions - optimal                                       2633589


select, sum(a.value) value
from v$sysstat a, v$statname b
and like '%workarea executions - %'
group by;

—My output

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
workarea executions - onepass                                          4712
workarea executions - multipass                                         800
workarea executions - optimal                                    9760345515


The result above shows that PGA size is not properly set because number of onepass and multipass are considerable.

4. v$PGA_TARGET_ADVICE– view shows the predicted cache hit-ratio improvement.

select round(pga_target_for_estimate/1024/1024) pga_size_mb
from v$pga_target_advice;

—My output

----------- ----------------------------- --------------------
       1024                            96                38530
       2048                            97                28413
       4096                            97                19187
       6144                            97                17741
       8192                           100                16326
       9830                           100                15458
      11469                           100                15049
      13107                           100                14336
      14746                           100                13136
      16384                           100                10570
      24576                           100                    0
      32768                           100                    0
      49152                           100                    0
      65536                           100                    0

Setting PGA to 24576MB can eliminate over allocation count and gain maximum hit ratio.

To change PGA size:

alter system set pga_aggregate_target=24576m;

Leave a Reply

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

You are commenting using your 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: