Buffer Busy Waits – Reason Codes and Block Classes

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;

Other Resources:
http://db-optimizer.blogspot.com/2010/01/oracle-wait-buffer-busy-wait.html
http://perfvision.com/papers/06_buffer_cache.ppt
http://perfvision.com/papers/Unit1_Intro_to_Waits.ppt