Decoding 'latch:cache buffers chains' object from Oracle trace file

from:http://blogs.sun.com/glennf/entry/decoding_latch_cache_buffers_chains

 

If you have been using Method-R for response time based profiling, then you will appreciate this note. The "Cache Buffers Chains" latch pops up from time-to-time when trying to scale applications on high-end systems. It is usually a sign of an application issue so locating the source of contention is critical. There are several notes in metalink (42152.1 and 163424.1 ) which describe how to find hot blocks, but nothing that uses the the Oracle "10046 event" trace files.
Below is output from a trace file (10gR1) which shows the application waiting on a CBC latch:

   WAIT #3: nam='latch: cache buffers chains' ela= 18996 p1=15245584968 p2=116 p3=1

The ADDR in "p1=" is a decimal value which can be converted into hex and used to query the x$bh, v$latch_children, and sys.dba_extents tables to find the objects that are contending CBCs.

I created a script "CBC_p1_to_obj.sql" which hides the nasty sql and takes the ADDR as input. Below is an example from a recent experiment:

SQL> @CBC_p1_to_obj



Function created.



Enter value for cbc_addr_p1: 15245584968

old  12:   x.hladdr  = to_hex('&&CBC_ADDR_P1') and

new  12:   x.hladdr  = to_hex('15245584968') and



SEGMENT_NAME                           EXTENT#     BLOCK#        TCH     CHILD#     SLEEPS

----------------------------------- ---------- ---------- ---------- ---------- ----------

SYSMAN.MGMT_METRICS                          6          8          2        944          0

SYSMAN.MGMT_METRICS_RAW_PK                  32        113          2        944          0

SYSMAN.MGMT_METRICS_RAW_PK                  33         90          2        944          0

SYSMAN.MGMT_METRICS_1HOUR_PK                18         21          2        944          0

SYSMAN.MGMT_METRICS_1HOUR_PK                17         44          2        944          0

SYS.I_DEPENDENCY1                           18         14          1        944          0

SYS.WRI$_ADV_TASKS_IDX_01                    0          1          1        944          0

XDB.SYS_LOB0000043477C00008$$                0          3          1        944          0

OLAPSYS.MRAC_OLAP2_AW_PHYS_OBJ_T             0          3          1        944          0

OLAPSYS.MRAC_OLAP2_AW_PHYS_OBJ_T             0          3          1        944          0

XDB.SYS_LOB0000043477C00008$$                0          3          1        944          0



SEGMENT_NAME                           EXTENT#     BLOCK#        TCH     CHILD#     SLEEPS

----------------------------------- ---------- ---------- ---------- ---------- ----------

SYS.C_OBJ#                                  16        118          0        944          0

DG.T1PK                                     19        115          0        944          0



13 rows selected.

I hope you will find this script useful. Let me know if you experience any issues.

 

-----------------------------

CBC_p1_to_obj.sql

-----------------------------

 

 

create or replace function to_hex( p_dec in number )
return varchar2
is
        l_str   varchar2(255) default NULL;
        l_num   number  default p_dec;
        l_hex   varchar2(16) default '0123456789ABCDEF';
begin
        if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
                raise PROGRAM_ERROR;
        end if;
        loop
                l_str := substr( l_hex, mod(l_num,16)+1, 1 ) || l_str;
                l_num := trunc( l_num/16 );
                exit when ( l_num = 0 );
        end loop;
        return lpad(l_str,16,'0');
end to_hex;
/

undefine CBC_ADDR_P1
column segment_name format a35
set linesize 120

select /*+ RULE */
  e.owner ||'.'|| e.segment_name  segment_name,
  e.extent_id  extent#,
  x.dbablk - e.block_id + 1  block#,
  x.tch,
  l.child#,l.sleeps
from
  sys.v$latch_children  l,
  sys.x$bh  x,
  sys.dba_extents  e
where
  x.hladdr  = to_hex('&&CBC_ADDR_P1') and
  e.file_id = x.file# and
  x.hladdr = l.addr and
  x.dbablk between e.block_id and e.block_id + e.blocks -1
  order by x.tch desc ;
exit;