Archive for March 30th, 2011

  |  
March 30, 2011

Using CIO/DIO for Oracle on AIX

– 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

In this Document
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

References

http://www.ibm.com/developerworks/wikis/download/attachments/53871900/Oracle_on_AIX_WebinarFeb2007.pdf?version=2

Posted in AIX, Archived logs, Datafile, File System, redo log | Leave a Comment »

Tags: ,
  |