PGA_AGGREGATE_TARGET vs PGA_AGGREGATE_LIMIT

WITH

pgastat_denorm_1 AS (

SELECT /*+ MATERIALIZE NO_MERGE */

       snap_id,

       dbid,

       instance_number,

       SUM(CASE name WHEN 'PGA memory freed back to OS'           THEN value ELSE 0 END) pga_mem_freed_to_os,

       SUM(CASE name WHEN 'aggregate PGA auto target'             THEN value ELSE 0 END) aggr_pga_auto_target,

       SUM(CASE name WHEN 'aggregate PGA target parameter'        THEN value ELSE 0 END) aggr_pga_target_param,

       SUM(CASE name WHEN 'bytes processed'                       THEN value ELSE 0 END) bytes_processed,

       SUM(CASE name WHEN 'extra bytes read/written'              THEN value ELSE 0 END) extra_bytes_rw,

       SUM(CASE name WHEN 'global memory bound'                   THEN value ELSE 0 END) global_memory_bound,

       SUM(CASE name WHEN 'maximum PGA allocated'                 THEN value ELSE 0 END) max_pga_allocated,

       SUM(CASE name WHEN 'maximum PGA used for auto workareas'   THEN value ELSE 0 END) max_pga_used_aut_wa,

       SUM(CASE name WHEN 'maximum PGA used for manual workareas' THEN value ELSE 0 END) max_pga_used_man_wa,

       SUM(CASE name WHEN 'total PGA allocated'                   THEN value ELSE 0 END) tot_pga_allocated,

       SUM(CASE name WHEN 'total PGA inuse'                       THEN value ELSE 0 END) tot_pga_inuse,

       SUM(CASE name WHEN 'total PGA used for auto workareas'     THEN value ELSE 0 END) tot_pga_used_aut_wa,

       SUM(CASE name WHEN 'total PGA used for manual workareas'   THEN value ELSE 0 END) tot_pga_used_man_wa,

       SUM(CASE name WHEN 'total freeable PGA memory'             THEN value ELSE 0 END) tot_freeable_pga_mem

  FROM dba_hist_pgastat

 WHERE name IN

('PGA memory freed back to OS'

,'aggregate PGA auto target'

,'aggregate PGA target parameter'

,'bytes processed'

,'extra bytes read/written'

,'global memory bound'

,'maximum PGA allocated'

,'maximum PGA used for auto workareas'

,'maximum PGA used for manual workareas'

,'total PGA allocated'

,'total PGA inuse'

,'total PGA used for auto workareas'

,'total PGA used for manual workareas'

,'total freeable PGA memory'

)

   AND snap_id in (select snap_id from dba_hist_snapshot where begin_interval_time > sysdate -60)

 GROUP BY

       snap_id,

       dbid,

       instance_number

),

pgastat_denorm_2 AS (

SELECT /*+ MATERIALIZE NO_MERGE */

       h.dbid,

       h.instance_number,

       s.startup_time,

       MIN(h.pga_mem_freed_to_os) pga_mem_freed_to_os,

       MIN(h.bytes_processed) bytes_processed,

       MIN(h.extra_bytes_rw) extra_bytes_rw

  FROM pgastat_denorm_1 h,

       dba_hist_snapshot s

 WHERE s.snap_id = h.snap_id

   AND s.dbid = h.dbid

   AND s.instance_number = h.instance_number

 GROUP BY

       h.dbid,

       h.instance_number,

       s.startup_time

),

pgastat_delta AS (

SELECT /*+ MATERIALIZE NO_MERGE */

       h1.snap_id,

       h1.dbid,

       h1.instance_number,

       s1.begin_interval_time,

       s1.end_interval_time,

       ROUND((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 24 * 60 * 60) interval_secs,

       (h1.pga_mem_freed_to_os - h0.pga_mem_freed_to_os) pga_mem_freed_to_os,

       h1.aggr_pga_auto_target,

       h1.aggr_pga_target_param,

       (h1.bytes_processed - h0.bytes_processed) bytes_processed,

       (h1.extra_bytes_rw - h0.extra_bytes_rw) extra_bytes_rw,

       h1.global_memory_bound,

       h1.max_pga_allocated,

       h1.max_pga_used_aut_wa,

       h1.max_pga_used_man_wa,

       h1.tot_pga_allocated,

       h1.tot_pga_inuse,

       h1.tot_pga_used_aut_wa,

       h1.tot_pga_used_man_wa,

       h1.tot_freeable_pga_mem

  FROM pgastat_denorm_1 h0,

       pgastat_denorm_1 h1,

       dba_hist_snapshot s0,

       dba_hist_snapshot s1,

       pgastat_denorm_2 min /* to see cumulative use (replace h0 with min on select list above) */

 WHERE h1.snap_id = h0.snap_id + 1

   AND h1.dbid = h0.dbid

   AND h1.instance_number = h0.instance_number

   AND s0.snap_id = h0.snap_id

   AND s0.dbid = h0.dbid

   AND s0.instance_number = h0.instance_number

   AND s1.snap_id = h1.snap_id

   AND s1.dbid = h1.dbid

   AND s1.instance_number = h1.instance_number

   AND s1.snap_id = s0.snap_id + 1

   AND s1.startup_time = s0.startup_time

   AND s1.begin_interval_time > (s0.begin_interval_time + (1 / (24 * 60))) /* filter out snaps apart < 1 min */

   AND min.dbid = s1.dbid

   AND min.instance_number = s1.instance_number

   AND min.startup_time = s1.startup_time

)

SELECT snap_id,

       TO_CHAR(MIN(begin_interval_time), 'YYYY-MM-DD HH24:MI') begin_time,

       TO_CHAR(MIN(end_interval_time), 'YYYY-MM-DD HH24:MI') end_time,

       ROUND(SUM(pga_mem_freed_to_os) / POWER(2, 30), 3) pga_mem_freed_to_os,

       ROUND(SUM(aggr_pga_auto_target) / POWER(2, 30), 3) aggr_pga_auto_target,

       ROUND(SUM(aggr_pga_target_param) / POWER(2, 30), 3) aggr_pga_target_param,

       ROUND(SUM(bytes_processed) / POWER(2, 30), 3) bytes_processed,

       ROUND(SUM(extra_bytes_rw) / POWER(2, 30), 3) extra_bytes_rw,

       ROUND(SUM(global_memory_bound) / POWER(2, 30), 3) global_memory_bound,

       ROUND(SUM(max_pga_allocated) / POWER(2, 30), 3) max_pga_allocated,

       ROUND(SUM(max_pga_used_aut_wa) / POWER(2, 30), 3) max_pga_used_aut_wa,

       ROUND(SUM(max_pga_used_man_wa) / POWER(2, 30), 3) max_pga_used_man_wa,

       ROUND(SUM(tot_pga_allocated) / POWER(2, 30), 3) tot_pga_allocated,

       ROUND(SUM(tot_pga_inuse) / POWER(2, 30), 3) tot_pga_inuse,

       ROUND(SUM(tot_pga_used_aut_wa) / POWER(2, 30), 3) tot_pga_used_aut_wa,

       ROUND(SUM(tot_pga_used_man_wa) / POWER(2, 30), 3) tot_pga_used_man_wa,

       ROUND(SUM(tot_freeable_pga_mem) / POWER(2, 30), 3) tot_freeable_pga_mem,

       0 dummy_15

  FROM pgastat_delta

 GROUP BY

       snap_id

 ORDER BY

       snap_id;

Captured and imported the result into Excel spreadsheet. Then I created the following two charts showing PGA allocation and usage for the past 60 days and 7 days.

Source :https://weidongzhou.wordpress.com/2016/02/25/pga_aggregate_target-vs-pga_aggregate_limit/