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 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l