Re: why buffer busy waits/latch free/buffes chains on selects?

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..
>
Received on Sun Nov 11 2001 - 17:46:52 CST