You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

\n \n \n \n \n \n \n \n \n \n \n \n \n
Related DocumentationVersion of up.time \naffectedAffected Platforms
\nAllAll
\n \n

The Oracle (Advanced Metrics) service monitor provides checks on several internal Oracle performance statistics. To gather these statistics up.time 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.

\n \n

Required Permissions
\n

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

    \n\t
  • v$sysstat
    • \n\t
  • v$rowcache
    • \n\t
  • v$librarycache
    • \n\t
  • v$session
    • \n\t
  • dba_blockers
    • \n
\n \n

Metrics

\n \n
\n \n\t \n\t \n\t \n \n \n\t \n\t \n\t \n \n \n\t \n\t \n\t \n \n \n\t \n\t \n\t \n \n \n\t \n\t \n\t \n \n \n\t \n\t \n\t \n \n \n \n\t \n\t \n\t \n \n \n \n\t \n\t \n\t \n \n \n\t \n\t \n\t \n \n
MetricExplanationQuery
Buffer Cache Hits RatioThe number of buffer cache hits that are completed without accessing disk I/O.

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

\n
Data Dictionary Cache Hits RatioThe number of data dictionary cache hits that are completed without accessing disk I/O.

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

Library Cache Hits RatioThe rate at which library cache pin misses occur.

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

Redo Log Space Request RatioThe number of redo log space requests per minute that have been made since the server was started.

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

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

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

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

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

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

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

Oracle Idle SessionsThe number of Oracle sessions that are idle.

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

\n
  • No labels