Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘gc buffer busy

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.

Written by Dion Cho

February 23, 2009 at 8:48 am

Follow

Get every new post delivered to your Inbox.

Join 58 other followers