Re: latch wait - cache buffer chain

The condition "hladdr='ADDR'" is not correct.

Use this sql:

   select  /*+ first_rows */ hladdr,file#,dbablk,tch from x$bh where hladdr
in (select distinct p1raw from v\$session_wait where event='latch free'
          and p2=98) and tch>10;

You can find the file# and block_id (maybe not one blocks) which are on the
chain.

On 10/25/06, genegurevich@xxxxxxxxxxxxxxxxxxxxx <
genegurevich@xxxxxxxxxxxxxxxxxxxxx> wrote:


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


--
http://www.freelists.org/webpage/oracle-l





--
Eagle Fan

Oracle DBA

Other related posts: