explain statspack values for tablespace & file IO
PktAcesSep 18 2008 — edited Sep 24 200810.2.0.2 aix 5.2 64bit
in the Tablespace IO Stats & File IO Stats section of statspack and awr reports can someone help to clear up a little confusion I have with the value for AV Reads/s & AV Rd(ms). I'll reference some values I have from one of my reports over a 1 hour snap shot period, with the first three columns being reads, av rd/s, av rd(ms) respectively for both sections.
For Tablespace IO I have the following.
PRODGLDTAI
466,879 130 3.9 1.0 8,443 2 0 0.0
For File IO I have the following for each file within this tablespace.
PRODGLDTAI /jdb10/oradata/jde/b7333/prodgldtai04.dbf
113,530 32 2.6 1.0 1,302 0 0 0.0
PRODGLDTAI /jdb14/oradata/jde/b7333/prodgldtai03.dbf
107,878 30 1.6 1.0 1,898 1 0 0.0
PRODGLDTAI /jdb5/oradata/jde/b7333/prodgldtai01.dbf
114,234 32 5.8 1.0 2,834 1 0 0.0
PRODGLDTAI /jdb5/oradata/jde/b7333/prodgldtai02.dbf
131,237 36 5.2 1.0 2,409 1 0 0.0
From this I can calculate that there were on average 129.68 reads every second for the tablespace and that matches what is listed. But where does the av rd(ms) come from? If there are 1000 milli-seconds in a second and there were 130 reads per second, doesn't that work out to 7.6 ms per read?
What exactly is av rd(ms)? Is it how many milli-seconds it takes on average for 1 read? I've read in the Oracle Performance Tuning doc that it shouldn't be higher than 20. What exactly is this statistic? Also, we are currently looking at the purchase of a SAN and we were told that value shouldn't be above 10, is that just a matter of opinion? Would these values be kind of useless on tablespaces and datafiles that aren't very active over an hours period of time?
Edited by: PktAces on Sep 22, 2008 8:31 AM