How to find history of SGA/PAGA usage

For resource planning it is vital to know how the memory usage is changing during night/day  time. 

Most often the database load varies over time.  If you know the hourly usage of the SGA / PGA , you can better plan your resources or tune your instance.

You need to have diagnostic pack for your database to get this information.

Here is a query getting hourly  sga/pga memory usages of an Oracle instance. 

select sn.INSTANCE_NUMBER, sga.allo sga, pga.allo pga,(sga.allo+pga.allo) tot,

trunc(SN.END_INTERVAL_TIME,'mi') time
  from
(select snap_id,INSTANCE_NUMBER,round(sum(bytes)/1024/1024/1024,3) allo 
   from DBA_HIST_SGASTAT 
  group by snap_id,INSTANCE_NUMBER) sga
,(select snap_id,INSTANCE_NUMBER,round(sum(value)/1024/1024/1024,3) 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
;

Sample Output

----------------------

INSTANCE_NUMBER        SGA        PGA        TOT TIME
--------------- ---------- ---------- ---------- ---------
              1      69.75       .278     70.028 17-DEC-14
              1     69.751       .277     70.028 17-DEC-14
              1     69.753       .276     70.029 17-DEC-14
              1     69.764       .257     70.021 17-DEC-14
              1     69.798       .304     70.102 17-DEC-14
              1     69.798       .301     70.099 17-DEC-14
              1     69.798       .303     70.101 17-DEC-14
              1     69.798       .304     70.102 17-DEC-14
              1     69.798       .307     70.105 17-DEC-14
              1     69.798       .307     70.105 17-DEC-14
              1     69.798       .307     70.105 17-DEC-14
              1     69.799       .307     70.106 17-DEC-14
              1       69.8       .309     70.109 17-DEC-14
              1       69.8       .308     70.108 17-DEC-14
              1     69.805       .304     70.109 16-DEC-14
              1     69.806       .304      70.11 16-DEC-14
              1     69.791       .301     70.092 16-DEC-14
              1     69.793       .292     70.085 16-DEC-14
              1     69.793        .29     70.083 16-DEC-14