Oracle Advanced Metrics service monitor

column hit heading "HIT RATIO (%)" format 990.9
column "SHARED POOL" format A16
select round(100 * (sum(decode(name, 'physical reads', 0, value))
- sum(decode(name,'physical reads',value,0)))
/ sum(decode(name,'physical reads',0,value)),1) hit
from v$sysstat
where name in ('db block gets', 'consistent gets', 'physical reads')
;

column "HIT RATIO (%)" format 990.9
column "SHARED POOL" format A16
select 'DICTIONARY CACHE' "SHARED POOL",
least(100, round(100 * sum(gets - getmisses + (usage - fixed))
/ sum(gets), 2)) "HIT RATIO (%)"
from v$rowcache
;

column "HIT RATIO (%)" format 990.9
column "SHARED POOL" format A16
select 'LIBRARY CACHE' "SHARED POOL",
least(100, round(100 * sum(pinhits)/sum(pins), 2)) "HIT RATIO (%)"
from v$librarycache
where pins > 0
;

column "Space requests (%)" format 990.9
select round(100 * sum(decode(name, 'redo log space requests', value, 0))
/sum(decode(name, 'redo entries', value, 0)), 1)
"Space requests (%)"
from v$sysstat
where name in ('redo log space requests', 'redo entries')
;

select d.value * 100/(d.value + m.value) DISK_SORT_RATE
from v$sysstat m, v$sysstat d
where m.name='sorts (memory)' and d.name='sorts (disk)'
;

set line 120 pagesize 1000 feed off;
column "sid" format 999
column "user" format a10
column "st" format a8
column "prg" format a30
select status "st",
sid "sid",
username "user",
program "prg"
from v$session
where username is not null
and audsid <> userenv('SESSIONID')
order by status
;

set line 120 pagesize 1000 feed off;
select a.sid, a.username, a.program
from v$session a, dba_blockers b
where a.sid = b.holding_session
;

set line 120 pagesize 1000 feed off;
column "sid" format 999
column "user" format a10
column "st" format a8
column "prg" format a30
select status "st",
sid "sid",
username "user",
program "prg"
from v$session
where username is not null
and audsid <> userenv('SESSIONID')
order by status
;