Hot Blocks (And Candidates To Hot Blocks)

A hot blocks happens when multiple sessions access one or more blocks that are protected by the same child cache buffers chains latch. These is the typical queries to see the hot blocks in a given time or in real time, based on the cache buffers chains latchs.
 

 SELECT inst_id, CHILD, ADDR, GETS, MISSES, SLEEPS
FROM (select inst_id, CHILD#  CHILD, ADDR, GETS, MISSES, SLEEPS
      from gv$latch_children
      where name = ‘cache buffers chains’
      order by 6 desc)
WHERE ROWNUM < 11;

select /*+ RULE */
  e.owner||’.'||e.segment_name  segmento, e.extent_id  extent#, x.dbablk - e.block_id + 1  block#, x.tch hits, l.child#
from
  sys.gv$latch_children  l, sys.x$bh  x, sys.dba_extents  e
where
  x.hladdr  IN (SELECT ADDR
                FROM (select ADDR, SLEEPS
                      from gv$latch_children
                      where name = 'cache buffers chains'
                      order by 2 desc)
                WHERE ROWNUM < 11) 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 ;

 

Other way different from above and can serve to identify cache buffer chains waits for latch, but it would identified waits in blocks that are accessed very repeatedly (and can eventually become in hot blocks) is the lower and is based on the ASH.

 

 -- May return many rows, depends on you retetion policy
SELECT CURRENT_BLOCK#, CURRENT_FILE#, CURRENT_OBJ#, COUNT (*) 
FROM DBA_HIST_ACTIVE_SESS_HISTORY 
GROUP BY CURRENT_BLOCK#, CURRENT_FILE#, CURRENT_OBJ# 
ORDER BY 4 DESC 3 ASC 

 

The current_obj # corresponds to the object_id in dba_objects;

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s