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

From: Bjørn Augestad <boa_at_metasystems.no.spam.to.me>
Date: Wed, 21 Jan 2004 20:31:58 GMT
Message-ID: <2lBPb.31060$BD3.6837538@juliett.dax.net>

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
>>

-- 
The worlds fastest web server is now available
at http://highlander.metasystems.no:2000. Enjoy!

Received on Wed Jan 21 2004 - 14:31:58 CST