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

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

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.

Do you know whether the buffer busy is caused due to lot of INSERTS or lot of 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. o If the hot object is already partitioned - revisit the partitioning scheme, so that concurrent DML goes to different partitions.

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 - 13:03:42 CST