Wednesday, December 9, 2009

V$BACKUP_ASYNC_IO

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

No comments:

Post a Comment