How to check historical SGA and PGA allocation from AWR in Oracle

Historical view of SGA and PGA allocation across AWR snapshots — useful for spotting when memory pressure started or how memory changed after a restart or a resize.

DBA_HIST_SGASTAT, DBA_HIST_PGASTAT, and DBA_HIST_SNAPSHOT are part of Oracle Diagnostic Pack and require that license on Enterprise Edition.

SET LINES 1000 PAGES 1000

SELECT sn.instance_number,
       sga.allo AS sga_gb,
       pga.allo AS pga_gb,
       (sga.allo + pga.allo) AS total_gb,
       TRUNC(sn.end_interval_time, 'mi') AS time
FROM (SELECT snap_id, instance_number,
             ROUND(SUM(bytes) / 1024 / 1024 / 1024, 3) AS allo
      FROM   dba_hist_sgastat
      GROUP BY snap_id, instance_number) sga,
     (SELECT snap_id, instance_number,
             ROUND(SUM(value) / 1024 / 1024 / 1024, 3) AS allo
      FROM   dba_hist_pgastat
      WHERE  name = 'total PGA allocated'
      GROUP BY snap_id, instance_number) pga,
     dba_hist_snapshot sn
WHERE  sn.snap_id         = sga.snap_id
AND    sn.instance_number = sga.instance_number
AND    sn.snap_id         = pga.snap_id
AND    sn.instance_number = pga.instance_number
ORDER BY sn.snap_id DESC, sn.instance_number;