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