Re: latch wait - cache buffer chain

From: <genegurevich_at_discoverfinancial.com>
Date: Tue, 24 Oct 2006 17:01:27 -0500
Message-ID: <OF96BB025E.2FAA1158-ON86257211.0078AD2B-86257211.0078F9F3@discoverfinancial.com>

Thank you. Goran and Fritz.

I have now identified an index with a number of "hot" extents. I noticed that all the extents in the output of my SQL have the same child latch #. I am not sure whether this is OK. I am now trying to rerun my SQL without the index that was on the hot list to see if that helps. This index is parallelized and the SQL is executed with 8 readers and 8 writers. Should I change the value of freelist of my index based on that?

thank you

Gene Gurevich

                                                                           
             "goran                                                        
             bogdanovic"                                                   
             <goran00_at_gmail.co                                          To 
             m>                        genegurevich_at_discoverfinancial.com  
                                                                        cc 
             10/24/2006 03:33          frits.hoogland_at_gmail.com,           
             PM                        oracle-l_at_freelists.org              
                                                                   Subject 
                                       Re: latch wait - cache buffer chain 
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           

take the p1raw from the v$session_wait for waiting session and this is your hladdr in the query you are looking for.

On 10/24/06, Frits Hoogland < frits.hoogland_at_gmail.com> wrote:   You should find the hottest child latch yourself, and swap the value of   ADDR on line 12 with the value of hladdr of the child latch.

  if you execute this query, no rows should come up, because the chance of   having an hladdr = 'ADDR' in x$bh is fairly low.

  cheers!

  frits

  On 10/24/06, genegurevich_at_discoverfinancial.com <   genegurevich_at_discoverfinancial.com> 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

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

Received on Tue Oct 24 2006 - 17:01:27 CDT