Re: Data block and buffer busy quite high, suggestions?

> I don't know if the number is large or not, but the instance spends a
> lot if time waiting for data blocks even if the avg. load is rather low,
> IMHO. So I'd like to get rid of all this waiting, if possible.

It's hard to tune an application bottleneck by looking at an instance-level view. Just because your entire instance is experiencing a large number of waits on this particular event doesn't necessarily mean that those waits are contributing to the application bottleneck. They are certainly contributing to a bottleneck, somewhere, but how can you be sure where?  

> First I monitored the changes in the output from v$waitstat, which
> showed me that the instance does lots of data block waits. Then I looked
> at v$session_wait for some time to find the objects for which the buffer
> busy waits occur, as well as the reason code. That gave me enough data
> to use dba_segments to identify a couple of tables as well as the
> rollback segments as the source of the 'buffer busy waits' events.
>
> The approach I used is more or less from this article:
> http://builder.com.com/5102-6388-5110875.html

Then I'd take the author's (D. Burleson) approach to heart, "The main way to reduce buffer busy waits is to reduce the total I/O on the system. This can be done by tuning the SQL to access rows with fewer block reads." If your application needs less blocks to satisfy your SQL statement, then there will be less blocks to wait on. When that application is run with a high degree concurrency, the frequency of the 'buffer busy wiat' will be lessened since you aren't relying on as many data blocks.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"