SGA

September 3rd, 2012

SGA usage

SET LINESIZE 145
SET PAGESIZE 9999
SET FEEDBACK off
SET VERIFY   off

COLUMN bytes   FORMAT  999,999,999
COLUMN percent FORMAT  999.99999

break on report

compute sum of bytes on report
compute sum of percent on report

SELECT
    a.name
  , a.bytes
  , a.bytes/(b.sum_bytes*100)  Percent  
FROM sys.v_$sgastat a
   , (SELECT SUM(value)sum_bytes FROM sys.v_$sga) b 
ORDER BY bytes DESC
/


SGA free pool

COLUMN pool    HEADING "Pool"
COLUMN name    HEADING "Name"
COLUMN sgasize HEADING "Allocated" FORMAT 999,999,999
COLUMN bytes   HEADING "Free" FORMAT 999,999,999

SELECT
    f.pool
  , f.name
  , s.sgasize
  , f.bytes
  , ROUND(f.bytes/s.sgasize*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
/


Comments are closed.