6.3.9.2 Monitoring Cell Disk I/O Using Database Statistics and Wait Events

The following table describes various database statistics that are useful for monitoring cell disk I/O. The statistics are available in various dynamic performance views, including V$SYSSTAT, and may be displayed in the Global Activity Statistics or Instance Activity Statistics section of an AWR report.

Statistic	Description
physical read IO requests	Number of physical read requests issued by the database.
physical read requests optimized	Total number of read requests satisfied by using Exadata Smart Flash Cache, and read requests avoided by using storage index or columnar cache.
physical read total bytes	Total amount of I/O bytes for reads issued by the database, whether or not the read was offloaded to the storage servers.
physical read total bytes optimized	Total number of bytes read from Exadata Smart Flash Cache, and bytes avoided by using storage index or columnar cache.
physical read total IO requests	Total number of read requests issued by the database for all activity including application, backup, recovery, and other utilities.
physical write IO requests	Number of physical write requests issued by the database.
physical write total bytes	Total number of IO bytes for writes issued by the database for all activity.
physical write total bytes optimized	Total number of bytes written to Exadata Smart Flash Cache. These bytes are synchronized to disk in a lazy manner.
physical write total IO requests	Total number of write requests issued by the database for all activity, including application, backup, recovery and other utilities.

Additional database I/O statistics are also contained in the following V$IOSTAT views, which are also used in the IOStat sections of the AWR report.

• V$IOSTAT_FILE — displays information about disk I/O statistics for database files, including data files, temp files, and other types of database files.
• V$IOSTAT_FUNCTION — displays disk I/O statistics for database functions, such as the LGWR and DBWR.
• V$IOSTAT_FUNCTION_DETAIL — displays disk I/O statistics for database functions, broken down by file type.

The following table describes database wait events that are useful for monitoring cell disk I/O. The wait events are visible in various dynamic performance views, including V$SESSION, V$SYSTEM_EVENT and V$SESSION_EVENT, and may be displayed in the Wait Event sections of the AWR report.

Wait Event	Description
cell list of blocks physical read	This wait event occurs during recovery or during buffer pre-fetching (rather than performing multiple single-block reads). It is used to monitor database blocks that must be changed as part of recovery and are read in parallel for the database. In V$SESSION, records associated with this event include additional parameters: P1 contains the relevant storage server hash number, which corresponds to V$CELL.CELL_HASHVAL. P2 identifies the disk hash number for the grid disk that contains the data, which corresponds to V$ASM_DISK.HASH_VALUE. P3 specifies the number of bytes processed during the I/O read operation. This wait event is equivalent to db file parallel read for a cell.
cell list of blocks read request	This is a placeholder wait event associated with cell list of blocks physical read, which is visible only during the wait period. After the wait event ends, the placeholder is typically converted to cell list of blocks physical read.
cell multiblock physical read	This wait event represents the time taken to perform all the I/Os for a multi-block database read. In V$SESSION, records associated with this event include additional parameters: P1 contains the relevant storage server hash number, which corresponds to V$CELL.CELL_HASHVAL. P2 identifies the disk hash number for the grid disk that contains the data, which corresponds to V$ASM_DISK.HASH_VALUE. P3 specifies the number of bytes processed during the I/O read operation. This wait event is equivalent to db file scattered read for a cell.
cell multiblock read request	This is a placeholder wait event associated with cell multiblock physical read, which is visible only during the wait period. After the wait event ends, the placeholder is typically converted to cell multiblock physical read.
cell single block physical read	This wait event represents the time taken to perform a single block database I/O. In V$SESSION, records associated with this event include additional parameters: P1 contains the relevant storage server hash number, which corresponds to V$CELL.CELL_HASHVAL. P2 identifies the disk hash number for the grid disk that contains the data, which corresponds to V$ASM_DISK.HASH_VALUE. P3 specifies the number of bytes processed during the I/O read operation. This wait event is equivalent to db file sequential read for a cell. Commencing with the May 2022 Oracle Database release updates (versions 19.15.0.0.220419, 21.6.0.0.220419, and later), this wait event no longer includes I/O from Exadata Smart Flash Cache or database I/O using a Remote Direct Memory Access (RDMA) read.
cell single block physical read: flash cache	This wait event represents the time taken to perform a single block database I/O from Exadata Smart Flash Cache. In V$SESSION, records associated with this event include additional parameters: P1 contains the relevant storage server hash number, which corresponds to V$CELL.CELL_HASHVAL. P2 identifies the disk hash number for the grid disk that contains the data (not the cache location), which corresponds to V$ASM_DISK.HASH_VALUE. P3 specifies the number of bytes processed during the I/O read operation. This wait event was introduced in the May 2022 Oracle Database release updates and is present in Oracle Database versions 19.15.0.0.220419, 21.6.0.0.220419, and later. Previously, cell single block physical read included these waits.
cell single block physical read: xrmem cache cell single block physical read: pmem cache	This wait event represents the time taken to perform a single block database I/O from XRMEM cache. In V$SESSION, records associated with this event include additional parameters: P1 contains the relevant storage server hash number, which corresponds to V$CELL.CELL_HASHVAL. P2 identifies the disk hash number for the grid disk that contains the data (not the cache location), which corresponds to V$ASM_DISK.HASH_VALUE. P3 specifies the number of bytes processed during the I/O read operation. cell single block physical read: pmem cache was introduced in the May 2022 Oracle Database release updates and is present in Oracle Database versions 19.15.0.0.220419, 21.6.0.0.220419, and later. Previously, cell single block physical read included these waits. The wait event is named cell single block physical read: xrmem cache in Oracle Database versions that support Oracle Exadata System Software release 23.1.0 and later.
cell single block physical read: RDMA	This wait event represents the time taken to perform a single block database I/O using a Remote Direct Memory Access (RDMA) read. In V$SESSION, records associated with this event include additional parameters: P1 contains the relevant storage server hash number, which corresponds to V$CELL.CELL_HASHVAL. P2 identifies the disk hash number for the grid disk that contains the data, which corresponds to V$ASM_DISK.HASH_VALUE. P3 specifies the number of bytes processed during the I/O read operation. This wait event was introduced in the May 2022 Oracle Database release updates and is present in Oracle Database versions 19.15.0.0.220419, 21.6.0.0.220419, and later. Previously, cell single block physical read included these waits.
cell single block read request	This is a placeholder wait event associated with a single block database I/O that is visible only during the wait period. After the wait event ends, the placeholder is converted to the appropriate wait event, which is typically one of the cell single block physical read events.
cell interconnect retransmit during physical read	This wait event appears during retransmission for an I/O of a single-block or multiblock read. The cell hash number in the P1 column in the V$SESSION_WAIT view is the same cell identified for cell single block physical read and cell multiblock physical read. The P2 column contains the subnet number to the cell, and the P3 column contains the number of bytes processed during the I/O read operation.

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