A new Oracle DBA can find here information on how to troubleshoot file descriptor performance problems.
File Open
Definition: The "file open" 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.
How do I know if I have a problem?
As with any wait event, you have a problem when the wait is significantly impacting end user performance. Generally 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_waited FROM v$system_event WHERE 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.p3 FROM v$session a, v$session_wait b, v$process c WHERE a.sid = b.sid AND a.paddr = c.addr AND 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 that is held close to their chests and as such we really can’t use them for anything.
How to combat the event
This is one of those events you need to "catch in the act" through the v$session_wait view as prescribed above. Then, 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 <c.spid> (hook to a process for everything) |
||
There are different truss-like commands depending on your flavor of Unix and we have even seen an NT version 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 opens
While these are not all the causes, they tend to be some of the more common reasons Oracle has 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.
Conclusion
The 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.
