latch: cache buffers chains
buffer busy waits
occurs intensively, system performance is sluggish and CPU starved.Locating Hot Block originals could be the first step before tuning system and SQL statements.
This Blog will first show 2 queries to list the Hot Blocks, and 1 query to connect Hot Blocks and their fetching Sessions. Then try to demonstrate some Workaround and Fixes.
Note. All tests are done in Oracle 12.1.0.2.0 and 11.2.0.3.0
1. Hot Block x$bh Query
with sq as (select object_name, data_object_id from dba_objects)
select hladdr cbc_latch_addr
,sum(tch) tch_sum
,listagg(tch || '-' || obj || '(' || object_name || ')/' || file# || '/' ||dbablk, ';')
within group (order by tch desc) "tch-obj(name)/file/blk_list"
,count(*) protected_db_blocks_cnt
from x$bh b, sq
where b.obj = sq.data_object_id
and tch > 0
group by hladdr
order by tch_sum desc;
2. Hot Block Tracking Query
alter system set "_db_hot_block_tracking"=true;
with sq as (select object_name, data_object_id from dba_objects)
select object_name ,h.*, b.*
from x$kslhot h, v$bh b, sq
where b.block# = h.kslhot_id
and b.objd = sq.data_object_id
order by h.kslhot_ref desc, b.file#, b.block#;
In case of Index block and IOT table block, an internal Oracle function can be used to map block to the entries. Voilà an example:
create table test_iot
(
id number(9), name varchar2(10),
constraint test_iot_key
primary key (id, name)
enable validate
)
organization index;
insert into test_iot select level, 'test_'||level from dual connect by level < 100000;
commit;
select object_id from dba_objects where object_name = 'TEST_IOT_KEY';
--returns
278020
select dbms_rowid.rowid_block_number(sys_op_lbid (278020, 'L', t.rowid)) block#
,t.*
from test_iot t
where id is not null or name is not null;
select block#
,count(*) rows_per_block
from (
select dbms_rowid.rowid_block_number(sys_op_lbid (278020, 'L', t.rowid)) block#
,t.*
from test_iot t
where id is not null or name is not null
)
group by block#
order by block#;
3. Hot Blocks and Sessions
We can connect Data Blocks with fetching Sessions via CBC Latch Address during the last 10 wait events as follows:
with lch_sid as
(select /*+ materialize */ event, p1, sid, sum(wait_time_micro) wait_time_micro, count(*) wait_cnt
from v$session_wait_history
where (p2=228 or event = 'latch: cache buffers chains') group by event, p1, sid)
,lch as
(select /*+ materialize */ event, p1
,listagg(sid || '(' || wait_time_micro || '/' || wait_cnt || ')', ';')
within group (order by wait_time_micro desc) "sid(wait_time_micro/wait_cnt)"
from lch_sid group by event, p1)
select hladdr cbc_latch_addr, w.p1 latch_addr_number, sum(tch) tch_sum, count(*) protected_db_blocks_cnt
,w."sid(wait_time_micro/wait_cnt)"
,listagg(tch || '-' || b.obj || '(' || ob.object_name || ')/' || file# || '/' ||dbablk, ';')
within group (order by tch desc) "tch-obj(name)/file/blk_list"
from x$bh b, dba_objects ob, lch w
where b.obj = ob.data_object_id(+)
and tch > 0
and to_number(hladdr, 'XXXXXXXXXXXXXXXX') = w.p1
group by hladdr, w.p1
order by tch_sum desc;
4. Workaround and Fixes
Hot Block is a phenomenon of frequently access / update of same DB Block concurrently by multiple sessions, exposed as "latch: cache buffers chains" (CBC Latch) contention.
DB Data Block is either Table Block or Index Block.
4.1 Table Block
We can use "MINIMIZE RECORDS_PER_BLOCK" to control the number of rows in each Block, one optimal value should be no more than "_db_block_max_cr_dba" (Maximum Allowed Number of CR buffers per dba), which is 6 in default (PCTFREE is not able to control the exact number of rows). For example,
drop table test_tab;
create table test_tab
INITRANS 26 -- prevent Segments ITL Waits and Deadlocks
as select level id, rpad('ABC', 10, 'X') val from dual connect by level <= 5;
alter table test_tab minimize records_per_block;
truncate table test_tab;
insert into test_tab select level id, rpad('ABC', 10, 'X') val from dual connect by level <= 100;
commit;
select block, count (*) cnt
from (select rowid rid,
dbms_rowid.rowid_object (rowid) object,
dbms_rowid.rowid_relative_fno (rowid) fil,
dbms_rowid.rowid_block_number (rowid) block,
dbms_rowid.rowid_row_number (rowid) ro,
t.*
from test_tab t
order by fil, block, ro)
group by block
order by block, cnt desc;
BLOCK CNT
-------- -----
1035776 5
1035777 5
1035778 5
...
20 rows selected.
select dbms_rowid.rowid_to_absolute_fno(t.rowid, 'K', 'TEST_TAB') af,
dbms_rowid.rowid_relative_fno (rowid) rf,
dbms_rowid.rowid_block_number (rowid) block,
dbms_rowid.rowid_row_number(rowid) row_no,
(sys_op_rpb(rowid)) rpb,
t.*
from test_tab t
order by af, rf, block, rpb;
AF RF BLOCK ROW_NO RPB ID VAL
----- --- --------- ------- ---- ---- ----------
1548 0 1035776 0 0 31 ABCXXXXXXX
1548 0 1035776 1 1 32 ABCXXXXXXX
1548 0 1035776 2 2 33 ABCXXXXXXX
1548 0 1035776 3 3 34 ABCXXXXXXX
1548 0 1035776 4 4 35 ABCXXXXXXX
1548 0 1035777 0 0 36 ABCXXXXXXX
...
100 rows selected.
select spare1
,least(1, bitand(spare1, (power(2, 15))))
"16th bit Hakan factor" -- "minimize records_per_block" flag
from sys.tab$ where obj# = (select obj# from sys.obj$ where name = 'TEST_TAB');
SPARE1 16th bit Hakan factor
------- ---------------------
32772 1
4.2 Index Block
We need to use PCTFREE, and INITRANS to approximate "RECORDS_PER_BLOCK".
drop index test_tab#u1;
create unique index test_tab#u1 on k.test_tab (id)
pctfree 90
initrans 26 -- prevent Segments ITL Waits and Deadlocks
;
exec dbms_stats.gather_table_stats(null, 'TEST_TAB', cascade => TRUE);
select object_id from dba_objects where object_name = 'TEST_TAB#U1';
-- return 2267423
select block#
,count(*) rows_per_block
from (
select dbms_rowid.rowid_block_number(sys_op_lbid (2267423, 'L', t.rowid)) block#
,t.*
from TEST_TAB t
where id is not null
)
group by block#
order by block#;
BLOCK# ROWS_PER_BLOCK
-------- --------------
1035796 5
1035797 5
1035798 5
...
20 rows selected.
Dumping Leaf block and Branch block:
Leaf block dump
===============
header address 18446741324863441060=0xfffffd7ffc0108a4
kdxcolev 0 <<<--- index level (0 represents Leaf blocks)
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 5 <<<--- number of index entries
kdxcofbo 46=0x2e
kdxcofeo 7401=0x1ce9
kdxcoavs 7355
kdxlespl 0
kdxlende 0
kdxlenxt 1035799=0xfce17
kdxleprv 1035797=0xfce15
kdxledsz 6
kdxlebksz 7456
kdxconro: number of index entries
Branch block dump
=================
header address 18446741324863505996=0xfffffd7ffc02064c
kdxcolev 1 <<<--- index level (1 represents Branch blocks)
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 19 <<<--- number of index entries
kdxcofbo 66=0x42
kdxcofeo 7923=0x1ef3
kdxcoavs 7857
kdxbrlmc 1035796=0xfce14
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 15
Leaf block "kdxconro 5" Branch block "kdxconro 19"So it seems that Branch block is not obliged to the same "PCTFREE, and INITRANS" as Leaf block.
Another observation is that the 5 Index Rows per Block is not persistent after Table is modified. That means some blocks can contain more or less than 5 Rows.
5. Index Hot Block Application Fix
Index Block can experience more acute CBC Latch contention because its size is usually smaller, and hence one block can contain more entries.
Since we have no way to apply "minimize records_per_block" to Index Blocks, in the application, we can try to minimize INDEX usage to cut down excess index access.
For example, by caching ROWID of previously fetched ROWs, at subsequent time, we can make the direct table fetch, and therefore bypass INDEX access:
declare
l_id number;
l_val varchar2(10);
type tab_rowid is table of rowid index by pls_integer;
rowid_cache tab_rowid;
begin
for k in 1..100 loop
for j in 1..10 loop
if rowid_cache.exists(k) then
begin
select /*+ not use index access */ id, val, rowid into l_id, l_val, rowid_cache(k)
from test_tab
where rowid = rowid_cache(k)
and id = k;
exception when others then dbms_output.put_line('No_Data_Found: id='||k||', rowid='||rowid_cache(k));
end;
end if;
if l_id is null or l_id != k then
select /*+ use index access */ id, val, rowid into l_id, l_val, rowid_cache(k)
from test_tab
where id = k;
dbms_output.put_line('Fill rowid_cache: id='||k||', rowid='||rowid_cache(k));
end if;
end loop;
end loop;
end;
/
/*+ use index access */: 3 Block Reads (1 Branch block + 1 Leaf block + 1 Table Block)
/*+ not use index access */: 1 Block Read (1 Table Block)
SELECT /*+ use index access */ ID, VAL, ROWID FROM TEST_TAB WHERE ID = :B1
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TEST_TAB (cr=3 pr=3 pw=0 time=145 us cost=1 size=14 card=1)
1 INDEX UNIQUE SCAN TEST_TAB#U1 (cr=2 pr=2 pw=0 time=102 us cost=1 size=0 card=1)(object id 2267423)
SELECT /*+ not use index access */ ID, VAL, ROWID FROM TEST_TAB WHERE ROWID = :B1 AND ID = :B2
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID TEST_TAB (cr=1 pr=0 pw=0 time=10 us cost=1 size=14 card=1)
