Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Parameter3 of gc buffer busy/gc current request wait event

with 6 comments

Following is the definition(p1, p2, p3) of gc buffer busy and gc current request wait event.

UKJA@ukja102> begin
  2    print_table('select name, parameter1, parameter2, parameter3
  3             from v$event_name
  4             where name in (''gc buffer busy'', ''gc current request'')');
  5  end;
  6  /
NAME                          : gc buffer busy
PARAMETER1                    : file#
PARAMETER2                    : block#
PARAMETER3                    : id#
-----------------
NAME                          : gc current request
PARAMETER1                    : file#
PARAMETER2                    : block#
PARAMETER3                    : id#
-----------------

We have a clear meaning of parameter1 and parameter2 which represents DBA(data block address). But parameter3? What does id# mean? This was one of my long unsolved question.

Tanel Poder gave an insight on this at this OTN forum message.
The parameter3 of gc cr request wait event is “block class”(class#). When troubleshooting block level contention, this block class information is really helpful. Without this, the boring block dump(alter system dump…) is unavoidable.

UKJA@ukja102> begin                                               
  2    print_table('select name, parameter1, parameter2, parameter3
  3             from v$event_name                                    
  4             where name in (''gc cr request'')');                 
  5  end;                                                         
  6  /                                                            
NAME                          : gc cr request                     
PARAMETER1                    : file#                             
PARAMETER2                    : block#                            
PARAMETER3                    : class#                            
-----------------

The good news is that the parameter3 of gc buffer busy/gc current request wait event also has block class info in it. Exactly speaking, the lower 2 bytes of the parameter3 value is block class.

For instance, we have following wait events (extracted from raw 10046 trace)

gc current request  file#= 717   block#= 2  id#= 33554445
gc buffer busy      file#= 1058  block#= 2  id#= 65549

I can extract the lower 2 bytes of (33554445, 65549) like this

UKJA@ukja102> with
  2       v1 as (select to_hex(33554445) as h from dual),
  3       v2 as (select to_hex(65549) as h from dual)
  4  select
  5    to_dec(substr(v1.h, length(v1.h)-1, 2)) as v1,
  6    to_dec(substr(v2.h, length(v2.h)-1, 2)) as v2
  7  from v1, v2
  8  ;
        V1         V2
---------- ----------
        13         13   <-- Both blocks are class# 13

What does this 13 mean? V$waitstat view has the answer.

UKJA@ukja102> select rownum, class from v$waitstat;
    ROWNUM CLASS
---------- ------------------
         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 bmb
         9 2nd level bmb
        10 3rd level bmb
        11 bitmap block
        12 bitmap index block
        13 file header block <-- Here!
        14 unused
        15 system undo header
        16 system undo block
        17 undo header
        18 undo block

Yes, we have block contention on file header block which controls the bitmap information in LMT.

We can infer the block class from block# 2 in this special case. But,th in more general cases, it’s not easy to infer the block class from block#.

Tanel deserves my sincere thanks. :)

PS) The sources of to_dec and to_hex function are here.

About these ads

Written by Dion Cho

February 23, 2009 at 8:48 am

6 Responses

Subscribe to comments with RSS.

  1. It’s a good stuff.I encounterd a similar case.
    From the gc buffer busy waits’s p1 and p2,i get the object’s UNDO datafile.But the p3′s 65659 that meaning “bitmap block”.The Undo tablespace isn’t assm.I confuse what the undo segment’s ‘bitmap block’.

    johnwoo

    November 25, 2009 at 9:37 am

    • Hi, Johnwoo.

      The value of 65659 means the block class “123″, which represents the undo header of the 54th(2*54+15=123) undo segment. This would mean that your nodes are fighting each other to get the same undo header(by any reason).

      Dion Cho

      November 26, 2009 at 1:18 am

  2. Thank you very much.But I get the answer from tanel that class numbers starting from 17 are regular undo segments.So is it 17 or 15?

    johnwoo

    November 26, 2009 at 8:09 am

    • Hi, Johnwoo.
      Tanel might have meant following things:

      UKJA@ukja1021> select rownum, class from v$waitstat;
          ROWNUM CLASS
      ---------- ------------------
               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 bmb
               9 2nd level bmb
              10 3rd level bmb
              11 bitmap block
              12 bitmap index block
              13 file header block
              14 unused
              15 system undo header
              16 system undo block
              17 undo header
              18 undo block
      

      Undo header = 2*r + 15
      Undo block = 2*r + 16
      r = 0 means system undo, r >= 1 means user undo. Except the system undo, it starts with 17. 17 is the first undo segment.
      So 123 is the undo block header of the 54th undo segment. (2*54 + 15)

      Dion Cho

      November 26, 2009 at 8:32 am

  3. Tanel gave me a sql:
    select class, r undo_segment_id from (
    select class, null r
    from (select class, rownum r from v$waitstat)
    where r = bitand(&1,to_number(‘FFFF’,’XXXX’))
    union all
    select
    decode(mod(bitand(&1,to_number(‘FFFF’,’XXXX’)) – 17,2),0,’undo header’,1,’undo data’, ‘error’) type
    , trunc((bitand(&1,to_number(‘FFFF’,’XXXX’)) – 17)/2) undoseg_id
    from
    dual
    )
    where rownum = 1
    /
    It seems like the undo starts with 17.So from tanel’s sql,i get the 53th undo segment.

    johnwoo

    November 27, 2009 at 2:58 am

    • Johnwoo.
      It seems to depend on how we define the “1st”.
      Tanel’s script defines the 1st as 0.
      My calculation defines the 1st as 1.

      When the first undo segment is #0, 123 is the 53th undo segment.
      When the first undo segment is #1, 123 is the 54th undo segment.

      Dion Cho

      November 27, 2009 at 3:12 am


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 58 other followers

%d bloggers like this: