Foreground processes open the datafiles only when they need to read from them (or write to them in the case of a sort). The file open wait event represents the opening of and typically the first I/O operation against that datafile, however, sometimes a few extraneous I/O operations against other datafiles seem to be included. The truss output below shows a file open wait delimited by calls to the times() system call, with two extraneous reads included in the wait. Incidentally, it also shows how Oracle duplicates the file descriptor onto a high numbered file descriptor.
times(0xEFFF8F78) = 31885993
open64("/database/PRODidxC01.dbf", O_RDWR|O_DSYNC) = 11
getrlimit(RLIMIT_NOFILE, 0xEFFF5100) = 0
fstat64(403, 0xEFFF5068) = 0
fstat64(402, 0xEFFF5068) Err#9 EBADF
fcntl(11, F_DUP2FD, 0x00000192) = 402
close(11) = 0
fcntl(402, F_SETFD, 0x00000001) = 0
ioctl(402, 0x0403, 0xEFFF50C4) = 0
pread64(402, "0602\0\0\081 /A5 . -1A8C".., 8192, 0x25F4A000) = 8192
pread64(405, "0602\0\00380 <01 /BC *18".., 8192, 0x07802000) = 8192
pread64(408, "0602\0\00E81 B18 0848AF6".., 8192, 0x28430000) = 8192
times(0xEFFF7D00) = 31885997
Large numbers of file open waits are seen in applications that frequently disconnect from the instance and then reconnect later, rather than leaving their
connection open. Also, if the instance has been up for a long time, the CKPT
process can accumulate a lot of file open waits - several per file per checkpoint.
In your case the number of file identify waits makes me suspect the second explanation. In general the file identify operation is just done once per datafile and controlfile. It validates the filename and establishes a file information block in the SGA that is then shared by all processes needing to open any of the datafiles or controlfiles. On the other hand, LGWR identifies each member of the selected logfile group as part of each logfile switch. So a large number of file identify waits is consistent with the instance having been up for a long time with regular log switches.
Your average time waited for these waits is acceptable. In general, the average file open time should be of the same order of magnitude as the average db file sequential read time. If not, you should ensure that none of the Oracle files have what the operating system regards as "long" filenames, or a parent directory with a "long" filename. See What's in a name? in last December's issue of Ixora News on that. In some cases, it also helps to hold the log files open. See Holding the log files open on that.
Can somebody please explain to me what this event is, I am not able to find good documentation on this issue.
EVENT TOTAL_WAIT TOTAL_TIME TIME_WAITE AVERAGE_WA -------------- ---------- ---------- ---------- ---------- file identify 38343 0 3135 .08176199 file open 9436698 0 3934742 .416961738