Path: news.easynews.com!easynews!hub1.nntpserver.com!nntp-relay.ihug.net!ihug.co.nz!out.nntp.be!propagator-SanJose!in.nntp.be!news-in-sanjose!newshub2.rdc1.sfba.home.com!news.home.com!news1.rdc1.ne.home.com.POSTED!not-for-mail
Message-ID: <3BEF0E2C.4E3A02C4@more.net>
From: Ricky Sanchez <rsanchez@more.net>
X-Mailer: Mozilla 4.75 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.server
Subject: Re: why buffer busy waits/latch free/buffes chains on selects?
References: <3be94c85.666573632@news.globix.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 36
Date: Sun, 11 Nov 2001 23:46:52 GMT
NNTP-Posting-Host: 24.183.193.31
X-Complaints-To: abuse@home.net
X-Trace: news1.rdc1.ne.home.com 1005522412 24.183.193.31 (Sun, 11 Nov 2001 15:46:52 PST)
NNTP-Posting-Date: Sun, 11 Nov 2001 15:46:52 PST
Organization: Excite@Home - The Leader in Broadband http://home.com/faster
Xref: easynews comp.databases.oracle.server:124609
X-Received-Date: Mon, 12 Nov 2001 02:36:29 MST (news.easynews.com)

NetComrade-

I think you need to be looking in v$latch_children for the actual child
latch of concern. It doesn't really matter much though, if the same
latch address keeps showing up for 'cache buffers chains', then you
probably have a hot block and the buffer busy wait details will tell you
which one. Use the p1 and p2 parameters as file# and block# to find the
object in question:

select segment_name, segment_type, owner, tablespace_name
from sys.dba_extents
where file_id = <p1 val>
and <p2 val> between block_id and (block_id + (blocks -1))
/

This will tell you the precise object and object type in question.
Consider the sql statement in question and figure out what is keeping
that block busy. If it is the object being inserted, then consider if it
is a table or an index.

For a table, you might want to increase freelists, perhaps even recreate
the object with several freelist groups. If an index, you may want to
consider a reverse key index or some non-consecutive key assignment
technique.

For additional diagnostic detail, you can set the 10046 event, level 12
in a session and run the query from that session. Review the resulting
trace file and see where the waits fall and on what objects.

- ricky


NetComrade wrote:
> 
> I was running a big insert yesterday on a fairly large table..
>