Help - Search - Members - Calendar
Full Version: Buffer waits in tablespace IO stats section
Oracle DBA Forums > Oracle > Oracle Forum
serenaender
Hi,

A simple question.. Which is a good threshold value for the Buffer Waits and Av Buf Wt (ms) fields in Tablespace IO Stats section from AWR report?

Thanks
SteveC
Zero.

http://www.dba-oracle.com/m_buffer_waits.htm
burleson
Hi,

>> Which is a good threshold value for the Buffer Waits and Av Buf Wt (ms) fields in Tablespace IO Stats section from AWR report?

Good question!

On a system with low DML (update) activity, there should be no buffer waits.

Consider this sample below:


CODE
Tablespace IO Stats for DB: DIA2  Instance: dia2  Snaps: 1 -2
->ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
                 Av      Av     Av                    Av        Buffer Av Buf
         Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SYSTEM
            20       0    2.5     1.0           71        0          0    0.0
PERFSTAT
             1       0    0.0     1.0           32        0          0    0.0
UNDOTBS
             1       0   10.0     1.0           29        0          0    0.0
CWMLITE
             1       0    0.0     1.0            0        0          0    0.0


Sometimes, high buffer waits can occur when the db_cache_size is too small. This DBA reports that increasing the size of the data buffer cache relieved buffer waits as reported in the STATSPACK "Tablespace IO Stats" section:

http://www.orafaq.com/usenet/comp.database.../03/07/0629.htm

QUOTE
I recently added 600M to buffer cache because of disk i/o issues.

Before this statspack was reporting very high i/o wait times (> 2000 buffer waits in the tablespace io stats section). iostat was reporting high awaits. Tablespaces were moved off hot disks, tuned sql and I did the standard DBA stuff.

To make a long story short, I figured out that there is a controller problem because it only is able to sustain a max i/o rate of 10M/sec when it should be 80M/sec.

So as a last resort I increased the buffer cache by 600M. Then statspack reported great io times with buffer waits < 10. I checked and rechecked. The extra 600M took a huge load off the disks. Buffer busy waits remained fine.


There are notes on choosing the optimal db_cache_size:

http://www.dba-oracle.com/art_builder_buffers.htm
serenaender
Thanks for the answer.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.