\n \n \n| Related Documentation | \nVersion of up.time \naffected | \nAffected Platforms | \n
\n \n| \n | \nAll | \nAll | \n
\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.
...
Required Permissions \n
The Oracle (Advanced Metrics) service monitor selects from the following objects: \n
...
- v$sysstat
\n\t- v$rowcache
\n\t- v$librarycache
\n\t- v$session
\n\t- dba_blockers
\n
...
Metrics
...
\n \n\t| Metric | \n\t \n\t \n \n \n\t \n\t| The number of buffer cache hits that are completed without accessing disk I/O. |
\n\t \ncolumn column 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 \n
where name in ('db block gets', 'consistent gets', 'physical reads')
|
\n \n \n \n \n \n\t| Data Dictionary Cache Hits Ratio |
\n\t| The number of data dictionary cache hits that are completed without accessing disk I/O. |
\n\t \ncolumn column "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 \n \n \n \n \n\t \n\t | The rate at which library cache pin misses occur. |
\n\t \ncolumn column "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 \n \n \n \n \n \n\t| Redo Log Space Request Ratio |
\n\t| The number of redo log space requests per minute that have been made since the server was started. |
\n\t \ncolumn column "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 \n \n
where name in ('redo log space requests', 'redo entries')
|
\n \n \n \n \n\t \n\t| The rate of Oracle sorts that are too large to be completed in memory and which are sorted using a temporary segment. |
\n\t \nselect select 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 \n \n \n\t \n \n\t \nset | The number of active sessions based on the value of V$PARAMETER.PROCESSES in the file init.ora. |
\n\tset line 120 pagesize 1000 feed off; |
\n \n \n \n \n \n \n \n \n \n
where username is not null
|
\n
and audsid <> userenv('SESSIONID')
|
\n \n \n \n \n \n \n\t \n\t| The number of sessions that are preventing other sessions from committing changes to the Oracle database. |
\n\t \nset set 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 \n \n\t \n \n\t| The number of Oracle sessions that are idle. |
\n\t \nset set line 120 pagesize 1000 feed off; |
\n \n \n \n \n \n \n \n \n \n
where username is not null
|
\n
and audsid <> userenv('SESSIONID')
|
\n \n \n \n \n ...
