RMAN - 发现I/O瓶颈
大多数操作系统支持异步I/O。
备份到磁盘，异步I/O是一个优势，因为一个服务器进程同时可以执行多个I/O操作；同步I/O必须等上一个I/O操作完成才可以执行下一个I/O操作。
初始化参数disk_asynch_io控制oracle异步I/O操作行为。如果操作系统支持异步I/O，oracle建议设置该参数为true。
如果操作系统不支持异步I/O,可以设置dbwr_io_slaves参数为非零值来调优I/O性能。（如果设置为非零值，会增加数据库进程的数量，需要增加processes的值）
如果使用的是磁带备份，查看视图v$backup_sync_io中的列effective_bytes_per_second。如果该值比磁带设备的最大吞吐小，表明磁带设备不是streaming（连续写） 。
此外，对于磁带设备，可以比较backup validate命令和实际备份任务的时间，如果时间明显比实际备份任务时间小，表明磁带的写能力很可能是瓶颈。
可以通过以下两个试图,监控异步I/O和同步I/O:
·v$backup_async_io：查看备份或者还原操作的异步I/O
·v$backup_symc_io：查看备份或者还原的同步I/O性能。
在这两个试图中，effective_bytes_per_second列的含义：Read/write rate for device during backup or restore.
备份或还原操作中，异步I/O的监控信息：
SQL> SELECT sid, serial, filename, type, elapsed_time, effective_bytes_per_second
2 FROM v$backup_async_io
3 WHERE close_time > sysdate-7;
SID SERIAL FILENAME TYPE ELAPSED_TIME EFFECTIVE_BYTES_PER_SECOND
---------- ---------- ------------------------------------------------------------ --------- ------------ --------------------------
53 34 AGGREGATE 7500 19028924
53 34 /u11/app/oracle/oradata/ora11/system01.dbf INPUT 7500 9521179
53 34 /u11/app/oracle/oradata/ora11/sysaux01.dbf INPUT 6800 7933832
53 34 /u11/app/oracle/oradata/ora11/example01.dbf INPUT 1600 5243392
53 34 /u11/app/oracle/oradata/ora11/UNDOTBS02.dbf INPUT 1500 4823996
53 34 /u11/app/oracle/oradata/ora11/yb01.dbf INPUT 400 2754560
53 34 /u11/app/oracle/oradata/ora11/users01.dbf INPUT 200 2363392
53 34 /u11/app/oracle/oradata/ora11/yb02.dbf INPUT 0
53 34 /u11/app/oracle/fra/ORA11/backupset/2015_07_01/o1_mf_nnndf_T OUTPUT 7500 15325157
AG20150701T122458_bs6twvbn_.bkp
53 34 AGGREGATE 0
53 34 /u11/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ora11.f INPUT 0
53 34 /u11/app/oracle/fra/ORA11/autobackup/2015_07_01/o1_mf_s_8839 OUTPUT 0
16783_bs6tzjr5_.bkp
12 rows selected.
SQL> SELECT filename, sid, serial, close_time, long_waits/io_count as ratio
2 FROM v$backup_async_io
3 WHERE type != 'AGGREGATE'
4 AND SID = &SID
5 AND SERIAL = &SERIAL
6 ORDER BY ratio desc;
Enter value for sid: 53
old 4: AND SID = &SID
new 4: AND SID = 53
Enter value for serial: 34
old 5: AND SERIAL = &SERIAL
new 5: AND SERIAL = 34
FILENAME SID SERIAL CLOSE_TIM RATIO
------------------------------------------------------------ ---------- ---------- --------- ----------
/u11/app/oracle/fra/ORA11/backupset/2015_07_01/o1_mf_nnndf_T 53 34 01-JUL-15 .249772106
AG20150701T122458_bs6twvbn_.bkp
/u11/app/oracle/fra/ORA11/autobackup/2015_07_01/o1_mf_s_8839 53 34 01-JUL-15 .2
16783_bs6tzjr5_.bkp
/u11/app/oracle/oradata/ora11/example01.dbf 53 34 01-JUL-15 0
/u11/app/oracle/oradata/ora11/UNDOTBS02.dbf 53 34 01-JUL-15 0
/u11/app/oracle/oradata/ora11/users01.dbf 53 34 01-JUL-15 0
/u11/app/oracle/oradata/ora11/yb02.dbf 53 34 01-JUL-15 0
/u11/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ora11.f 53 34 01-JUL-15 0
/u11/app/oracle/oradata/ora11/sysaux01.dbf 53 34 01-JUL-15 0
/u11/app/oracle/oradata/ora11/system01.dbf 53 34 01-JUL-15 0
/u11/app/oracle/oradata/ora11/yb01.dbf 53 34 01-JUL-15 0
10 rows selected.
SQL>
