Re: BUFFER BUSY WAITS

From: Andrew Mobbs <andrewm_at_chiark.greenend.org.uk>
Date: 21 Feb 2002 15:44:17 +0000 (GMT)
Message-ID: <vpv*0vuhp@news.chiark.greenend.org.uk>

Ganesh Raja <ganesh_at_gtfs-gulf.com> wrote:
>Buffer Busy Waits Can Occur at Two places one is the segment header and another
>the data Level. If the Buffer Busy Waits ocur at the Segment Header then there
>is contention for the Freelist. If it occurs at the Row level then the server
>process is waiting for another server process to rel the buffer. This value is
>also directly prpoytional to the Free Buffers Scanned.. A Increasing Value
>should always take you towrads tuning the Buffer Cache.

A few more than two places, e.g. in 8.1.7 -

SQL> SELECT class FROM v$waitstat;

CLASS

---

data block
sort block
save undo block
segment header
save undo header
free list
extent map
bitmap block
bitmap index block
unused
system undo header
system undo block
undo header
undo block

14 rows selected.

(13 discounting the "unused")

There's no such thing as a row-level buffer busy wait in Oracle, it's always block level, as the name suggests. A wait for a row level lock is an enqueue.

A buffer busy wait is always when one session has a buffer locked and another session wishes to access that buffer. The first session is modifying that buffer for some reason; possibly reading a block off disk into that buffer, or updating that buffer.

>Make the Buffer Cache Big and also try to cache your tables so that they are not
>thrown out after a FTS.

On a FTS, only db_file_multiblock_read_count blocks will be stored, at the LRU end of the buffer cache chain (unless you have a CACHE hint). This means that the buffer cache is not swamped by a FTS.

If you think you know better than Oracle which objects are best kept in cache, create a suitable sized KEEP pool and put those objects into that pool.

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/

Received on Thu Feb 21 2002 - 09:44:17 CST