How to check memory components of your database? (Oracle)

This script shows you information about your memory configurations, current usage, adviser views, etc. Before jumping into any conclusions, you should check if the database is using AMM, HugePage, or any other OS related configurations. Changing memory configuration involves at least a rolling restart in most cases.

prompt ====================================================
prompt == start of check_memory.sql
prompt ====================================================

prompt ====================================================
prompt == Show Memory parameters
prompt ====================================================
show parameter memory

prompt ====================================================
prompt == Top 50 memory allocations by sessions
prompt ====================================================

SET LINESIZE 300
SET Pages 300

COLUMN username FORMAT A20
COLUMN module FORMAT A20

SELECT * FROM
(SELECT a.inst_id,
       NVL(a.username,'(oracle)') AS username,
       a.sid,
       a.module,
       a.program,
       Trunc(b.value/1024/1024) AS memory_mb
FROM   gv$session a,
       gv$sesstat b,
       gv$statname c
WHERE  a.sid = b.sid
AND    a.inst_id = b.inst_id
AND    b.statistic# = c.statistic#
AND    b.inst_id = c.inst_id
AND    c.name = 'session pga memory'
AND    a.program IS NOT NULL
ORDER BY b.value DESC)
WHERE rownum <=50;

prompt ====================================================
prompt == Show SGA PGA hourly usage in last 10 days
prompt ====================================================
set lines 200
set pages 1000
select sn.INSTANCE_NUMBER, sga.allo sga, pga.allo pga,(sga.allo+pga.allo) totalGb,to_char(SN.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI') datetime
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
and sn.END_INTERVAL_TIME > sysdate - 10
order by sn.snap_id desc, sn.INSTANCE_NUMBER
;
prompt ====================================================
prompt == V$SHARED_POOL_ADVICE
prompt ====================================================
select * from V$SHARED_POOL_ADVICE;

prompt ====================================================
prompt == V$SGA_TARGET_ADVICE
prompt ====================================================
select * from v$sga_target_advice;

prompt ====================================================
prompt == V$MEMORY_TARGET_ADVICE
prompt ====================================================
select * from  v$memory_target_advice;

prompt ====================================================
prompt == Show SGA
prompt ====================================================
show sga
show parameter sga

prompt ====================================================
prompt == Current Free SGA
prompt ====================================================
select current_size/1024/1024/1024 free_gb
from V$SGA_DYNAMIC_FREE_MEMORY;

prompt ====================================================
prompt == Current SGA Components
prompt ====================================================
select component, current_size/1024/1024/1024 current_Gb
from V$SGA_DYNAMIC_COMPONENTS;

prompt ====================================================
prompt == Show PGA
prompt ====================================================
show parameter pga

prompt ====================================================
prompt == Max PGA allocated
prompt ====================================================
select name, value/1024/1024/1024 Gb
from V$PGASTAT where name='maximum PGA allocated';


prompt ====================================================
prompt == End of check_memory.sql
prompt ====================================================