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