db file async I/O submit 等待事件說明
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/20674423/viewspace-1445395/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- db file async I/O submit 等待事件優化MIT事件優化
- db file async I/O submit等待事件的故障診斷MIT事件
- 等待事件:Disk file operations I/O事件
- Oracle db file parallel write 和 log file parallel write 等待事件 說明OracleParallel事件
- 【WAIT】 log file sync等待事件說明AI事件
- 【等待事件】等待事件系列(1)--User I/O型別事件型別
- db file scattered read等待事件事件
- db file sequential read等待事件事件
- 【等待事件】db file sequential read事件
- 【等待事件】db file scattered read事件
- 等待事件--db file scattered reads事件
- 等待事件--db file sequential reads事件
- latch free 等待事件說明事件
- I/O上的等待事件 —— control file sequential read/control file parallel write事件Parallel
- 詳解 db file sequential read 等待事件事件
- 【TUNE_ORACLE】等待事件之IO等待“db file parallel write”Oracle事件Parallel
- Oracle常見等待事件說明Oracle事件
- latch free 等待事件說明(轉)事件
- 【TUNE_ORACLE】等待事件之IO等待“db file sequential read”Oracle事件
- 【TUNE_ORACLE】等待事件之IO等待“db file scattered read”Oracle事件
- oracle常見的等待事件說明Oracle事件
- buffer busy wait 等待事件說明AI事件
- 0322理解db file parallel read等待事件2Parallel事件
- 0316理解db file parallel read等待事件Parallel事件
- 非空閒的等待事件-db file scattered read事件
- buffer busy wait 等待事件說明(轉)AI事件
- 等待事件db file sequential read、db file scattered read和direct read的區別事件
- oracle之 db file sequential read等待事件優化思想Oracle事件優化
- 非空閒等待事件之:db file scattered read(轉)事件
- 何時會發生db file sequential read等待事件?事件
- db file sequential read wait event等待事件之二AI事件
- Java非阻塞I/O模型之NIO說明Java模型
- [異常等待事件rdbms ipc reply]-分析說明事件
- log file sync等待事件事件
- 【等待事件】log file sync事件
- LOG FILE SWITCH等待事件事件
- log file sync" 等待事件事件
- oracle等待事件型別wait_class說明Oracle事件型別AI