ITRemote

Col pool_name format a15
Col in_mb format 999,999
Col inuse_mb format 999,999

Break on snap_id skip 1

Select r.snap_id,cast(k.begin_interval_time as Date) snap_time,r.inst_id,r.pool_name,
       round(sum(r.in_mb)) In_Mb,
       round(sum(r.inuse_mb)) Inuse_Mb
  from (select snap_id,
               instance_number inst_id,
               case when name = 'buffer_cache'
                    then 'db_cache_size'
                    when name = 'log_buffer'
                    then 'log_buffer'
               else pool
                end pool_name,
               bytes/1024/1024 in_mb,
               case when name <> 'free memory'
                    then bytes/1024/1024
                end inuse_mb
           from dba_hist_sgastat
          where instance_number = &inst_id
            and snap_id between &beg_snap and &end_snap
          ) r,
         dba_hist_snapshot k
  where r.snap_id = k.snap_id
    and r.inst_id = k.instance_number
    and r.pool_name is not null
  group by r.snap_id,cast(k.begin_interval_time as Date),r.inst_id,r.pool_name
  order by 1,2 asc, 5 desc
/