Databases Reference
In-Depth Information
Solution
Query V$BACKUP_ASYNC_IO and V$BACKUP_SYNC_IO to determine I/O bottlenecks. Ideally, the EFFECTIVE_
BYTES_PER_SECOND column should return a rate that is close to the capacity of the backup device. The following query
returns statistics for asynchronous I/O for backup and restore operations that have occurred within the past seven days:
SELECT sid, serial, filename, type, elapsed_time,
effective_bytes_per_second
FROM v$backup_async_io
WHERE close_time > sysdate - 7;
If you have identified your SID and SERIAL number (see Recipe 16-1), you can specifically query for records
associated with your current session:
SELECT filename, sid, serial, close_time, long_waits/io_count as ratio
FROM v$backup_async_io
WHERE type != 'AGGREGATE'
AND SID = &SID
AND SERIAL = &SERIAL
ORDER BY ratio desc;
If you are using tape drives, query the EFFECTIVE_BYTES_PER_SECOND column of V$BACKUP_SYNC_IO. If
the effective rate is less than the tape device's maximum throughput, this may indicate that your tape device is not
streaming (continuously writing).
For tape devices, you can also identify bottlenecks by using the
backup validate
command. You can compare
the time it takes for a regular backup job to tape versus just a
backup validate
command. A
backup validate
command performs the same reads as a regular backup but does not write to tape. If the time to perform a
backup
validate
is significantly less than a regular backup job to tape, then writing to tape is most likely the bottleneck.
How It Works
Most operating systems now support asynchronous I/O. When backing up to disk, asynchronous I/O is advantageous
because a server process can perform more than one I/O operation at a time. Contrast that with synchronous I/O,
where the server process has to wait for each I/O operation to complete before starting the next I/O operation.
The initialization parameter
disk_asynch_io
controls Oracle's asynchronous behavior. If your operating system
supports asynchronous I/O, then Oracle recommends that you leave this parameter set to its default value of TRUE.
Oracle will take advantage of asynchronous I/O if it's available. If asynchronous I/O is not available with your operating
system, you can tune I/O performance by setting the
dbwr_io_slaves
initialization parameter to a nonzero value.
Not
■
enabling multiple i/O slaves will increase the number of processes that your database uses. You will need to
adjust the processes initialization parameter accordingly.
You can use two views to monitor asynchronous and synchronous I/O. As its name implies, V$BACKUP_ASYNC_IO
contains information for asynchronous backup or restore operations. Likewise, V$BACKUP_SYNC_IO contains information
for synchronous operations. For each backup or restore operation, you will see a row in the view for the following events:
•
Each data file read or written
•
Each backup piece read or written
•
An aggregate record for overall performance of files read or written during an operation
