db file async I/O submit 等待事件说明
在做11G升级的时候,监控系统性能发现如下情况:
SQL> select * from
(select EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,AVERAGE_WAIT,rownum rn
from v$session_event where WAIT_CLASS<>'Idle' order by TIME_WAITED desc
)
where rn<10;
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT RN
------------------------------ ----------- -------------- ----------- ------------ ----------
db file async I/O submit 3437 0 11506 3.35 7
Disk file operations I/O 141 0 71 .5 2
os thread startup 1 0 14 13.65 1
events in waitclass Other 51 0 10 .19 9
control file sequential read 549 0 7 .01 3
latch: shared pool 4 0 2 .53 8
db file sequential read 12 0 0 0 6
latch: redo writing 1 0 0 .03 5
latch: cache buffers chains 3 0 0 .01 4
发现很高的 db file async I/O submit 等待事件。
从等待事件的名字可以看出是 异步IO相关。查看系统异步IO相关的参数及其设置。
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string none
dbwr_io_slaves integer 0
disk_asynch_io boolean TRUE
db_writer_processes integer 1
SQL>
关于以上几个参数的说明:
1, FILESYSTEMIO_OPTIONS
You can use the FILESYSTEMIO_OPTIONS initialization parameter to enable or disable asynchronous I/O or direct I/O on file system files. This parameter is platform-specific and has a default value that is best for a particular platform.
FILESYTEMIO_OPTIONS can be set to one of the following values:
ASYNCH: enable asynchronous I/O on file system files, which has no timing requirement for transmission.
DIRECTIO: enable direct I/O on file system files, which bypasses the buffer cache.
SETALL: enable both asynchronous and direct I/O on file system files.
NONE: disable both asynchronous and direct I/O on file system files.
2,DBWR_IO_SLAVES
Property Description
Parameter type Integer
Default value 0
Modifiable No
Range of values 0 to operating system-dependent
Basic No
DBWR_IO_SLAVES is relevant only on systems with only one database writer process (DBW0). It specifies the number of I/O server processes used by the DBW0 process. The DBW0 process and its server processes always write to disk. By default, the value is 0 and I/O server processes are not used.
If you set DBWR_IO_SLAVES to a nonzero value, the number of I/O server processes used by the ARCH and LGWR processes is set to 4. However, the number of I/O server processes used by Recovery Manager is set to 4 only if asynchronous I/O is disabled (either your platform does not support asynchronous I/O or disk_asynch_io is set to false).
Typically, I/O server processes are used to simulate asynchronous I/O on platforms that do not support asynchronous I/O or that implement it inefficiently. However, you can use I/O server processes even when asynchronous I/O is being used. In that case the I/O server processes will use asynchronous I/O.
I/O server processes are also useful in database environments with very large I/O throughput, even if asynchronous I/O is enabled.
DBWR_IO_SLAVES
If it is not practical to use multiple DBWR processes, then Oracle Database provides a facility whereby the I/O load can be distributed over multiple slave processes. The DBWR process is the only process that scans the buffer cache LRU list for blocks to be written out. However, the I/O for those blocks is performed by the I/O slaves. The number of I/O slaves is determined by the parameter DBWR_IO_SLAVES.
DBWR_IO_SLAVES is intended for scenarios where you cannot use multiple DB_WRITER_PROCESSES (for example, where you have a single CPU). I/O slaves are also useful when asynchronous I/O is not available, because the multiple I/O slaves simulate nonblocking, asynchronous requests by freeing DBWR to continue identifying blocks in the cache to be written. Asynchronous I/O at the operating system level, if you have it, is generally preferred.
DBWR I/O slaves are allocated immediately following database open when the first I/O request is made. The DBWR continues to perform all of the DBWR-related work, apart from performing I/O. I/O slaves simply perform the I/O on behalf of DBWR. The writing of the batch is parallelized between the I/O slaves.
Note:
Implementing DBWR_IO_SLAVES requires that extra shared memory be allocated for I/O buffers and request queues. Multiple DBWR processes cannot be used with I/O slaves. Configuring I/O slaves forces only one DBWR process to start.
3,DB_WRITER_PROCESSES
Property Description
Parameter type Integer
Default value 1 or CPU_COUNT / 8, whichever is greater. If the number of processor groups is less than 36 but greater than the number of DB writer processes, then the number of DB writer processes is adjusted to be a multiple of the number of processor groups. If the number of DB writer processes is greater than or equal to the number of processor groups, then there is no adjustment.
Modifiable No
Range of values 1 to 36
Basic No
DB_WRITER_PROCESSES specifies the initial number of database writer processes (DBW0, ... DBW9 and DBWa, ... DBWz) for an instance. This parameter is useful for systems that modify data heavily.
4,DISK_ASYNCH_IO
Property Description
Parameter type Boolean
Default value true
Modifiable No
Range of values true | false
Basic No
DISK_ASYNCH_IO controls whether I/O to datafiles, control files, and logfiles is asynchronous (that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans). If your platform supports asynchronous I/O to disk, Oracle recommends that you leave this parameter set to its default value. However, if the asynchronous I/O implementation is not stable, you can set this parameter to false to disable asynchronous I/O. If your platform does not support asynchronous I/O to disk, this parameter has no effect.
If you set DISK_ASYNCH_IO to false, then you should also set DBWR_IO_SLAVES to a value other than its default of zero in order to simulate asynchronous I/O.
查看数据文件的异步IO情况
SQL> select name,asynch_io from v$datafile f,v$iostat_file i where f.file#=i.file_no and (filetype_name='Data File' or filetype_name= 'Temp File' );
NAME ASYNCH_IO
-------------------------------------------------- ---------
/u01/oracle/grid/oradata/orcl/system.dbf ASYNC_OFF
/u01/oracle/grid/oradata/orcl/system.dbf ASYNC_OFF
/u01/oracle/grid/oradata/orcl/undotbs1.dbf ASYNC_OFF
/u01/oracle/grid/oradata/orcl/undotbs1.dbf ASYNC_OFF
/u01/oracle/grid/oradata/orcl/sysaux.dbf ASYNC_OFF
/u01/oracle/grid/oradata/orcl/user.dbf ASYNC_OFF
/u01/oracle/grid/oradata/orcl/xhl.dbf ASYNC_OFF
7 rows selected.
SQL>
在MOS上找到相关的说明
'db file async I/O submit' when FILESYSTEMIO_OPTIONS=NONE [ID 1274737.1]
In this Document
Symptoms
Cause
Solution
References
--------------------------------------------------------------------------------
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 11.2.0.2 and later [Release: 11.2 and later ]
Information in this document applies to any platform.
Symptoms
According to the Oracle documentation, the asynch is disabled for normal file systems if FILESYSTEMIO_OPTIONS=NONE.
But, when setting DISK_ASYNCH_IO to TRUE, the wait event list shows 'db file async I/O submit'.
Using for example Note 237299.1 How To Check if Asynchronous I/O is Working On Linux to check if the asynch is enable shows synchronous IO;
the kiocb is 0 running the steps from the note:
cat /proc/slabinfo |grep kio
kioctx 50 84 320 12 1 : tunables 54 27 8 : slabdata 7 7 0
kiocb 0 0 256 15 1 : tunables 120 60 8 : slabdata 0 0 0
A second check is to collect the OS debugger trace on a database writer process:
E.g.
trace –p
If the IO calls are like pwrite64 then this is synchronous IO and if the IO calls are like io_getevents then this is asynch IO.
Cause
This is the expected behavior.
According to unpublished the Bug 9649885 DB FILE ASYNC I/O SUBMIT EVENT NOT TRACKED WHEN DISK_ASYCH_IO = TRUE, when DISK_ASYNCH_IO=TRUE, the wait event 'db file async I/O submit' is posted even if the IO calls cannot be performed asynchronously and this is the current behavior.
The tests show the following behavior.:
disk_asynch_io filesystemio_options strace DBWR AIO DBWR waits
FALSE NONE pwrite64 NO db file parallel write
FALSE ASYNCH pwrite64 NO db file parallel write
TRUE ASYNCH io_submit/ YES db file parallel write
io_getevents
TRUE NONE pwrite64 NO db file async I/O submit
Solution
'db file async I/O submit' should be treated as 'db file parallel write' in previous releases.
Changing the DISK_ASYCH_IO to FALSE removes the wait event 'db file async I/O submit'.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25475140/viewspace-749518/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25475140/viewspace-749518/