latch wait - cache buffer chain
- From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 24 Oct 2006 14:19:39 -0500
He everybody:
I am trying to tune a report which has been running for some time and has
been timing out since last week.
When I execute that report I see a large number of "latch free" wait events
with p2 parameter equal to 98.
Based on v$latch that is a cache buffer chain latch. One of the reasons
for this event can be a hot block.
I have found the following query on the metalink to locate a hot block:
1 select /*+ RULE */
2 e.owner ||'.'|| e.segment_name segment_name,
3 e.extent_id extent#,
4 x.dbablk - e.block_id + 1 block#,
5 x.tch,
6 l.child#
7 from
8 sys.v$latch_children l,
9 sys.x$bh x,
10 sys.dba_extents e
11 where
12 x.hladdr = 'ADDR' and
13 e.file_id = x.file# and
14 x.hladdr = l.addr and
15 x.dbablk between e.block_id and e.block_id + e.blocks -1
16* order by x.tch desc
but it did not return anything. Is there anything else I need to look at
related to this latch?
Thanks for any insight
thank you
Gene Gurevich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
