file open等待事件

2653885

This wait event is experienced whenever the database needs to open a file. Wait time is recorded beginning just prior to when the open request is issued until the time the request is returned, having succeeded or failed, from the operating system.ProblemWhen this wait is significantly impacting end user performance, you will see the 'file open' wait event in trace files, or in the V$SESSION_WAIT & V$SYSTEM_WAIT views.For the V$SYSTEM_WAIT view a simple check to see if the TOTAL_WAITS and TIME_WAITED are high or are increasing will determine if there is a problem.SELECT event, total_waits, time_waitedFROM v$system_eventWHERE event = 'file open';If you find that there are excessive wait times for file open activity it is best to go straight to the V$SESSION_WAIT view. This will assist in pinpointing which process is experiencing the wait.SELECT a.sid, c.pid, c.spid, a.username, b.event, b.wait_time, b.seconds_in_wait, b.p1, b.p2, b.p3FROM v$session a, v$session_wait b, v$process cWHERE a.sid = b.sidAND a.paddr = c.addrAND b.event = 'file open'This is the typical method for looking at processes in wait. The only problem is that we would hope that the "P" values could be used to assist us in pointing to the file that is in the wait state. Instead P1 & P2 contains internal Oracle information.SolutionsThis is one of those events you need to "catch in the act" through the v$session_wait view as prescribed above. Since this is a disk operating system issue, take the associated system process identifier (c.spid) and see what information we can obtain from the operating system.Typically the Unix truss command is used to determine the system level activity of a process.The two most typical methods for calling the truss command are:(hook to a process for read/write activity)truss rall wall p (hook to a process for everything)truss p We have even seen an NT version of truss called strace. The output of these commands will show, if caught in time, the file that it finally opens. You can then take this information and zero in on the disk subsystem the file resides on and then verify any tuning efforts or configuration changes.Causes for excessive time for data file opensWhile these are not all the causes, they tend to be some of the more common reasons Oracle encounters problems with the file open wait event.More data files in your database than the number of file descriptors allowed on your system. If this is the case then Oracle will need to cycle through the descriptors thus closing and opening files when needed.Hardware error may cause data files to be closed and then reopened when available.Excessive operating system disk activity outside Oracle increasing the time required to perform file opens. For example putting an Oracle data file on a disk that is used for logging might experience file open issues during high logging activity.Usage of remote data files or network attached storage where network latencies might be experienced.Improper hardware configuration, drivers, or patch levels not being maintained that do not allow for optimal communication with Oracle for file open requests. For example one type of controller may create a tablespace much faster than another.Expanded DefinitionThe file open is another wait event that signals you to look elsewhere besides the database for performance issues in the database server. It often is an indication of faulty disk configuration, misuse of disk resources, or even potential failure of a disk sub-system. To properly address the issue we need to go outside the database and look at the data files being accessed by Oracle. Once that is done we can take proper action to replace, reconfigure, or reroute disk activity.