Oracle instance memory usage

The right place to look for memory usage of a process in at operating system level. Interpreting the shared memory usage might be misleading. The top command seems to add the shared part to each process. And thou makes it hard to get right numbers. Here is a blog post trying to do that. Thou you could ask the numbers also from Oracle. Parameters influencing Oracle instance memory usage limits. 11g instance might have memory target set or sga and pga set separately.

select name,display_value 
  from v$parameter 
 where name like 'mem%target' 
    or name like 'pga%' 
    or name like 'sga%'
;

The actual PGA usage might hit and go above those limits. The current allocation can be queried.

select round(sum(bytes)/1024/1024/1024,3) SGA_G 
  from v$sgastat;

select round(value/1024/1024/1024,3) PGA_G
  from v$pgastat 
 where name = 'total PGA allocated';

Most often the database load varies over time. For resource planning it is vital to know how the memory usage is changing during night time. There might be going on some batch jobs whose behavior is not seen during day time. If you have diagnostics pack purchased you can also ask what was the situation earlier. Here is a query getting hourly 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
;