Andrew Fraser DBA

Calculate optimal PGA size

Simple script to calculate optimal pga_aggregate_target size (in gb):

col avg_tpi form 999.99
col max_tpi form 999.99
select instance_number, avg(value)/1024/1024/1024 avg_tpi , max(value)/1024/1024/1024 max_tpi
from dba_hist_pgastat
where name = 'total PGA inuse'
group by instance_number ;

dba_hist_pgastat has 1 weeks worth of data by default, snapshots taken at 1 hour intervals.

Memory not assigned to pga_aggregate_target can be better used to increase sga_target. Rules of thumb/dbca defaults will assign 2/3 memory to sga and 1/3 to pga for general purpose/transactional databases, and 1/2 sga 1/2 pga for data warehouse databases – a reasonable starting point, but it can often turn out to be more than the pga really wants to use especially for larger systems.