– Mount FS with CIO for DFs, redo, and CFs, do not use it with archived logs and OH.
– On 9i and 10g, change file filesystemio_options=setall, by default it is asynch. So if file system is not mounted with CIO option, Oracle will not use Concurrent IO.
– It’s recommended to use JFS2 with CIO for standalone instance, use GPFS for clustered database. Do NOT use JFS with DIO or JFS with large file enabled.
– Enable large page support
– disable VMM from steeling pages from the SGA (lock_sga)
Check whether CIO is being used by Oracle
Test 1) file system /gold/GLT/db is JFS2 system mounted with CIO
while Oracle setting filesystemio_options=ASYNC
-- start up instance in nomount state SQL> startup nomount; ORACLE instance started. Total System Global Area 3221225472 bytes Fixed Size 2099856 bytes Variable Size 1593836912 bytes Database Buffers 1610612736 bytes Redo Buffers 14675968 bytes -- trace dbwr process $ ps -ef |grep dbw gltoracl 1175784 1 0 12:10:05 - 0:00 ora_dbw0_GOLDTEST $ truss -a -t open -p 1175784 psargs: ora_dbw0_GOLDTEST (sleeping...) (sleeping...) (sleeping...) (sleeping...) (sleeping...) -- open datafiles open("/gold/GLT/db/oradata/GOLDTEST/system01.dbf", O_RDONLY|O_LARGEFILE) = 16 open("/gold/GLT/db/oradata/GOLDTEST/system01.dbf", O_RDWR|O_DSYNC|O_LARGEFILE) = 16 open("/gold/GLT/db/oradata/GOLDTEST/undotbs01.dbf", O_RDONLY|O_LARGEFILE) = 17 open("/etc/vfs", O_RDONLY) = 18 open("/gold/GLT/db/oradata/GOLDTEST/undotbs01.dbf", O_RDONLY|O_LARGEFILE) = 17 open("/gold/GLT/db/oradata/GOLDTEST/undotbs01.dbf", O_RDWR|O_DSYNC|O_LARGEFILE) = 17 open("/gold/GLT/db/oradata/GOLDTEST/sysaux01.dbf", O_RDONLY|O_LARGEFILE) = 18 open("/etc/vfs", O_RDONLY) = 19 open("/gold/GLT/db/oradata/GOLDTEST/sysaux01.dbf", O_RDONLY|O_LARGEFILE) = 18 open("/gold/GLT/db/oradata/GOLDTEST/sysaux01.dbf", O_RDWR|O_DSYNC|O_LARGEFILE) = 18 open("/gold/GLT/db/oradata/GOLDTEST/users01.dbf", O_RDONLY|O_LARGEFILE) = 19 open("/etc/vfs", O_RDONLY) = 20 open("/gold/GLT/db/oradata/GOLDTEST/users01.dbf", O_RDONLY|O_LARGEFILE) = 19
CIO is not being used by Oracle even the file system is mounted with CIO option;
Test 2) file system /gold/GLT/db is JFS2 file system mounted with CIO option
while Oracle setting filesystemio_options=SETALL
————————————————————————————
— start up instance in nomount state
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 3221225472 bytes
Fixed Size 2099856 bytes
Variable Size 1593836912 bytes
Database Buffers 1610612736 bytes
Redo Buffers 14675968 bytes
— trace dbwr process
$ ps -ef |grep dbw
gltoracl 1175784 1 0 12:10:05 – 0:00 ora_dbw0_GOLDTEST
$ truss -a -t open -p 1175784
psargs: ora_dbw0_GOLDTEST
(sleeping…)
(sleeping…)
(sleeping…)
(sleeping…)
(sleeping…)
— when mounting database
SQL> alter database mount;
open(“/gold/GLT/bin/oracle/product/10.2.0/db_1/dbs/lkGOLDTEST”, O_RDWR|O_CREAT|O_EXCL, S_IRUSR|S_IWUSR|S_IRGRP|S_IWGRP) = -1
open(“/gold/GLT/bin/oracle/product/10.2.0/db_1/dbs/lkGOLDTEST”, O_RDWR) = 12
(sleeping…)
open(“/gold/GLT/admin/GOLDTEST/control/control01.ctl”, O_RDONLY|O_LARGEFILE) = -1
open(“/gold/GLT/admin/GOLDTEST/control/control01.ctl”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 13
^^^^^
open(“/gold/GLT/admin/GOLDTEST/control/control02.ctl”, O_RDONLY|O_LARGEFILE) = -1
open(“/gold/GLT/admin/GOLDTEST/control/control02.ctl”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 14
^^^^^
open(“/gold/GLT/db/GOLDTEST/control/control03.ctl”, O_RDONLY|O_LARGEFILE) = 15
open(“/gold/GLT/db/GOLDTEST/control/control03.ctl”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 15
^^^^^
— when opening database
SQL> alter database open;
open(“/gold/GLT/db/oradata/GOLDTEST/system01.dbf”, O_RDONLY|O_CIO|O_LARGEFILE) = 16
open(“/etc/vfs”, O_RDONLY) = 17
open(“/gold/GLT/db/oradata/GOLDTEST/system01.dbf”, O_RDONLY|O_LARGEFILE) = 16
open(“/gold/GLT/db/oradata/GOLDTEST/system01.dbf”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 16
open(“/gold/GLT/db/oradata/GOLDTEST/undotbs01.dbf”, O_RDONLY|O_CIO|O_LARGEFILE) = 17
open(“/etc/vfs”, O_RDONLY) = 18
open(“/gold/GLT/db/oradata/GOLDTEST/undotbs01.dbf”, O_RDONLY|O_LARGEFILE) = 17
open(“/gold/GLT/db/oradata/GOLDTEST/undotbs01.dbf”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 17
open(“/gold/GLT/db/oradata/GOLDTEST/sysaux01.dbf”, O_RDONLY|O_CIO|O_LARGEFILE) = 18
open(“/etc/vfs”, O_RDONLY) = 19
open(“/gold/GLT/db/oradata/GOLDTEST/sysaux01.dbf”, O_RDONLY|O_LARGEFILE) = 18
open(“/gold/GLT/db/oradata/GOLDTEST/sysaux01.dbf”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 18
open(“/gold/GLT/db/oradata/GOLDTEST/users01.dbf”, O_RDONLY|O_CIO|O_LARGEFILE) = 19
open(“/etc/vfs”, O_RDONLY) = 20
open(“/gold/GLT/db/oradata/GOLDTEST/users01.dbf”, O_RDONLY|O_LARGEFILE) = 19
open(“/gold/GLT/db/oradata/GOLDTEST/users01.dbf”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 19
open(“/gold/GLT/db/oradata/GOLDTEST/centestdata01.dbf”, O_RDONLY|O_CIO|O_LARGEFILE) = 20
open(“/etc/vfs”, O_RDONLY) = 21
open(“/gold/GLT/db/oradata/GOLDTEST/centestdata01.dbf”, O_RDONLY|O_LARGEFILE) = 20
open(“/gold/GLT/db/oradata/GOLDTEST/centestdata01.dbf”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 20
open(“/gold/GLT/db/oradata/GOLDTEST/centestidx01.dbf”, O_RDONLY|O_CIO|O_LARGEFILE) = 21
open(“/etc/vfs”, O_RDONLY) = 22
open(“/gold/GLT/db/oradata/GOLDTEST/centestidx01.dbf”, O_RDONLY|O_LARGEFILE) = 21
open(“/gold/GLT/db/oradata/GOLDTEST/centestidx01.dbf”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 21
open(“/gold/GLT/db/oradata/GOLDTEST/goldblob.dbf”, O_RDONLY|O_CIO|O_LARGEFILE) = 22
open(“/etc/vfs”, O_RDONLY) = 23
open(“/gold/GLT/db/oradata/GOLDTEST/goldblob.dbf”, O_RDONLY|O_LARGEFILE) = 22
open(“/gold/GLT/db/oradata/GOLDTEST/goldblob.dbf”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 22
open(“/gold/GLT/db/oradata/GOLDTEST/stktestdata01.dbf”, O_RDONLY|O_CIO|O_LARGEFILE) = 23
open(“/etc/vfs”, O_RDONLY) = 24
open(“/gold/GLT/db/oradata/GOLDTEST/stktestdata01.dbf”, O_RDONLY|O_LARGEFILE) = 23
open(“/gold/GLT/db/oradata/GOLDTEST/stktestdata01.dbf”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 23
open(“/gold/GLT/db/oradata/GOLDTEST/stktestidx01.dbf”, O_RDONLY|O_CIO|O_LARGEFILE) = 24
open(“/etc/vfs”, O_RDONLY) = 25
open(“/gold/GLT/db/oradata/GOLDTEST/stktestidx01.dbf”, O_RDONLY|O_LARGEFILE) = 24
open(“/gold/GLT/db/oradata/GOLDTEST/stktestidx01.dbf”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 24
open(“/gold/GLT/db/oradata/GOLDTEST/temp_01.dbf”, O_RDONLY|O_CIO|O_LARGEFILE) = 25
open(“/etc/vfs”, O_RDONLY) = 26
open(“/gold/GLT/db/oradata/GOLDTEST/temp_01.dbf”, O_RDONLY|O_LARGEFILE) = 25
open(“/gold/GLT/db/oradata/GOLDTEST/temp_01.dbf”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 25
open(“/gold/GLT/db/oradata/GOLDTEST/temp_02.dbf”, O_RDONLY|O_CIO|O_LARGEFILE) = 26
open(“/etc/vfs”, O_RDONLY) = 27
open(“/gold/GLT/db/oradata/GOLDTEST/temp_02.dbf”, O_RDONLY|O_LARGEFILE) = 26
open(“/gold/GLT/db/oradata/GOLDTEST/temp_02.dbf”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 26
open(“/gold/GLT/db/oradata/GOLDTEST/temp_03.dbf”, O_RDONLY|O_CIO|O_LARGEFILE) = 27
open(“/etc/vfs”, O_RDONLY) = 28
open(“/gold/GLT/db/oradata/GOLDTEST/temp_03.dbf”, O_RDONLY|O_LARGEFILE) = 27
open(“/gold/GLT/db/oradata/GOLDTEST/temp_03.dbf”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 27
open(“/gold/GLT/db/oradata/GOLDTEST/temp_04.dbf”, O_RDONLY|O_CIO|O_LARGEFILE) = 28
open(“/etc/vfs”, O_RDONLY) = 29
open(“/gold/GLT/db/oradata/GOLDTEST/temp_04.dbf”, O_RDONLY|O_LARGEFILE) = 28
open(“/gold/GLT/db/oradata/GOLDTEST/temp_04.dbf”, O_RDWR|O_CIO|O_DSYNC|O_LARGEFILE) = 28
open(“/gold/GLT/bin/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb”, O_RDONLY) = 29
[/sourcecode]
Conclusion
although file system is mounted with CIO option, Oracle will ignore it if its filesystemio_options=async (default value)
In order to use Asynch IO and Concurrent IO, we have to set it to filesystemio_options=SETALL;
Further reading:
Recommendations For using CIO/DIO for Filesystems containing Oracle Files on AIX [ID 960055.1] | |||||
|
|||||
Modified 17-MAR-2011 Type BULLETIN Status PUBLISHED |
Purpose
Scope and Application
Recommendations For using CIO/DIO for Filesystems containing Oracle Files on AIX
The CIO/DIO recommendations for filesystems containing Oracle Files are:
References
Applies to:
Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 11.1.0.7 – Release: 10.1 to 11.1
IBM AIX on POWER Systems (64-bit)
IBM AIX Based Systems (64-bit)
Purpose
Recommendation for using CIO/DIO for Oracle Files on AIX
Scope and Application
All DBAs who work on AIX.
Recommendations For using CIO/DIO for Filesystems containing Oracle Files on AIX
From 10g onwards, If you set filesystemio_options=setall then Oracle by default uses the CIO option to perform i/o on the oracle files. There is no need to specify the cio flag explicitly to mount the disk used for the Oracle Files.
You need to set the CIO flag explicitly if you are not using filesystemio_options=setall.
The CIO/DIO recommendations for filesystems containing Oracle Files are:
Data Base Files (DBF)
++ Use CIO or no mount options for extremely sequential I/O
Redo Log/Control Files
++ Use CIO or DIO
Archive Log Files
++ Do not use CIO or DIO
Flashback Log Files
++ Flashback Log files should use CIO, DIO, or rbrw
Oracle Binaries
++ Do not use CIO or DIO