Wednesday, April 09, 2008

Show active sessions with physical reads and hit-ratio in Oracle

/* Formatted on (Formatter Plus v4.8.8) */
SET lines 200
PROMPT Active sessions
COLUMN schemaname format a10 heading 'Oracle ID'

COLUMN username format a10 heading 'System ID'

COLUMN program format a32 heading 'Program'

COLUMN hit_ratio format 9.90 heading'Hit Ratio'

COLUMN physical_reads format 99999999 heading 'Reads'

COLUMN sid format 99999

SELECT s.schemaname, p.username, s.program, io.physical_reads,
(io.block_gets + io.consistent_gets)
/ (io.block_gets + io.consistent_gets + io.physical_reads) hit_ratio,
s.SID, s.serial#
FROM v$session s, v$process p, v$sess_io io
WHERE s.paddr = p.addr
AND s.SID = io.SID
AND s.status = 'ACTIVE'
AND (io.block_gets + io.consistent_gets + io.physical_reads) > 0;

No comments: