Oracle "file open" wait event tuning Oracle Database Tips by Donald Burleson

In Oracle AWR and the 10g wait event views can quickly identify the total waits and the time waited for "file open" events:

select
   event,
   total_waits,
   time_waited
from
   v$system_event
where
   event = 'file open';

To find the specific files that are experiencing "file open" waits you can run this query:

You can then get the name of the data file with these queries.  Here is a complete article on getting the values from p1, p2 and p3.

• P1—The absolute file number for the data file involved in the wait.  

• P2—The block number within the data file referenced in P1 that is being waited upon.  

• P3—The reason code describing why the wait is occurring.

Here's an Oracle data dictionary query for these values:

select
   p1 "File #",
   p2 "Block #",
   p3 "Reason Code"
from
   v$session_wait
where
   event = 'file open'; 

Causes of "file open" waits

There are many possible cause of file open waits in an Oracle databases, most of them related to external disk conditions:

• Disk contention - Disk spindle channel contention and file enqueues (you can check these with the iostat utility) 

• Lack of OS file descriptors 

• Improper hardware configuration - This is especially true in Oracle Real Application Clusters (RAC) database where you have a wide variety of file storage options.  Most savvy RAC shops use JBOD with Oracle Automatic Storage Management (ASM), using S.A.M.E. striping.  Here is the best book on this issue. 

• Remote mounts - Using SAN (storage area networks) or NAS (Network attached storage) can slow-down file opens. 

• Distributed queries - File activity over a database link van be very slow