6.3.7.3 Monitoring Smart I/O Using SQL Monitor

In addition to dynamic performance views and wait events, Oracle Database provides SQL monitor, which enables you to monitor the execution of individual SQL statements.

The SQL monitor report includes detailed statistics for the row sources, which includes additional information for smart I/O operations. The row source statistics can be viewed in the Enterprise Manager active SQL monitor report by clicking on the binoculars in the row source. For example:

Figure 6-24 SQL Monitor Report: Smart I/O Row Source Statistics

The image shows an example of smart I/O row source statistics provided by SQL monitor.

The following table describes various row source statistics provided by SQL monitor, which are useful for monitoring smart I/O.

Statistic Description
Filtered bytes The number of bytes returned by the cell.
Cell passthru IO bytes The number of bytes that are not offloaded and sent back to the database for processing.
Cell passthru IO bytes due to quarantine The number of bytes that are not offloaded and sent back to the database for processing due to a quarantine on the cell.
Eligible bytes for smart IO

The number of actual bytes eligible for predicate offload.

For example, when using columnar cache, this is the size of columnar cache instead of the on-disk size.
SI saved bytes The number of bytes saved by storage index; that is, the number of bytes that did not have to be read.
Columnar cache saved bytes The number of bytes saved by columnar cache; that is, the number of bytes that did not have to be read.
Partial flash cache and disk bytes The number of bytes that read from both Exadata Smart Flash Cache and disk.
Flash cache bytes The number of bytes read from Exadata Smart Flash Cache.
IM Capacity bytes The number of bytes read from the columnar cache in memcompress for capacity format.
IM Query bytes The number of bytes read from the columnar cache in memcompress for query format.
No memcompress bytes The number of bytes read from the columnar cache in no memcompress format.
XRMEM Columnar Cache bytes The number of bytes read from the columnar cache on Exadata RDMA Memory (XRMEM).
Bytes added to storage index The number of bytes added to the storage index during a Smart Scan. This is an indication that the storage index is being built.
cell IORM IO requests on flash The number of physical I/O requests to flash storage.
cell IORM wait time on flash (us)

The amount of time (in microseconds) IORM queued the flash request.

The cell IORM wait time on flash (us) / cell IORM IO requests on flash gives an indication of how much time, on average, is spent in the IORM queue.

cell IORM IO requests on disk The number of physical I/O requests to disk storage.
cell IORM wait time on disk (us)

The amount of time (in microseconds) IORM queued the disk request.

The cell IORM wait time on disk (us) / cell IORM IO requests on disk gives an indication of how much time, on average, is spent in the IORM queue.

Block IO bytes The number of bytes in block I/O mode.

Slow metadata bytes

Metadata bytes

 The size of the query metadata sent from the database compute node to a cell.
Eligible bytes The number of bytes on-disk eligible for predicate offload.

The availability of a specific statistic is subject to the version of Oracle Database being used.

Related Topics

Parent topic: Monitoring Smart I/O