How to determine if PGA is set properly
May 31, 2011 2 Comments
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
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
OPTIMAL ONEPASS MULTIPASS
------- ---------- ----------
0487582 51 16
select b.name,sum(a.value) value from v$sesstat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.name like '%workarea executions – %' group by b.name;
—My output
NAME VALUE ---------------------------------------------------------------- ---------- workarea executions - onepass 19 workarea executions - multipass 0 workarea executions - optimal 2633589
select b.name, sum(a.value) value from v$sysstat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.name like '%workarea executions - %' group by b.name;
—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 ,estd_pga_cache_hit_percentage ,estd_overalloc_count from v$pga_target_advice;
—My output
PGA_SIZE_MB ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------- ----------------------------- --------------------
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;
About Mariami Kupatadze
DBA Knowledge Base 
Nice!
More than nice!!!