Parameter3 of gc buffer busy/gc current request wait event

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.