Thursday, July 19, 2012

Disk file operations I/O wait event on 11.2 version oracle


In continuation of previous blog i would like to share what i know recently in version 11.2.0.3 version (or starting with 11.2.0.1 but not sure), that there is new functionally involved when ever any read/write request is made to datafiles. The functionality goes something like:
When oracle process make a call to datafiles for its read operation, a new call open() is made to open datafile (which is identified as a raw device) to determine if file is  safe for Oracle to use.

This is more like safety measure built in oracle version 11.2 which was not a part of earlier release. This operation i.e open() call is made with negligible time (as you have seen in previous blog for 10046 tracefile). However if there are large numbers of datafile probably 1000+ datafiles, this operation can impact some performance and more significant when OS takes time open and close a call, because its OS duty to make open() call.

Since we are Exadata, this wait event at starting of oracle session which is about to read data from datafiles should not take much time , at least in our environment i.e. X2-2 i have not seen significant time spent on this operation.

There are OS like HP where open() could take more time and more importantly if there are using RAW devices for their storage. There are BUGs reported when OS is HP and had used RAW devices for storage. And there are fixes available too to disable this new functionality on these platforms where it is know open() call takes more time then normal.

Below if excerpts took from support.oracle.com about this wait event:
*
When opening ASM disks, we try to determine if the file that is open is a VSD/
HSD raw device. This is done to give a warning to the customer that usage of
such a device will result in data loss. This check incurs an extra open() and
stat() system call. If the open() system call itself is taking a long time
due to some OS bug/limitation, the extra open() system call can make it even worse.*

Another problem is when data is opened/startup,  because as we know each datafile is checked before opening the database and this new functionality can cause slowdown the startup process of database if there are large numbers of datafiles especially if you are on HP-UX.

Be aware of this issue if you are on HP platform....