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

In my understanding, 'buffer busy waits' can be both 'read-read' contention and 'read-write' contention. It is read-read contention when a lot of sessions wants to read a particular block from the disk at the same time, one sessoin waits for the actual IO wait and every one else queues up behind him on a 'buffer busy wait'.

What are your top wait events? First 3 pages of statspack report would help.

You said, the top SQL types affected by buffer busy aren't INSERTS. Do you know, if they are UPDATES/DELETES or SELECTS?

Regards,
-V

on 1/21/2004 12:31 PM Bjørn Augestad said the following:

> Venkat wrote:
>
>>
>> Do you know which objects are affected by this buffer busy
>> contention? [You can use dba_segments and dba_extents to find which
>> object a given file# and a block# belongs to].
>
>
> Looks as if most of the waits come from two tables.
>
>>
>> o If the hot object is a table or a table partition - try rebuilding
>> the object with a higher PCTFREE so that you reduce the number of
>> rows per block.
>
>
> That was (part of) the plan, then I found out that one of the tables
> has PCTFREE set to 73. That seems way too high, IMHO. PCTUSED is 23,
> btw.
>
> That table is part of a lot of queries and has crappy index values, so
> now I'm wondering if the current PCTFREE of 73 causes too much block
> I/O? I'll try to look into that tomorrow.
>
>>
>> Do you know whether the buffer busy is caused due to lot of INSERTS
>> or lot of UPDATES?
>
>
> It is not a lot of inserts on the tables, maybe 10-20 new rows each
> minute, so I guess it has to be updates.
>
>
>>
>> o If the hot object is an index and there are a lot of UPDATES - try
>> hash partitioning the INDEX.
>> o If the hot object is not present in a locally managed tablespace
>> with AUTO SSM, and there are a lot of INSERTS - try moving the object
>> to a different tablespace that is locally managed and has AUTO SSM,
>> or of course, 'make' this tablespace locally managed w/ AUTOSSM.
>
>
> Unfortunately this is version 8.0.5 and we cannot upgrade. :-(
>
>> o If the hot object is already partitioned - revisit the
>> partitioning scheme, so that concurrent DML goes to different
>> partitions.
>
>
> The thing is that there's only about a million rows in one table, and
> about 4.5 million in the other.
>
>
> Thanks a lot for your help so far.
> Bjørn
>
>>
>> Regards,
>> -V
>>
>>
>> on 1/20/2004 7:27 AM Bjorn Augestad said the following:
>>
>>> Hello,
>>>
>>> I have a rather slow 8.0.5 instance and am trying to figure out how
>>> to make it faster. Hopefully someone can point me in the right
>>> direction on why the data block numbers below are so high?
>>>
>>> After four and a half day running, v$waitstat reports this:
>>> CLASS COUNT TIME
>>> ------------------ ---------- ----------
>>> data block 31158 1583793
>>> sort block 0 0
>>> save undo block 0 0
>>> segment header 54 22
>>> save undo header 0 0
>>> free list 0 0
>>> extent map 0 0
>>> bitmap block 0 0
>>> bitmap index block 0 0
>>> unused 0 0
>>> system undo header 0 0
>>>
>>> CLASS COUNT TIME
>>> ------------------ ---------- ----------
>>> system undo block 0 0
>>> undo header 29648 1537
>>> undo block 675 271
>>>
>>>
>>> and v$system_event reports
>>>
>>> event TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
>>> --------------------- ----------- ----------- ------------
>>> buffer busy waits 61528 1585576 25.7699909
>>>
>>>
>>>
>>> The next query returns no rows, hopefully indicating that I don't
>>> have a free list contention problem?
>>> select s.segment_name, s.segment_type, s.freelists
>>> from dba_segments s, v$session_wait w
>>> where w.p1 = s.header_file
>>> and w.p2 = s.header_block
>>> and w.event = 'buffer busy waits'
>>> ;
>>>
>>>
>>> Thanks in advance for all hints, pointers or suggestions to why my
>>> "data block" wait count is so large.
>>>
>>> Bjørn Augestad
>>>
>
>
Received on Wed Jan 21 2004 - 15:38:13 CST