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;

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值