Tuning Global Cache - Expert Oracle RAC Performance Diagnostics and Tuning - page 423

FROM gv$latch_children
WHERE NAME = 'cache buffers chains'
AND sleeps > 100
ORDER BY 1,
6,
2,
3;
INT Child Address Gets Misses Sleeps
----- -------- ---------------- -------- -------- --------
1 175974 00000011244E9830 9385329 2858981 12759
25761 0000001123684E48 5943627 2576659 125958
13962 0000001123570C50 11049054 7048742 394381
251077 0000001124C02A68 1.32E+08 72061090 2760136
2 227816 0000001124198588 28054003 2488138 14060
25761 00000010E3726B58 4713115 2018104 103497
13962 00000010E3612960 11301142 8143583 477962
251077 00000010E4CA4778 1.17E+08 69463081 3243099
3 43955 00000010E30BB9E8 1542331 913767 11602
13962 0000001123570C50 11098333 7682783 559700
251077 0000001124C02A68 1.31E+08 67702851 2046647
4 160316 0000001123B2A728 21587857 1659464 12831
25761 00000010E3726B58 8824900 3082195 118113
13962 00000010E3612960 45018900 31817473 2199646
251077 00000010E4CA4778 1.36E+08 74955924 3107749
Step 2
From Step 1, determine the LATCH_ID or address that has the highest sleep counts. For that address, determine the
objects and the number of times that the object has been touched using the following query:
Script: MVRACPDnTap_seglatchstats.sql
SELECT 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 x.hladdr = '0000001123570C50'
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;