Skip to Main Content
  • Questions
  • in wait event "disk file operations i/o", P1/p2/p3 is 8/0/8, the first 8 refers to the file type, what kind of file does the file type 8 stands for?


Question and Answer

Connor McDonald

Thanks for the question, Hui.

Asked: December 12, 2016 - 11:18 pm UTC

Last updated: May 20, 2024 - 1:11 am UTC


Viewed 10K+ times! This question is

You Asked

one of our DBs experienced a performance slow problem after a network issue, according to AWR report, the 2 bottlenecks are "disk file operations i/o" and "session login/logoff", since we have db audit turned on and it writes audit info to reboot os file through rsyslog, moreover the rsyslog daemon didn't work normally after the network problem. we suspect it was the rsyslog problem which caused the db performance issue, but we need concrete proof for it. the P/P2/P3 value of the wait event "disk file operations i/o" is 8,0,8, the first 8 refers to the file type, while we don't know what kind of file that the file type 8 stands for. pls help, thank u

and Connor said...

Take a look at my friend Kyle's excellent post on this:


  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment


Filetype_id 8 is missing

Robin Chatterjee, March 16, 2017 - 1:26 pm UTC

Unfortunately that article while being very illuminative does not include filetype 8 . Other locations semm to indicate filetype 8 might be an audit file entry. can you confirm this. We too are getting a lot of misc disk file i/o operations with filetype id as 8.
Connor McDonald
April 07, 2017 - 3:11 am UTC

You should have read it more carefully :-)

"What about FileOperation=2? After a call to Oracle support, it looks like on this version, the values are:

1 file creation
2 file open
3 file resize
4 file deletion
5 file close
6 wait for all aio requests to finish
7 write verification
8 wait for miscellaneous io (ftp, block dump, passwd file)
9 read from snapshot files

So file *operation* 8 is any miscellaneous I/O we need to do.

For file *type* 8, there is no precise definition for type 8 - it basically signifies anything that is not a "particular known" form of I/O. Here's a list from 12.2 of the named file types:

1,Control File
2,Data File
3,Log File
4,Archive Log
6,Temp File
9,Data File Backup
10,Data File Incremental Backup
11,Archive Log Backup
12,Data File Copy
13,Parameter File
14,DR Config file
15,OSM File
16,Change Tracking File
17,Flashback Log
18,Data Pump Dump File
19,Cross Platform Data File
20,Auto Backup File
21,OS file
22,Block Dump File
23,CSS Voting File
24,OCR File
25,ASM Staleness File
26,ASM Vol Device File
27,ASM Vol Dirty Region File
28,Password File
29,ADS AMS Relation File
30,OCR Backup File
31,ASM Parameter File
32,Flash File
33,ASM spfile backup
34,External Table File
35,Data File XTT Backup
36,OS Audit File
37,Data File Incremental Backup
38,AKM KeyStore
39,AKM AutoLogin KeyStore
40,ORS Block Pool File
41,SQL Loader File
42,AM Container File

So there is a reasonable probability that audit entries may fall into this, because there is no specific entry in the file type for writing audit logs.

Luis Santos, April 04, 2017 - 6:14 pm UTC

We're also facing this issue. Did you get any further info about it?

[11g]> @ashtop event,P1,P2,P3 "event='Disk file operations I/O'" &10min

  Seconds     AAS %This   EVENT                                            P1         P2         P3 FIRST_SEEN           LAST_SEEN            DIST_SQLEXEC_SEEN
--------- ------- ------- ---------------------------------------- ---------- ---------- ---------- -------------------- -------------------- -----------------
     3739     6.2   96% | Disk file operations I/O                          8          0          8 2017-04-04 10:27:21  2017-04-04 10:36:28                  1
       23      .0    1% | Disk file operations I/O                          2       1629          2 2017-04-04 10:27:00  2017-04-04 10:36:44                 23
        9      .0    0% | Disk file operations I/O                          2       1632          2 2017-04-04 10:29:06  2017-04-04 10:33:02                  9
        4      .0    0% | Disk file operations I/O                          2       1624          2 2017-04-04 10:29:49  2017-04-04 10:34:57                  4
        3      .0    0% | Disk file operations I/O                          2       1601          2 2017-04-04 10:28:14  2017-04-04 10:33:03                  3

Audit directory issues

Naresh Bhandare, August 16, 2018 - 6:39 pm UTC

I have seen these waits when there were a lot of files in the audit (audit_file_dest) directory - cleaning up the directory resolved it in those cases. So that is one possibility to check.
Connor McDonald
August 20, 2018 - 4:34 am UTC

Nice input

Indeed audit files

Javier, September 30, 2019 - 10:36 pm UTC

Was having a I/O performance problem upgrading a PDB with the same event and P1

%This SESSION EVENT P1 P2 P3 TotalSeconds
------ ------- ------------------------------ ---------- ---------- ---------- ------------
64% WAITING Disk file operations I/O 8 1 36 8610

I checked the audit file dest and indeed a lot of data was being written into the .aud files.
I remembered I had created an audit policy for unified auditing and was auditing all by sys. Once I ran the noaudit and removed the policy the issue was gone.

Connor McDonald
October 01, 2019 - 1:05 am UTC

thanks for the good info

what is OSM filetype

A reader, May 02, 2024 - 12:38 pm UTC

I have one system waiting for this ""disk file operations i/o" a lot. If found the sessions are executing a particular sql and P1=2 means open, P2 is file no but not sure 0 means what, P3=15 means OSM file? what is OSM file any ideas?

---------------------------------------------------------------- ---------------
9rmg5wd03ftcb 2 0 15 FileOperation fileno filetype

Connor McDonald
May 20, 2024 - 1:11 am UTC

What is the SQL?

More to Explore


Get all the information about database performance in the Database Performance guide.