Identify hot blocks

SQL> select banner from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create table horia.new_date as select * from all_objects;
SQL>

Run 3 sessions in parallel in order to touch the same block several times and introduce latch contention.

declare
   n number;
begin
    for i in 1..1000000 loop
            select object_id
               into n
              from new_date
             where 1=1 --object_type='TABLE'
             and object_id =116 and rownum <2;
    end loop;
end;

Identify a high or rapid increasing wait count on the CACHE BUFFERS CHAINS latch.

SQL> select CHILD#  "cCHILD"
  2       ,      ADDR    "sADDR"
  3       ,      GETS    "sGETS"
  4       ,      MISSES  "sMISSES"
  5       ,      SLEEPS  "sSLEEPS"
  6       from v$latch_children
  7       where name = 'cache buffers chains'
  8       and sleeps !=0
  9       order by 5, 1, 2, 3;

    cCHILD sADDR                 sGETS    sMISSES    sSLEEPS
---------- ---------------- ---------- ---------- ----------
      3421 000007FF309E0200   25923210     974760          1
       889 000007FF30FF4028   51849242    2044527          3

Run the above query a few times to establish the id(ADDR) that has the most
consistent amount of sleeps. Once the id(ADDR) with the highest sleep count is found
then this latch address can be used to get more details about the blocks
currently in the buffer cache protected by this latch.
The query below should be run just after determining the ADDR with
the highest sleep count.


SQL>      column segment_name format a35
SQL>      select /*+ RULE */
  2         e.owner ||'.'|| e.segment_name  segment_name,
  3         e.extent_id  extent#,
  4         x.dbablk - e.block_id + 1  block#,
  5         x.tch,
  6         l.child#
  7       from
  8         sys.v$latch_children  l,
  9         sys.x$bh  x,
 10         sys.dba_extents  e
 11       where
 12         x.hladdr  = '&ADDR' and
 13         e.file_id = x.file# and
 14         x.hladdr = l.addr and
 15         x.dbablk between e.block_id and e.block_id + e.blocks -1
 16         and x.tch>10
 17       order by x.tch desc ;
Enter value for addr: 000007FF30FF4028
old  12:        x.hladdr  = '&ADDR' and
new  12:        x.hladdr  = '000007FF30FF4028' and

SEGMENT_NAME                           EXTENT#     BLOCK#        TCH     CHILD#
----------------------------------- ---------- ---------- ---------- ----------
HORIA.NEW_DATE                               0          3        156        889

Depending on the TCH column (The number of times the block is hit by a SQL
statement), you can identify a hot block. The higher the value of the TCH column,
the more frequent the block is accessed by SQL statements.