Re: PGA_AGGREGATE_TARGET question

There are some nice views that help you in sizing PGA_AGGREGATE_TARGET (P_A_T). If you size this parameter too high, then you will be wasting memory, and if it is sufficiently high enough, this can cause swapping to occur in your database server.

To make sure that it is high enough, you can follow these guidelines:

1. Issue the following:

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

You will get output like the following:

NAME                                          VALUE
---------------------------------------- ----------
workarea executions - optimal                   510
workarea executions - onepass                     1
workarea executions - multipass                   4

   You want to make sure that you do not have any mutlipass executions. Increase P_A_T to make multipass executions zero. Ideally, you want to have onepass executions zero as well. If all your executions are optimal, then your P_A_T is high enough.

2. Issue the following:

select * from v$pgastat;

You will get output similar to the following:

NAME                                          VALUE UNIT
---------------------------------------- ---------- ------------
aggregate PGA target parameter             10485760 bytes
aggregate PGA auto target                   4248576 bytes
global memory bound                          524288 bytes
total PGA inuse                             5760000 bytes
total PGA allocated                        10342400 bytes
maximum PGA allocated                      42925056 bytes
total freeable PGA memory                     65536 bytes
PGA memory freed back to OS                 9306112 bytes
total PGA used for auto workareas                 0 bytes
maximum PGA used for auto workareas          631808 bytes
total PGA used for manual workareas               0 bytes
maximum PGA used for manual workareas        529408 bytes
over allocation count                          9201
bytes processed                            55100416 bytes
extra bytes read/written                  159971328 bytes
cache hit percentage                          25.61 percent

You want the 'over allocation count' statistic to be zero. You also want the 'cache hit percentage' to be closer to 100. Finally, you want to make sure that the 'aggregate PGA auto target' is close to the value of 'aggregate PGA target parameter'.

3. Finally, you can use this query:

select round(pga_target_for_estimate/1024/1024) as target_size_MB, bytes_processed,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;

This will produce output similar to the following:

TARGET_SIZE_MB BYTES_PROCESSED EST_RW_EXTRA_BYTES EST_HIT_PCT EST_OVERALLOC

-------------- --------------- ------------------ -----------
-------------
            10        45456384           84205568         
35             2
            20        45456384           78962688         
37             0
            40        45456384           19740672         
70             0
            60        45456384           19740672         
70             0
            80        45456384           19740672         
70             0
            96        45456384           19740672         
70             0
           112        45456384           19740672         
70             0
           128        45456384           13095936         
78             0
           144        45456384           13095936         
78             0
           160        45456384           13095936         
78             0
           240        45456384           13095936         
78             0
           320        45456384                  0        
100             0
           480        45456384                  0        
100             0
           640        45456384                  0        
100             0

In the case of the figures above, you'll need to know that P_A_T is currently set at 80MB. You can see that if I increase P_A_T to 320MB, then my estimated read/write extra bytes drops to zero (this is a good thing) and that my estimated cache hit percentage hits 100%. Anything over 320MB does not give us any benefit and will waste memory. I also suspect that the "better" value for P_A_T is somewhere betwen 240MB and 320MB. The target advice does not give us fine enough granularity that far away from the current setting.

After you've changed your P_A_T, you'll want to consult these views again.

HTH,
Brian

Bob Maggio wrote:
>
> We are moving to PGA_AGGREGATE_TARGET from sort_area sizes etc. However, I
> am unsure about sizing it. If I size it too large, is there any drawbacks,
> aside from some wasted memory? I'd rather err on the side of caution if
> possible. I've heard that 1m per connection is a good starting point. If I
> average 650 concurrent sessions, with maybe 10 to 20 active ones at any
> given moment, is setting it to 650m ok? I've used this in test DBs but
> haven't yet tried it on PROD ones. This is a 9i, not 9iR2 DB.
> Thanks for any rule of thumb advice.
> Bob

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"