article Oracle Advanced Metrics service monitor

Related Documentation Version of up.time affected Affected Platforms
All All

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.

Required Permissions

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

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

Metrics

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
;

Related Articles


MySQL Advanced Metrics

RatingViews
article

This article outlines the metrics collected by the up.time MySQL Advanced monitor.

By: uptime Support | Date Created: 1-26-2007 | Last Modified: 7-4-2011 | Index: 126

  4583

SQL Server Advanced Metrics

RatingViews
article

This article outlines the performance metrics that the up.time SQL Server Advanced Metrics monitor collects from a SQL Server database.

By: uptime Support | Date Created: 1-23-2007 | Last Modified: 1-3-2012 | Index: 122

  5370

Creating Custom Service Monitors with Retained Data Collection

RatingViews
article

By: uptime Support | Date Created: 5-15-2006 | Last Modified: 12-16-2011 | Index: 067

  10473

Receiving "Could not connect to database" from Oracle Monitor

RatingViews
article

This article suggests how to troubleshoot and resolve issues related to connecting to an Oracle database with any of the three up.time Oracle service monitors (Basic Check, Advanced Metrics or...

By: uptime Support | Date Created: 8-10-2012 | Last Modified: 8-11-2012 | Index: 583

  1514

Using retained service metrics data to generate graphs

RatingViews
article

Yes it is definitely possible to graph and even report on the data that is collected. First, you need to make sure that in the service monitor that you have the Retained Data checkboxes checked,...

By: uptime Support | Date Created: 10-8-2009 | Last Modified: 8-13-2011 | Index: 398

  2120

User Comments



No comments have been posted.

Copyright © 2021 IDERA, Inc.   Legal   Privacy Statement