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.