Monday, March 10, 2014

Hot Block Identification and latch: cache buffers chains

When Hot Block Event:
    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#; 
Once detecting the Hot Block, listing the rows in the block is straightforward. If the Hot Block belongs to a heap table, dbms_rowid.rowid_block_number will do that.

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
Now we can build the mapping between block# and rows:

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;
By the way, following short query can be used to check index quality:

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#;  
as discussed in Blog: "Index Efficiency 3"


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;
Then verify the number of entries per Block:

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. 
Blog: Spreading Out Data With MINIMIZE RECORDS_PER_BLOCK reveals the hidden mechanic ("Hakan factor") behind the scene:

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);
Then verify the number of entries per Block:

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.
Above method is not able to regulate exact number of index entries per block. Moreover, we need to check if both Leaf block and Branch block are subject to the same "PCTFREE, and INITRANS" conditions.

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
which shows the number of index entries:
  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;
/
Then we have two XPLANs: one single Run with Index reading 3 Blocks; other repeated Runs without Index reading one single Block.

  /*+ 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)