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 
;