Re: latch wait - cache buffer chain
- From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 24 Oct 2006 17:20:47 -0500
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
Other related posts:
