April 16, 2010
A couple of years ago the following question was asked in an OTN forum thread:
Does anyone know where I can find the reason codes for all the different wait events?
Sometimes questions need a but more detail to avoid confusion, where the correct answer is given to the wrong question. The original poster was actually asking about the meaning of the P3 parameter of a ‘direct path write‘ wait event, while the phrase “reason codes” seems to typically imply ‘buffer busy waits’ wait events. The thread eventually transitioned to a brief discussion of the P3 parameter of buffer busy waits.
For Oracle Database 10g R1 and above, the P3 parameter describes the block class, the type of block, involved in the buffer busy wait (source http://www.juliandyke.com/Internals/BlockClasses.html – see the links at the bottom of this article for the meaning of the P3 parameter prior to Oracle Database prior to 10g R1):
1 Data block 2 Sort block 3 Save undo block 4 Segment header 5 Save undo header 6 Free List 7 Extent map 8 1st level bitmap block 9 2nd level bitmap block 10 3rd level bitmap block 11 Bitmap block 12 Bitmap index block 13 File header block 14 Unused 15 System undo block 16 System undo block 17 Undo header 18 Undo block
For block class values above 18, the following calculation may be used, replacing n with the identified block class:
SELECT DECODE(MOD(n - 16,2),1,'Undo Header for Undo Segment ','Undo Block for Undo Segment ')||TO_CHAR(FLOOR((n - 17) / 2) + 1) DESCRIPTION FROM DUAL;
For example, if the following appeared in a 10046 trace file (10.1 or above, in this case):
WAIT #12: nam='buffer busy waits' ela= 133261 file#=7 block#=2 class#=13 obj#=0 tim=14319369615
The P3 parameter is identified as class#, with a value of 13. Based on the above list, the buffer busy wait is on the file header block of absolute file number 7.
If the following appeared in a 10046 trace file:
WAIT #24: nam='buffer busy waits' ela= 2636 file#=7 block#=137 class#=55 obj#=0 tim=82961305208
The output of the SQL statement calculation, after plugging in 55 for the value of n, is:
DESCRIPTION ------------------------------- Undo Header for Undo Segment 20
The above indicates that the buffer busy wait is for the undo header of undo segment 20. What should we do about buffer busy waits for the undo header, or for that matter any other block class? The Oracle documentation (under the heading 10.3.1 buffer busy waits) offers suggestions for several of the block classes. Maybe the next step in this case would be to start monitoring V$ROLLSTAT if automatic undo management is not in use:
SELECT * FROM V$ROLLSTAT WHERE USN=20;