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