Thank you for visiting Spatial DBA - Oracle and ArcSDE.
Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
===============================================================
V$BACKUP_ASYNC_IO displays performance information about ongoing and recently completed RMAN backups and restores. For each backup, it contains one row for each input datafile, one row for the aggregate total performance of all datafiles, and one row for the output backup piece. This data is not stored persistently, and is not preserved when the instance is re-started.
Column | Datatype | Description |
SID | NUMBER | Oracle SID of the session doing the backup or restore |
SERIAL | NUMBER | Use count for the SID doing the backup or restore |
USE_COUNT | NUMBER | A counter that can be used to identify rows from different backup sets |
RMAN_STATUS_RECID | NUMBER | Owning V$RMAN_STATUS record ID |
RMAN_STATUS_STAMP | NUMBER | Owning V$RMAN_STATUS record stamp |
DEVICE_TYPE | VARCHAR2(17) | Device type where the file is located |
TYPE | VARCHAR2(9) | INPUT, OUTPUT, or AGGREGATE |
STATUS | VARCHAR2(11) | NOT STARTED, IN PROGRESS, or FINISHED |
FILENAME | VARCHAR2(513) | Name of the backup file being read or written |
SET_COUNT | NUMBER | Set count of the backup set being read or written |
SET_STAMP | NUMBER | Set stamp of the backup set being read or written |
BUFFER_SIZE | NUMBER | Size of the buffers being used to read/write the file, in bytes |
BUFFER_COUNT | NUMBER | Number of buffers being used to read/write the file |
TOTAL_BYTES | NUMBER | Total number of bytes that will be read or written for the file, if known. If not known, this column will be null |
OPEN_TIME | DATE | Time the file was opened. If TYPE='AGGREGATE', then this is the time that the first file in the aggregate was opened |
CLOSE_TIME | DATE | Time the file was closed. If TYPE='AGGREGATE', then this is the time that the last file in the aggregate was closed |
ELAPSED_TIME | NUMBER | Time, in hundredths of a second, that the file was open |
MAXOPENFILES | NUMBER | Number of concurrently open DISK files. This value is only present in rows where TYPE='AGGREGATE'. |
BYTES | NUMBER | Number of bytes read or written so far |
EFFECTIVE_BYTES_PER_SECOND | NUMBER | I/O rate that was achieved with this device during this backup |
IO_COUNT | NUMBER | Number of I/Os that were performed to this file |
READY | NUMBER | Number of asynchronous requests for which a buffer was immediately ready for use |
SHORT_WAITS | NUMBER | Number of times that a buffer was not immediately available, but a buffer became available after doing a nonblocking poll for I/O completion |
SHORT_WAIT_TIME_TOTAL | NUMBER | Total time, in hundredths of a second, taken by nonblocking polls for I/O completion |
SHORT_WAIT_TIME_MAX | NUMBER | Maximum time taken for a nonblocking poll for I/O completion, in hundredths of a second |
LONG_WAITS | NUMBER | The number of times that a buffer was not immediately available, and only became available after a blocking wait was issued |
LONG_WAIT_TIME_TOTAL | NUMBER | The total time, in hundredths of a second, taken by blocking waits for I/O completion |
LONG_WAIT_TIME_MAX | NUMBER | The maximum time taken for a blocking wait for I/O completion, in hundredths of a second |
Note:
1. V$BACKUP_ASYNC_IO and V$BACKUP_SYNC_IO can be used to determine the source of backup or restore bottlenecks and to see detailed progress of backup jobs. V$BACKUP_SYNC_IO contains rows when the I/O is synchronous to the process / thread performing the backup. V$BACKUP_ASYNC_IO contains rows when the I/O is asynchronous.
2. EFFECTIVE_BYTES_PER_SECOND column in V$BACKUP_ASYNC_IO and V$BACKUP_SYNC_IO can be used to determine whether the tape is streaming when the I/O is synchronous. If EFFECTIVE_BYTES_PER_SECOND is less than the raw capacity of the hardware, then the tape is not streaming. If EFFECTIVE_BYTES_PER_SECOND is greater than the raw capacity of the hardware, the tape may or may not be streaming. Compression may cause the EFFECTIVE_BYTES_PER_SECOND to be greater than the speed of real I/O.
3. To identify bottlenecks with asynchronous I/O: LONG_WAITS column is the number of times the backup or restore process told the operating system to wait until an I/O was complete. SHORT_WAITS column is the number of times the backup or restore process made an operating system call to poll for I/O completion in a nonblocking mode. READY column indicates the number of time when I/O was already ready for use and so there was no need to made an operating system call to poll for I/O completion. The simplest way to identify bottleneck is to query V$BACKUP_ASYNC_IO for the datafile that has the largest ratio for LONG_WAITS/IO_COUNT.
4. If you have synchronous I/O but you have set BACKUP_DISK_IO_SLAVES, then I/O will be displayed in V$BACKUP_ASYNC_IO.
Oracle data dictionary views
Last updated: 2009-12-09 Wednesday |
