Waits, Enqueues, and Latches - Expert Oracle RAC Performance Diagnostics and Tuning - page 577

Inst Latch Level Child Address Name of Latch Gets Misses Sleeps
---- ------ ------ ------- ---------------- ------------------------ ---------- -------- ------
1 203 1 306 00000000A51B4058 cache buffers chains 3716256 3946 510
203 1 5152 00000000A54F32D8 cache buffers chains 33185081 1794 34
203 1 7302 00000000A5667F58 cache buffers chains 6391335 24320 11702
203 1 7478 00000000A5684358 cache buffers chains 36464190 13925 13
From the preceding output, Address 00000000A5667F58 has the highest miss and sleep rates. We now try to
understand what hot objects in memory are causing these high misses and sleeps.
Step 3
Using the address from Step 2, we can query the X$BH table, joining with DBA_EXTENTS and V$LATCH_CHILDREN to get
the hot objects in buffer.
Script: MVRACPDnTap_HotSegments.sql 4
SELECT /*+ ordered */ e.owner
||'.'
|| e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
FROM sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
WHERE l.name = 'cache buffers chains'
AND l.sleeps > &sleep_count
AND x.hladdr = l.addr
AND e.file_id = x.file#
AND x.dbablk BETWEEN e.block_id AND e.block_id + e.blocks - 1;
SEGMENT_NAME EXTENT# BLOCK# TCH CHILD#
----------------------------------- -------- -------- -------- --------
RAPUSR.STOCK 77 5 157 7302
RAPUSR.CUSTOMER 81 26 44 7302
SYS._SYSSMU14_4287170308$ 2 68 8 7302
SYS.C_OBJ# 21 110 7 7302
SYS._SYSSMU14_4287170308$ 7 54 7 7302
RAPUSR.CUSTOMER 63 40 3 7302
RAPUSR.WAREHOUSE 1 1 1 7302
RAPUSR.WAREHOUSE 1 1 1 7302
XDB.SYS_IL0000018294C00007$$ 0 3 1 7302
XDB.SYS_IL0000018294C00007$$ 0 3 1 7302
SYS.I_JIJOIN$ 0 1 1 7302
SYS.I_JIJOIN$ 0 1 1 7302
4 Script source is Oracle Wait Interface: A Practical Guide To Performance Diagnostics & Tuning (2004, Oracle Press) by
Richmond Shee, Kirtikumar Deshpande, and K. Gopalakrishnan.