====== Oracle memory ======

Show Free space in shared-pool:
<sxh bash>
select sum(decode(name,'free memory',bytes/1024/1024)) from v$sgastat where pool = 'shared pool';
</sxh>



Show me all SGA components statistics :
<sxh bash>

column dummy noprint
column area format a20 heading 'Main SGA Areas'
column name format a20
column pool format a20
column bytes format 999,999,999,999
column sum(bytes) format 999,999,999,999

break on report
compute sum of sum(bytes)/1024/1024 on report

SELECT 1 dummy, 'DB Buffer Cache' area, name, sum(bytes)/1024/1024
FROM v$sgastat
WHERE pool is null and
name = 'db_block_buffers'
group by name
union all
SELECT 2, 'Shared Pool', pool, sum(bytes)/1024/1024
FROM v$sgastat
WHERE pool = 'shared pool'
group by pool
union all
SELECT 3, 'Large Pool', pool, sum(bytes)/1024/1024
FROM v$sgastat
WHERE pool = 'large pool'
group by pool
union all
SELECT 4, 'Java Pool', pool, sum(bytes)/1024/1024
FROM v$sgastat
WHERE pool = 'java pool'
group by pool
union all
SELECT 5, 'Redo Log Buffer', name, sum(bytes)/1024/1024
FROM v$sgastat
WHERE pool is null and
name = 'log_buffer'
group by name
union all
SELECT 6, 'Fixed SGA', name, sum(bytes)/1024/1024
FROM v$sgastat
WHERE pool is null and
name = 'fixed_sga'
group by name
ORDER BY 4 desc;
</sxh>

Show me the Shared Pool

<sxh bash>

SET LINESIZE 145
SET PAGESIZE 9999
SELECT 'Shared Pool' area, name, sum(bytes)/1024/1024
FROM v$sgastat
WHERE pool = 'shared pool' and
name in ('library cache','dictionary cache','free memory','sql area')
group by name
union all
SELECT 'Shared Pool' area, 'miscellaneous', sum(bytes)/1024/1024
FROM v$sgastat
WHERE pool = 'shared pool' and
name not in ('library cache','dictionary cache','free memory','sql area')
group by pool
order by 3 desc
/
</sxh>

Show me the PGA and UGA for each sessions
<sxh bash>
SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid                     FORMAT 999999            HEADING 'SID'
COLUMN oracle_username         FORMAT a12            HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username             FORMAT a9             HEADING 'O/S User'        JUSTIFY right
COLUMN session_program         FORMAT a25            HEADING 'Session Program' TRUNC
COLUMN session_machine         FORMAT a8             HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN session_pga_memory      FORMAT 9,999,999,999  HEADING 'PGA Memory'
COLUMN session_pga_memory_max  FORMAT 9,999,999,999  HEADING 'PGA Memory Max'
COLUMN session_uga_memory      FORMAT 9,999,999,999  HEADING 'UGA Memory'
COLUMN session_uga_memory_max  FORMAT 9,999,999,999  HEADING 'UGA Memory MAX'

SELECT
    s.sid                sid
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , s.program            session_program
  , lpad(s.machine,8)    session_machine
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and 
           sn.statistic# = ss.statistic# and
           sn.name = 'session pga memory')/1024        session_pga_memory
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and 
           sn.statistic# = ss.statistic# and
           sn.name = 'session pga memory max')/1024   session_pga_memory_max
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and 
           sn.statistic# = ss.statistic# and
           sn.name = 'session uga memory')/1024        session_uga_memory
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and 
           sn.statistic# = ss.statistic# and
           sn.name = 'session uga memory max')/1024    session_uga_memory_max
FROM 
    v$session  s
ORDER BY session_pga_memory DESC
/
</sxh>

Show me the Total and free PGA and UGA
<sxh bash>
SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid                     FORMAT 999            HEADING 'SID'
COLUMN oracle_username         FORMAT a12            HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username             FORMAT a9             HEADING 'O/S User'        JUSTIFY right
COLUMN session_program         FORMAT a18            HEADING 'Session Program' TRUNC
COLUMN session_machine         FORMAT a8             HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN session_pga_memory      FORMAT 9,999,999,999  HEADING 'PGA Memory'
COLUMN session_pga_memory_max  FORMAT 9,999,999,999  HEADING 'PGA Memory Max'
COLUMN session_uga_memory      FORMAT 9,999,999,999  HEADING 'UGA Memory'
COLUMN session_uga_memory_max  FORMAT 9,999,999,999  HEADING 'UGA Memory MAX'

SELECT
   sum((select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and 
           sn.statistic# = ss.statistic# and
           sn.name = 'session pga memory'))/1024/1024     session_pga_memory
  , sum((select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and 
           sn.statistic# = ss.statistic# and
           sn.name = 'session pga memory max'))/1024/1024  session_pga_memory_max
  , sum((select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and 
           sn.statistic# = ss.statistic# and
           sn.name = 'session uga memory'))/1024/1024      session_uga_memory
  , sum((select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and 
           sn.statistic# = ss.statistic# and
           sn.name = 'session uga memory max'))/1024/1024  session_uga_memory_max
FROM 
    v$session  s
ORDER BY session_pga_memory DESC
/
</sxh>


Display the hit ration
<sxh bash>
select TRUNC((1-(phy.value/(cur.value + con.value)))*100,2) HIT_RATIO
from   v$sysstat cur
,      v$sysstat con
,      v$sysstat phy
where  cur.name = 'db block gets'
and    con.name = 'consistent gets'
and    phy.name = 'physical reads'
/
</sxh>

Library Cache hit ratio
<sxh bash>
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM V$LIBRARYCACHE;
</sxh>


Shared pool free space
<sxh bash>
select value/1024/1024 shared_pool_size
from   v$parameter 
where name = 'shared_pool_size';
</sxh>


Show me the pools of the SGA
<sxh bash>
COLUMN pool    HEADING "Pool"
COLUMN name    HEADING "Name"
COLUMN sgasize HEADING "Allocated" FORMAT 999,999,999
COLUMN MB   HEADING "Free MB" FORMAT 999,999,999,999

SELECT
    f.pool
  , f.name
  , ROUND(s.sgasize, 2)/1024/1024 "% TOTAL MB"
  , f.bytes/1024/1024 MB
  , ROUND((f.bytes/ROUND(s.sgasize, 2)*100), 2) "% Free"
FROM
    (SELECT SUM(bytes) sgasize, pool FROM v$sgastat GROUP BY pool) s
  , v$sgastat f
WHERE
    f.name = 'free memory'
  AND f.pool = s.pool
/
</sxh>


Detect hot blocks
<sxh bash>
SET LINESIZE 200
SET VERIFY OFF

SELECT *
FROM   (SELECT name,
               addr,
               gets,
               misses,
               sleeps
        FROM   v$latch_children
        WHERE  name = 'cache buffers chains'
        AND    misses > 0
        ORDER BY misses DESC)
WHERE  rownum < 11
order by 3;
</sxh>