Oracle metrics collected by DPA
The following sections list the metrics that DPA collects for Oracle databases. Some metrics are not available for all Oracle deployments.
- Learn how to view these metrics and change the thresholds.
- For detailed information about resolving issues, click the
next to the metric on the Resources tab. The Information link is not available for all metrics. - If the database instance runs on a virtual machine (VM), metrics collected for the VM are described in VM metrics collected by DPA
ASM
| Metric | Description |
|---|---|
| ASM Summary Reads | The total number of all I/O read requests. |
| ASM Summary Writes | The total number of all I/O write requests. |
| ASM Summary Read Time | The average I/O time per read request over all disks. |
| ASM Summary Write Time | The average I/O time per write request over all disks. |
| ASM Summary Write Rate | The total number of kilobytes written to disk every second. |
| ASM Summary Read Rate | The total number of kilobytes read from disk every second. |
Connections
| Metric | Description |
|---|---|
| Connected Devices | The number of distinct client machines connected to this instance (even if the connection is idle). |
| Connected Users | The number of distinct users connected to this instance (even if the connection is idle). |
| Sessions | The number of sessions connected to this instance (even if the connection is idle). |
CPU
| Metric | Description |
|---|---|
| Core Count | The number of cores used by the instance. |
| CPU Utilization by DB | The percentage of CPU being utilized by the database instance, which is a subset of the CPU utilized by the entire system. Oracle supplies this value only if the database parameter timed_statistics = TRUE. If this is high, use DPA Trends charts to review queries waiting on CPU. |
| O/S CPU Utilization | The percentage of CPU being utilized by the entire system. If this is high, compare this utilization with the CPU Utilization By Oracle metric. If most of the CPU is being used by Oracle, use the DPA Trends charts to review queries waiting on CPU. If Oracle is not using a significant portion of total CPU, review other non-Oracle programs running at this time. |
Disk
| Metric | Description |
|---|---|
| DB Commit Time | The average number of milliseconds waiting for the log file sync event indicating commit times for this database. |
| DB Multi Block Disk Read Time | The average number of milliseconds waiting for the db file scattered read event in this database. If this is high, contact your system administrator to understand why these disk reads are slow. Use DPA to drill in to the db file scattered read waits and use the Files tab to show the disks involved. |
| DB Physical I/O Rate | The number of kilobytes being read and written to disk every second for this database. If this is high, drill in to the DPA Trends charts and review physical read and write wait events. |
| DB Physical Read Rate | The number of kilobytes being read from disk every second for this database. If this is high, drill in to the DPA Trends charts and review queries waiting on physical read wait events (for example, db file scattered read or db file sequential read). |
| DB Physical Write Rate | The number of kilobytes being written to disk every second for this database. If this is high, drill in to the DPA Trends charts and review queries waiting on write wait events (for example, free buffer waits or direct path write temp). |
| DB Single Block Disk Read Time | The average number of milliseconds waiting for the db file sequential read event in this database. If this is high, contact your system administrator to understand why disk reads are slow.
Use DPA to drill in to the db file sequential read waits and use the Files tab to show the disks involved. |
Exadata
| Metric | Description |
|---|---|
| Cell Multiblock Physical Read Latency | The average number of milliseconds waiting for the cell multiblock physical read Exadata event in this database. |
| Cell Single Block Physical Read Latency | The average number of milliseconds waiting for the cell single block physical read Exadata event in this database. |
| Cell Smart Table Scan Latency | The average number of milliseconds waiting for the cell smart table scan Exadata event in this database. |
| Flash Cache Hit Ratio | The amount of I/O operations satisfied by the Exadata Smart Flash Cache within the Storage Servers. Exadata Smart Flash Cache is one of the essential technologies of the Oracle Exadata Database Machine that enables the processing of up to 1.5 million random I/O operations per second (IOPS), and the scanning of data within Exadata storage at up to 75 GB/second. This metric helps you understand how much the cache is helping. |
| IO Saved by Storage Cell Offloading | The amount of physical I/O that has been saved by offloading it to the Exadata storage servers. Each of the storage servers might get a piece of the SQL statement to operate on, so the processing is also parallelized at the same time. This saves valuable database server processing cycles for other non-I/O related activities and can dramatically reduce response times. Smart Scan is another term that essentially means the same thing. |
| Smart Scan Efficiency | When the storage cells process full table scans they can apply columns filters and perform column projection so that not all blocks are returned to the database server, only the ones that are needed. This metric shows an efficiency of how well that is occurring. The data comes from v$sysstat. It looks at the 'cell IO uncompressed bytes' (a), 'cell physical IO bytes saved by storage index' (b) and 'cell physical IO interconnect bytes returned by smart scan' (c) metrics. It then applies the formula of 100 * (a + b) / c to get the percentage of data saved by the smart scans. |
Memory
| Metric | Description |
|---|---|
| Buffer Cache Hit Ratio | The rate at which this database finds the data blocks it needs in memory rather than having to read from disk. By itself, the buffer cache hit ratio is not very meaningful except for databases with undersized data buffer cache (db_cache_size parameter). Oracle provides the data buffer cache advisory utility v$db_cache_advice for assistance with sizing. |
| Buffer Cache Size | The amount of memory allocated to all Oracle buffer caches. |
| DB Logical Read Rate | The number of memory reads (session logical reads statistic from v$sysstat) per second for this database. |
| Library Cache Hit Ratio | The library cache (a component of the shared pool) stores the executable (parsed or compiled) form of recently referenced SQL and PL/SQL code. Oracle tries to reuse this code. If the code has been executed previously and can be shared, Oracle will report a library cache hit. If Oracle is unable to use existing code, then a new executable version of the code must be built, which is known as a library cache miss. |
| PGA Cache Size | The amount of memory allocated to the PGA cache. |
| Shared Pool Size | The amount of memory allocated to the Oracle shared pool. |
Network
| Metric | Description |
|---|---|
| DB Round-trip Time | The round-trip time when running "select 1 from dual" (includes network time but not connect time) on this database. If this is high, contact your network administrator to understand network latency. |
| SQL*Net Received Rate | The throughput of SQL*Net bytes received from the clients in KB/second. |
| SQL*Net Sent Rate | The throughput of SQL*Net bytes sent to the clients in KB/second. |
RAC
| Metric | Description |
|---|---|
| Avg Current Block Flush Time |
The average current block flush times being experienced from this instance across the RAC Interconnect. This value is calculated as follows:
|
| Avg Current Block Pin Time |
The average current block pin times being experienced from this instance across the RAC Interconnect. This value is calculated as follows:
|
| Avg Current Block Send Time |
The average current block send times being experienced from this instance across the RAC Interconnect. This value is calculated as follows:
|
| Avg GC CR Block Build Time |
The average global cache CR block build times being experienced from this instance across the RAC Interconnect. The average time to build a consistent read block is calculated as follows:
|
| Avg GC CR Block Flush Time |
The average global cache CR block flush times being experienced from this instance across the RAC Interconnect. The average time spent waiting for a redo log flush is calculated as follows:
|
| Avg GC CR Block Receive Time |
The average round-trip time or latency for all requests for a Consistent Read (CR) from this instance across the RAC Interconnect. If the transfer time is too high, or if one of the nodes in the cluster shows excessive transfer times, the RAC interconnect should be checked (using system level commands) to verify that it is functioning correctly. Calculation in (ms) is as follows:
|
| Avg GC CR Block Send Time |
The average global cache CR block send times being experienced from this instance across the RAC Interconnect. The average time to send a complete consistent read block is calculated as follows:
|
| Avg GC Current Block Receive Time |
The average round-trip time or latency for all processing requests for Current Mode Block from this instance across the RAC Interconnect. Calculation in (ms) is as follows:
|
| Current Block Service Time |
The average Current Block Service Time (ms) is calculated as follows:
|
| LMS Service Time | The average LMS Service Time measures overall latency for a Consistent Read. This includes queue, build, flush, and send time. The Lock Manager Server (LMS) process, also called the GCS (Global Cache Services) process, is used to transport blocks across the nodes for cache-fusion requests. If there is a Consistent Read request, the LMS process rolls back the block, makes a Consistent Read image of the block, and then ships this block across the HSI (High Speed Interconnect) to the process requesting from a remote node. LMS must also check constantly with the LMD background process (or GES process) to get the lock requests placed by the LMD process. |
Sessions
| Metric | Description |
|---|---|
| DB Active Sessions | The number of sessions actively performing work or waiting for a resource (excludes idle sessions) for this database. |
| DB Blocked Sessions | The number of sessions that are blocked because another session is using a needed resource on this database. |
| DB Transaction Rate | The number of Transactions (user commits + user rollbacks statistics from v$sysstat) being executed every second for this database. |
Waits
| Metric | Description |
|---|---|
| Total Instance Wait Time | The total wait time for the database. |