Metric

Explanation

Query

Buffer Cache Hits Ratio

The number of buffer cache hits that are completed without accessing disk I
/O.

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')
  ;

Data Dictionary Cache Hits Ratio

The number of data dictionary cache hits that are completed without accessing disk I/O.

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
;

Library Cache Hits Ratio

The rate at which library cache pin misses occur.

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
;

Redo Log Space Request Ratio

The number of redo log space requests per minute that have been made since the server was started.

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')
;

Disk Sort Rate

The rate of Oracle sorts that are too large to be completed in memory and which are sorted using a temporary segment.

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)'
;

Active Sessions

The number of active sessions based on the value of V$PARAMETER. PROCESSES in the file init.ora.

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
;

Oracle Blocking Sessions

The number of sessions that are preventing other sessions from committing changes to the Oracle database.

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
;

Oracle Idle Sessions

The number of Oracle sessions that are idle.

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
;