Topics Releated to SGA

SGA Free Pool Report :

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;

Display Hit Ratio:

SELECT
        p.username            unix_id
      , s.username            oracle_id
      , s.osuser              os_user
      , s.sid                 sid
      , s.serial#             serial_id
      , LPAD(p.spid,7)        unix_pid
      , sio.consistent_gets   consistent_gets
      , sio.block_gets        block_gets
      , sio.physical_reads    physical_reads
      , ROUND((consistent_gets+Block_gets-Physical_reads) /
              (Consistent_gets+Block_gets)*100,2)             hit_ratio
    FROM
        v$process p
      , v$session s
      , v$sess_io sio
    WHERE
          p.addr=s.paddr
      AND s.sid = sio.sid
      AND (sio.consistent_gets + sio.block_gets) > 0
      AND s.username is not null
    ORDER BY hit_ratio
/

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: