DB Options disk_asynch_io is a kind of master switch, which turns on or off Async
I/O to database files on any type of storage, whether it's raw device
or filesystem. The filesystemio_options parameter gives finer control
over I/O to database files on filesystems. It allows you to turn off
async I/O to filesystem files but keep async I/O to raw devices if the
"master" switch disk_asynch_io is set to true.
Instance initialization parameter filesystemio_options has four options:
1. "asynch" : means buffered I/O + Async I/O
2. "directIO" : means Direct I/O only
3. "setall" : means Direct I/O + Async I/O
4. "none" : disables Async I/O and Direct I/O
How to confirm that the database really uses direct IO? http://www.pythian.com/blog/how-to-confirm-direct-io-is-getting-used-on-solaris/
Hi! Recently I was working on a task wherein I had to confirm if the direct IO is in use or not.
filesystem_io_option database parameter was set to "DIRECTIO” to make use of directio.
Now in Linux it becomes very easy.you just need to read /proc/slabinfo :
cat /proc/slabinfo | grep kio
In the SLAB allocator there are three different caches involved. The kioctx and kiocb
are Async I/O data structures that are defined in aio.h header file. If
it shows a non zero value that means async io is enabled.
Initially I was looking for same kind of memory structures in Solaris
but could not find so ( bad luck :( ) and so used basic tracing using
truss.
First I logged into database and check the filesystem parameter ( Changed the name of the database and host :) )
SQL> select name from v$database;
NAME
———
MYDB
SQL> show parameter filesystem
NAME TYPE VALUE
———————————— ———– ——————————
filesystemio_options string DIRECTIO
So the database parameter has been to set to make use of directio.
Now I just need to make sure that this directio really is in use (or
not).
What I have done here is used truss and created a test table to check
if undo and system datafiles are opened with directio or not.( see
below the output )
/homedirs/oracle> truss -f -t open,ioctl -u ‘:directio’ sqlplus "/ as sysdba”
27818: open("/var/ld/64/ld.config”, O_RDONLY) Err#2 ENOENT
27818: open("/ora01/homedirs/oracle/product/10.2.0/db_1/lib/libsqlplus.so”, O_RDONLY) = 3
27818: open("/ora01/homedirs/oracle/product/10.2.0/db_1/lib/libclntsh.so.10.1″, O_RDONLY) = 3
27818: open("/ora01/homedirs/oracle/product/10.2.0/db_1/lib/libnnz10.so”, O_RDONLY) = 3
27818: open("/lib/64/libkstat.so.1″, O_RDONLY) = 3 ... 27818: open("/lib/64/libmd.so.1″, O_RDONLY) = 3
27818/1: open("/ora01/homedirs/oracle/product/10.2.0/db_1/nls/data/lx1boot.nlb”, O_RDONLY) = 3 ... 27818/1: open("/ora01/homedirs/oracle/product/10.2.0/db_1/sqlplus/mesg/cpyus.msb”, O_RDONLY) = 5
27818/1: open("/usr/share/lib/zoneinfo/UTC”, O_RDONLY) = 6
27818/1: open("/ora01/homedirs/oracle/product/10.2.0/db_1/oracore/zoneinfo/timezlrg.dat”, O_RDONLY) = 6
27818/1: open("/ora01/homedirs/oracle/product/10.2.0/db_1/rdbms/mesg/ocius.msb”, O_RDONLY) = 6
27818/1: ioctl(1, TCGETA, 0xFFFFFD7FFFDFF410) = 0
SQL*Plus: Release 10.2.0.5.0 – Production on Wed May 11 12:15:22 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
27818/1: open("/proc/self/psinfo”, O_RDONLY) = 7
27818/1: open("/var/run/name_service_door”, O_RDONLY) = 7
27818/1: ioctl(8, FIONBIO, 0xFFFFFD7FFFDF7F58) = 0
27819: open("/var/ld/64/ld.config”, O_RDONLY) Err#2 ENOENT
27819: open("/ora01/homedirs/oracle/product/10.2.0/db_1/lib/libskgxp10.so”, O_RDONLY) = 3 ... 27819: open("/ora01/homedirs/oracle/product/10.2.0/db_1/lib/libnnz10.so”, O_RDONLY) = 3
27819: open("/lib/64/libkstat.so.1″, O_RDONLY) = 3 ... 27819: open("/lib/64/libmd.so.1″, O_RDONLY) = 3
27819/1: open("/dev/null”, O_RDONLY) = 3
27819/1: open("/proc/27819/psinfo”, O_RDONLY) = 5 ... 27819/1: open("/ora01/homedirs/oracle/product/10.2.0/db_1/nls/data/lx10001.nlb”, O_RDONLY) = 5
27819/1: open("/ora01/homedirs/oracle/product/10.2.0/db_1/nls/data/lx40011.nlb”, O_RDONLY) = 5
27819/1: open("/dev/null”, O_RDONLY) = 5
27819/1: open("/var/run/name_service_door”, O_RDONLY) = 7
27818/1: open("/ora01/homedirs/oracle/product/10.2.0/db_1/nls/data/lx207d0.nlb”, O_RDONLY) = 9
27818/1: ioctl(0, TCGETA, 0xFFFFFD7FFFDFA9B0) = 0
27818/1: open("/etc/ttysrch”, O_RDONLY) = 9
27818/1: ioctl(9, SIOCGIFNUM, 0xFFFFFD7FFFDFA2AC) = 0
27818/1: ioctl(9, SIOCGIFCONF, 0xFFFFFD7FFFDFA258) = 0
27818/1: ioctl(9, SIOCGIFFLAGS, 0x00521E90) = 0
27818/1: ioctl(9, SIOCGIFFLAGS, 0x00521EB0) = 0
27818/1: ioctl(9, SIOCGIFADDR, 0x00521EB0) = 0
27818/1: ioctl(9, SIOCGARP, 0xFFFFFD7FFFDFA26C) = 0
27819/1: open("/dev/zero”, O_RDONLY) = 8
27819/1: ioctl(0, TCGETA, 0xFFFFFD7FFFDF9F20) = 0
27819/1: open("/etc/ttysrch”, O_RDONLY) = 10
27819/1: open("/usr/share/lib/zoneinfo/UTC”, O_RDONLY) = 10
27819/1: open("/ora01/homedirs/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb”, O_RDONLY) = 10
27819/1: open("/etc/netconfig”, O_RDONLY|O_LARGEFILE) = 11
27819/1: open("/dev/udp”, O_RDONLY) = 11
27819/1: ioctl(11, SIOCGLIFNUM, 0xFFFFFD7FFFDFA890) = 0
27819/1: open("/etc/default/nss”, O_RDONLY|O_LARGEFILE) = 11
27819/1: open("/ora01/homedirs/oracle/admin/MYDB/adump/MYDB_ora_27819_1.aud”, O_RDWR|O_CREAT|O_EXCL, 0660) = 11
27818/1: open("/ora01/homedirs/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb”, O_RDONLY) = 12
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
27818/1: open("/ora01/homedirs/oracle/product/10.2.0/db_1/sqlplus/admin/glogin.sql”, O_RDONLY) = 12
SQL> 27818/1: ioctl(0, TCGETA, 0xFFFFFD7FFFDFAC60) = 0
SQL> create table test ( t number);
27819/1: open("/ora02/oradata/MYDB/undotbs101.dbf”, O_RDWR|O_DSYNC) = 13
27819/1@1: -> libc:directio(0×100, 0×1, 0×0, 0×0, 0xfefefefeffffffff, 0xfefefefeff726574)
27819/1: ioctl(256, _ION(‘f’, 76, 0), 0×00000001) = 0
27819/1@1: <- libc:directio() = 0
27819/1: open("/ora02/oradata/MYDB/system01.dbf”, O_RDWR|O_DSYNC) = 13
27819/1@1: -> libc:directio(0×101, 0×1, 0×0, 0×0, 0xfefefefeffffffff, 0xfefefefeff726574)
27819/1: ioctl(257, _ION(‘f’, 76, 0), 0×00000001) = 0
27819/1@1: <- libc:directio() = 0
Table created.
SQL> drop table test;
Table dropped.
See the line "ioctl(256, _ION(‘f’, 76, 0), 0×00000001)” above.
The 3rd parameter as shown in the above output/line to the ioctl() call decides the use of direct IO.
It is 0 for directio off, and 1 for directio on and its ON in case of
this database.i.e undo and system datafiles are opened with directio.
So with this it is confirmed that filesystem_io_option parameter is in effect and the database is making use of directio.
Now lets try this on a database with async io:
QL> select name from v$database;
NAME
———
MYANOTHERDB
SQL> show parameter filesystem
NAME TYPE VALUE
———————————— ———– ——————————
filesystemio_options string asynch
data/home/oracle> truss -f -t open,ioctl -u ‘:directio’ sqlplus "/ as sysdba”
13874: open("/var/ld/64/ld.config”, O_RDONLY) Err#2 ENOENT
13874: open("/global/opt/oracle/product/10.2.0/db_2/lib/libsqlplus.so”, O_RDONLY) = 3
13874: open("/global/opt/oracle/product/10.2.0/db_2/lib/libclntsh.so.10.1″, O_RDONLY) = 3
13874: open("/global/opt/oracle/product/10.2.0/db_2/lib/libnnz10.so”, O_RDONLY) = 3
13874: open("/lib/64/libkstat.so.1″, O_RDONLY) = 3 ...
13874: open("/lib/64/libthread.so.1″, O_RDONLY) = 3
13874: open("/lib/64/libmd.so.1″, O_RDONLY) = 3
13874/1: open("/global/opt/oracle/product/10.2.0/db_2/nls/data/lx1boot.nlb”, O_RDONLY) = 3 ... 13874/1: open("/global/opt/oracle/product/10.2.0/db_2/sqlplus/mesg/cpyus.msb”, O_RDONLY) = 5 13874/1: open("/global/opt/oracle/product/10.2.0/db_2/rdbms/mesg/ocius.msb”, O_RDONLY) = 6
13874/1: ioctl(1, TCGETA, 0xFFFFFD7FFFDFF460) = 0
SQL*Plus: Release 10.2.0.5.0 – Production on Wed May 11 12:15:05 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
13874/1: open("/global/opt/oracle/product/10.2.0/db_2/network/admin/sqlnet.ora”, O_RDONLY) = 7
13874/1: ioctl(7, TCGETA, 0xFFFFFD7FFFDF7DF0) Err#25 ENOTTY
13874/1: open("/global/opt/oracle/product/10.2.0/db_2/network/admin/sqlnet.ora”, O_RDONLY) = 7
13874/1: ioctl(7, TCGETA, 0xFFFFFD7FFFDF7E50) Err#25 ENOTTY
13874/1: open("/proc/self/psinfo”, O_RDONLY) = 7
13874/1: open("/var/run/name_service_door”, O_RDONLY) = 7
13874/1: ioctl(8, FIONBIO, 0xFFFFFD7FFFDF7FA8) = 0
13875: open("/var/ld/64/ld.config”, O_RDONLY) Err#2 ENOENT
13875: open("/global/opt/oracle/product/10.2.0/db_2/lib/libskgxp10.so”, O_RDONLY) = 3
13875: open("/global/opt/oracle/product/10.2.0/db_2/lib/libhasgen10.so”, O_RDONLY) = 3
13875: open("/global/opt/oracle/product/10.2.0/db_2/lib/libskgxn2.so”, O_RDONLY) = 3 ...
13875: open("/global/opt/oracle/product/10.2.0/db_2/lib/libnnz10.so”, O_RDONLY) = 3
13875: open("/lib/64/libkstat.so.1″, O_RDONLY) = 3
13875: open("/lib/64/libnsl.so.1″, O_RDONLY) = 3 ...
13875: open("/usr/lib/64/libsched.so.1″, O_RDONLY) = 3
13875: open("/lib/64/libm.so.2″, O_RDONLY) = 3
13875: open("/lib/64/libpthread.so.1″, O_RDONLY) = 3
13875: open("/lib/64/libmd.so.1″, O_RDONLY) = 3
13875/1: open("/dev/null”, O_RDONLY) = 3
13875/1: open("/proc/13875/psinfo”, O_RDONLY) = 5
13875/1: open("/global/opt/oracle/product/10.2.0/db_2/nls/data/lx1boot.nlb”, O_RDONLY) = 5 ... 13875/1: open("/global/opt/oracle/product/10.2.0/db_2/network/admin/sqlnet.ora”, O_RDONLY) = 5
13875/1: ioctl(5, TCGETA, 0xFFFFFD7FFFDFE100) Err#25 ENOTTY
13875/1: open("/global/opt/oracle/product/10.2.0/db_2/network/admin/sqlnet.ora”, O_RDONLY) = 5
13875/1: ioctl(5, TCGETA, 0xFFFFFD7FFFDFE160) Err#25 ENOTTY
13875/1: open("/dev/null”, O_RDONLY) = 5
13875/1: open("/var/run/name_service_door”, O_RDONLY) = 7
13874/1: open("/global/opt/oracle/product/10.2.0/db_2/nls/data/lx2001f.nlb”, O_RDONLY) = 9
13874/1: open("/global/opt/oracle/product/10.2.0/db_2/nls/data/lx207d0.nlb”, O_RDONLY) = 9
13874/1: ioctl(0, TCGETA, 0xFFFFFD7FFFDFAA00) = 0
13874/1: open("/etc/ttysrch”, O_RDONLY) = 9
13874/1: ioctl(9, SIOCGIFNUM, 0xFFFFFD7FFFDFA2FC) = 0
13874/1: ioctl(9, SIOCGIFCONF, 0xFFFFFD7FFFDFA2A8) = 0
13874/1: ioctl(9, SIOCGIFFLAGS, 0×00411840) = 0
13874/1: ioctl(9, SIOCGIFFLAGS, 0×00411860) = 0
13874/1: ioctl(9, SIOCGIFADDR, 0×00411860) = 0
13874/1: ioctl(9, SIOCGARP, 0xFFFFFD7FFFDFA2BC) = 0
13875/1: open("/dev/zero”, O_RDONLY) = 8
13875/1: ioctl(0, TCGETA, 0xFFFFFD7FFFDF9F60) = 0
13875/1: open("/etc/ttysrch”, O_RDONLY) = 10
13875/1: open("/usr/share/lib/zoneinfo/UTC”, O_RDONLY) = 10
13875/1: open("/global/opt/oracle/product/10.2.0/db_2/rdbms/mesg/oraus.msb”, O_RDONLY) = 10
13875/1: open("/etc/netconfig”, O_RDONLY|O_LARGEFILE) = 11
13875/1: open("/dev/udp”, O_RDONLY) = 11
13875/1: ioctl(11, SIOCGLIFNUM, 0xFFFFFD7FFFDFA8D0) = 0
13875/1: open("/etc/default/nss”, O_RDONLY|O_LARGEFILE) = 11
13875/1:
open("/global/opt/oracle/admin/MYANOTHERDB/adump/MYANOTHERDB_ora_13875_1.aud”,
O_RDWR|O_CREAT|O_EXCL, 0660) Err#17 EEXIST
13875/1: open("/global/opt/oracle/admin/MYANOTHERDB/adump/MYANOTHERDB_ora_13875_2.aud”, O_RDWR|O_CREAT|O_EXCL, 0660) = 11
13874/1: open("/global/opt/oracle/product/10.2.0/db_2/rdbms/mesg/oraus.msb”, O_RDONLY) = 12
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
13874/1: open("/global/opt/oracle/product/10.2.0/db_2/sqlplus/admin/glogin.sql”, O_RDONLY) = 12
SQL> 13874/1: ioctl(0, TCGETA, 0xFFFFFD7FFFDFACB0) = 0
SQL> create table test( t number);
13875/1: open("/global/db/data01/MYANOTHERDB/undotbs01.dbf”, O_RDWR|O_DSYNC) = 13
13875/1@1: -> libc:directio(0×100, 0×0, 0×0, 0×0, 0xfefefefeffffffff, 0xfefefefeff726574)
13875/1: ioctl(256, _ION(‘f’, 76, 0), 0×00000000) = 0
13875/1@1: <- libc:directio() = 0
13875/1: open("/global/db/data01/MYANOTHERDB/system01.dbf”, O_RDWR|O_DSYNC) = 13
13875/1@1: -> libc:directio(0×101, 0×0, 0×0, 0×0, 0xfefefefeffffffff, 0xfefefefeff726574)
13875/1: ioctl(257, _ION(‘f’, 76, 0), 0×00000000) = 0
13875/1@1: <- libc:directio() = 0
Table created.
SQL> drop table test;
Table dropped.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
In above output, ioctl(257, _ION(‘f’, 76, 0), 0×00000000) , 3rd parameter to ioctl call is 0 i.e. direct IO is not in use.
|