Identify excessively read blocks (hot blocks)

Use the following query to identify which files the hot blocks occur in;

select p1 "file#"
      ,p2 "block#"
      ,p3 "class#"
from   v$session_wait
where  event = 'read by other session'
/

Once the above query has been run several times over, a pattern may emerge which highlights a specific range of blocks.

Then run the following SQL statement to identify which object(s) the blocks are associated with;

select relative_fno
      ,owner
      ,segment_name
      ,segment_type
from   dba_extents

-- the file parameter comes from the result set above
where  file_id      = &file

-- the block parameter comes from the result set above
and    &block       between block_id and block_id + blocks - 1
/