RE: [QUAR] Re: latch wait - cache buffer chain

Mark, it is Oracle 9204. I'll look into the outlines

thank you

Gene Gurevich



                                                                           
             "Bobak, Mark"                                                 
             <Mark.Bobak@xxxxx                                             
             oquest.com>                                                To 
             Sent by:                  <genegurevich@xxxxxxxxxxxxxxxxxxxxx 
             oracle-l-bounce@f         >, <oracle-l@xxxxxxxxxxxxx>         
             reelists.org                                               cc 
                                                                           
                                                                   Subject 
             10/24/2006 06:15          RE: [QUAR] Re: latch wait - cache   
             PM                        buffer chain                        
                                                                           
                                                                           
             Please respond to                                             
             Mark.Bobak@xxxxxx                                             
                 quest.com                                                 
                                                                           
                                                                           




What version of Oracle?  In 10g, there are SQL Profiles.  In previous
versions, stored outlines...one of them may do the trick.


--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

There is nothing so useless as doing efficiently that which shouldn't be
done at all.  -Peter F. Drucker, 1909-2005


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
genegurevich@xxxxxxxxxxxxxxxxxxxxx
Sent: Tuesday, October 24, 2006 6:21 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: [QUAR] Re: latch wait - cache buffer chain
Importance: Low

Well,  I have executed the same SQL without the index (supressed it by
adding a +0 to a where clause) and it completed in 32 minutes. Still
nothing to write home about  , but better than it was. However the
report is created by a reporting tool and I can't manipulate the SQL. Is
there a way to rebuild that index or change some parameters based on my
findings so that it does not slow down my SQL?

Thanks for any input

thank you

Gene Gurevich
Oracle Engineering
224-405-4079




             genegurevich@disc

             overfinancial.com

             Sent by:
To
             oracle-l-bounce@f         oracle-l@xxxxxxxxxxxxx

             reelists.org
cc
                                       frits.hoogland@xxxxxxxxx, "goran

                                       bogdanovic" <goran00@xxxxxxxxx>

             10/24/2006 05:01
Subject
             PM                        Re: latch wait - cache buffer
chain




             Please respond to

             genegurevich@disc

             overfinancial.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@xxxxxxxx
To
             m>
genegurevich@xxxxxxxxxxxxxxxxxxxxx

cc
             10/24/2006 03:33          frits.hoogland@xxxxxxxxx,
             PM                        oracle-l@xxxxxxxxxxxxx

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@xxxxxxxxx> 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@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






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






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


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






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

Other related posts: