Oracle memory
Show Free space in shared-pool:
select sum(decode(name,'free memory',bytes/1024/1024)) from v$sgastat where pool = 'shared pool';
Show me all SGA components statistics :
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;
Show me the Shared Pool
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
/
Show me the PGA and UGA for each sessions
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
/
Show me the Total and free PGA and UGA
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
/
Display the hit ration
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' /
Library Cache hit ratio
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
Shared pool free space
select value/1024/1024 shared_pool_size from v$parameter where name = 'shared_pool_size';
Show me the pools of the SGA
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
/
Detect hot blocks
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;