RE: File open event

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: steve.adams_at_ixora.com.au

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: ListGuru_at_fatcity.com (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).