V$WAITSTAT, FREELISTS and BUFFER BUSY WAITS

From: P.Merks <pmerks_at_ibm.net>
Date: 1998/05/15
Message-ID: <01bd8024$9874a080$c11ebeb4@ispmer.northwest.ca>#1/1

I have a 'couple' of oracle databases that are reporting very high buffer busy waits and when I select from v$waitstat I get (what I think) are very high numbers for data_block indicating block contention.

On one of the databases, knowing the application it was very obvious which table was the source of the contention ie which table had the most simultaneous updates and inserts of data. For this I was able to increase the freelists just for the indexes and this alone has made a HUGE difference for some jobs. For the other database I have no idea which tables or index blocks are experiencing the most contention.

My question: How can I correlate high values for data_block in v$waitstat or high buffer busy waits to a specific object. The documenation I have seen refers me to v$session_wait but I don't see an obvious correlation.

thanks,

P. Merks
Winnipeg, Manitoba
Canada Received on Fri May 15 1998 - 00:00:00 CDT