Buffer Busy Waits – Reason Codes and Block Classes

16 04 2010

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


Actions

Information

3 responses

18 11 2011
Shu Hsieh

You said “Maybe the next step in this case would be to start monitoring V$ROLLSTAT if automatic undo management is not in use”
What if I have really high waits on “UNDO Header” and lots of buffer busy wait by smon, and AUM is in use, and plenty of free space still available for UNDO tablespaces.

18 11 2011
Charles Hooper

Shu,

I think that you need to add some perspective to the number of “high waits” for the undo header. Just because there are a large number of waits for the undo header, that does not necessarily mean that a lot of time is lost to this particular type of buffer busy wait. If you examine the deltas for the wait event, is this event even close to the top 5? Keep in mind, even in an otherwise idle system there will always be a top 5 set of wait events.

I suggest a bit of reading (take a close look at the comments also):

http://hoopercharles.wordpress.com/2010/06/21/undo-segments-what-is-wrong-with-this-quote/

http://hoopercharles.wordpress.com/2010/05/09/true-or-false-undo/

http://hoopercharles.wordpress.com/2010/04/19/true-or-false-buffer-busy-waits/

Finally, I suggest performing a search on Jonathan Lewis’ blog if you have identified that there is a real, measureable, performance problem related to waits for the undo header:

http://jonathanlewis.wordpress.com/

5 12 2012
Temporary tablespaces: to BIGFILE or not to BIGFILE | The Pythian Blog

[...] P1 = 501, P2 = 2, P3 = 13. P3 lists the block type, and there’s a great summary of values on Charles Hooper’s blog. 13 maps to “file header [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 143 other followers

%d bloggers like this: