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:

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

  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;