Re: AWR report- what does this metric mean ?

From: John Clarke <john.clarke_at_centroid.com>
Date: Sun, 23 Oct 2011 21:41:44 -0400
Message-ID: <CACA3997.56E9C%john.clarke_at_centroid.com>

Pretty sure it means Average Buffer Wait, as in "buffer busy waits". And this doesn't necessarily directly mean slow IO, it could be buffer contention due to lots of concurrent DML on the same blocks. The p3 value in v$session_wait would shed some light on this if you happen to catch it "in action". Take a look at the segment stats further down in the report for buffer busy waits and see if you can map specific segments to the XXDX01 tablespace ... that's where I'd start. Then you might also be able to map this back to specific SQL statements in the SQL section(s), in an indirect sort of way.

• John

From: "CRISLER, JON A" <JC1706_at_att.com<mailto:JC1706_at_att.com>> Reply-To: "JC1706_at_att.com<mailto:JC1706_at_att.com>" <JC1706_at_att.com<mailto:JC1706_at_att.com>> Date: Sun, 23 Oct 2011 21:34:21 -0400
To: Oracle-L Freelists <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: AWR report- what does this metric mean ?

I have a SR opened with Oracle, but I am struggling to determine what this metric in AWR means-

Av Buf Wt(ms) - I cannot tell if this means Average Buffer Wait(ms), or Average Buffer Write(ms). In the Context of the earlier "Av Rd(ms)" it makes me think that its Average Buffer Write, but how can I get such a Wide range between reads and writes? This is on a very high end NetApp san storage. The AWR report for disk i/o does not match the SAN statistics at all- we get sub-5 ms i/o's reported on the SAN side, but on the OS side I do see some above average times under i/o stat, but still sub-15ms times when it gets ugly.

Tablespace IO Stats DB/Inst: xxxx/xxxxxx Snaps: 26941-26987
-> 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)

-------------- ------- ------ ------- ------------ -------- ---------- ------ XXXDAT01
       143,639 3 2.9 1.7 985,708 24 26,753 43.5 XXXDX01
        13,492 0 4.2 1.0 946,883 23 708 145.5 UNDOTBS6
        61,740 1 3.8 1.0 51,146 1 1,079 3.0 XXXLOB01
        47,916 1 6.1 1.0 37,402 1 13,511 4.8 SYSAUX
         5,221 0 2.6 1.0 20,370 0 0 0.0

Some parameters that might influence this-

db_block_checking             MEDIUM
db_block_checksum             TYPICAL
db_block_size                 8192

db_lost_write_protect         TYPICAL

This is 11gR1 RAC to a Dataguard 11gR1 RAC setup, x64 RedHat Linux 5

--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l

Received on Sun Oct 23 2011 - 20:41:44 CDT