The Oracle (Advanced Metrics) service monitor provides checks on several internal Oracle performance statistics. To gather these statistics Uptime Infrastructure Monitor runs several queries directly against the Oracle database. Information on the exact queries and the required permissions for the Oracle (Advanced Metrics) monitor are outlined in this article.

Required Permissions

The Oracle (Advanced Metrics) service monitor selects from the following objects:

  • v$sysstat
  • v$rowcache
  • v$librarycache
  • v$session
  • dba_blockers

Metrics

MetricExplanationQuery
Buffer Cache Hits RatioThe 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 RatioThe 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 RatioThe 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 RatioThe 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 RateThe 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 SessionsThe 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 SessionsThe 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 SessionsThe 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
;