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

Brian Peasland wrote:

>>Thanks in advance for all hints, pointers or suggestions to why my "data
>>block" wait count is so large.

Good questions.

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.

Yesterday the instance, over a period of 20 hours, waited 12.000 times for data blocks, for a total of 1.4 million centiseconds. Large numbers or small, that's still a lot of waiting ;-)

>
> In a similar line of questioning... It's obvious that you've got a
> performance problem. But why does your performance problem have to be
> the data block waits or the buffer busy waits? What led you down the
> road as these being the culprits?

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

  Are you sure that it isn't something
> else?

Oh, we have lots of other issues as well. ;-) Stuff like bad algorithms, redundant indexes, suboptimal disk usage, too few rollback segments, too small connnection pools at the appservers And More.

I'm working on a plan to change all this and wanted to sanity check some of my own assumptions.

>
> The track that I would take would be to try to isolate the exact part of
> the application that is experiencing the problem. Then, take a look at
> exactly what that part of the application is trying to do along with
> what that part of the application is spending its time waiting for. This
> typically means a 10046 trace, level 8 or 12. You may find that the
> application is waiting on the 'buffer busy waits' event. Or, you may
> find that it is something else, in which case, this exercise (thread)
> isn't helping your problem. But unless you look at the problem spot in
> isolation, you could be clouding the issue with extraneous data that is
> not directly contributing to your end user's pain.
>
> HTH,

 > Brian

It does help. Thanks.

Bjørn Received on Thu Jan 22 2004 - 03:02:35 CST