Monday, March 10, 2014

Hot Block Identification

Hot Block trigger events:

   latch: cache buffers chains
   buffer busy waits


and causes CPU starvation.

Locating Hot Block original helps application developer to write efficient SQL and PL/SQL code.

This Blog will give two queries to list the Hot Blocks.

Query-1


 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;


Query-2


   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 by dbms_rowid.rowid_block_number.

However, for 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.