Hi Arun,
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?" at
http://www.ixora.com.au/newsletter/2000_12.htm#file_names 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" at
http://www.ixora.com.au/tips/tuning/log_switch.htm on that.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-----Original Message-----
Sent: Saturday, 12 May 2001 0:26
To: Multiple recipients of list ORACLE-L
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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Adams
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
