Find the hot block (OS) in Oracle
SET LINESIZE 200
SET VERIFY OFF
2 FROM (SELECT name,
3 addr,
4 gets,
5 misses,
6 sleeps
7 FROM v$latch_children
8 WHERE name = 'cache buffers chains'
9 AND misses > 0
10 ORDER BY misses DESC)
11 WHERE rownum < 11;
NAME ADDR GETS MISSES SLEEPS
---------------------------------------------------------------- ---------------- ---------- ---------- ----------
cache buffers chains 000000017CE588F0 251779 68962 20107
cache buffers chains 000000017CF1D6F0 228526 4117 1449
cache buffers chains 000000017684E400 5670108 2215 1
cache buffers chains 00000001767A0AA8 1887009 1643 266
cache buffers chains 000000017665F220 2184421 796 0
cache buffers chains 0000000174103738 3473384 695 0
cache buffers chains 000000017CFEDD50 89694 629 0
cache buffers chains 0000000176773288 307181 472 0
cache buffers chains 000000017681F3A8 430067 471 0
cache buffers chains 00000001768F4760 90863 296 0
10 rows selected.
SQL> SELECT *
2 FROM (SELECT o.owner,
3 o.object_name,
4 o.subobject_name,
5 bh.tch,
6 bh.obj,
7 bh.file#,
8 bh.dbablk,
9 bh.class,
10 bh.state
11 FROM x$bh bh,
12 dba_objects o
13 WHERE o.data_object_id = bh.obj
14 AND hladdr = '000000017681F3A8'
15 ORDER BY tch DESC)
16 WHERE rownum < 11;
OWNER OBJECT_NAME SUBOBJECT_NAME TCH OBJ FILE# DBABLK CLASS STATE
--------------- ------------------------------ -------------------- ---------- ---------- ---------- ---------- ---------- ----------
SYS I_USER1 82 46 1 417 1 1
STLBAS STSBSDTL 12 76715 20 553084 1 1
STLBAS PK_MV_STFTRSUM 1 78695 24 636349 1 1
STLBAS PK_MV_STFTRSUM 1 78695 24 671286 1 1
STLBAS STTRNDTL 0 76732 19 436191 1 1
STLBAS STTRNDTL 0 76732 19 1283588 1 1
STLBAS STTRNDTL 0 76732 19 1353462 1 1
STLBAS STTRNDTL 0 76732 19 1388399 1 1
8 rows selected.
SQL>
