Disk I/O contention

[mike73]

How do you find out if your database having problem with disk I/O contention.

any advise are welcomed.

[marist89]

First, you have to see how busy your physical disks are. In Solaris, you would use iostat.

Then, check what your I/O activity is on your different mount points by querying v$filestat with something like:

col "% I/O" for 999.00
select substr(df.name,1,11) mount, sum(fs.PHYBLKRD) "Reads", sum(fs.PHYBLKWRT) "Writes",
sum(fs.PHYBLKRD+fs.PHYBLKWRT) "Total IO", (sum(fs.PHYBLKRD+fs.PHYBLKWRT)/t1.total_io)*100 "% I/O"
from v$filestat fs, v$datafile df, (select sum(PHYBLKRD+PHYBLKWRT) total_io from v$filestat) t1
where df.file# = fs.file#
group by substr(df.name,1,11), t1.total_io
order by "% I/O" desc
/

[mike73]

Thanks Jeff

What does it mean if you have a negative % I/O ???

[marist89]

I'm not aware you can have a negative % I/O. Post the query results here...

[Mr.Hanky]

Hey Jeff, I am trying to massage this query by playing with the, substr(df.name,1,11) since we have long datafile dirs. When I do this I get more results. I am trying to eliminate % I/O > .00 but having no luck, any suggestions.

I tried adding
having (sum(fs.PHYBLKRD+fs.PHYBLKWRT)/t1.total_io)*100 <> .00
but this did nothing.
MH

[mike73]

Hi Jeff,

here it is, can you give some explanation about this result, what can be done to improve it ???

Thanks

/u03/oradat 3423505683 1971014 3425476697 83.85578528420575769797392190079299745069
/u02/oradat 765089351 2950338 768039689 18.80163753761252539455277074004142396751
/u06/oradat 85776345 1330262 87106607 2.13237268255971856139432937770210398757
/u05/oradat 55947691 3011420 58959111 1.4433210294186548993925177288847822294
/u01/oradat 44936062 746766 45682828 1.11831717299325208211341062953941314351
/u04/oradat -327207628 26904378 -300303250 -7.35143370678990863542695037696072077868

[marist89]

This is probably a better script as it gets the I/O over a 10 second period:

col name for a50
col pct_io for 999.00
set linesize 132
set pages 666
-- drop temporary table
drop table jh$filestats;
create table jh$filestats as 
select file#, PHYBLKRD, PHYBLKWRT
from v$filestat;
prompt Waiting......
exec dbms_lock.sleep(10);
select substr(df.name,1,11) mount, sum(fs.PHYBLKRD) - sum(t.PHYBLKRD) "Reads",
   sum(fs.PHYBLKWRT) - sum(t.PHYBLKWRT) "Writes",
sum(fs.PHYBLKRD+fs.PHYBLKWRT) - sum(t.PHYBLKRD+t.PHYBLKWRT) "Total IO",
((sum(fs.PHYBLKRD+fs.PHYBLKWRT) - sum(t.PHYBLKRD+t.PHYBLKWRT))/x.totio)*100 pct_io
from v$filestat fs, v$datafile df, jh$filestats t,
   (select sum(a.PHYBLKRD+a.PHYBLKWRT) - sum(b.PHYBLKRD+b.PHYBLKWRT) totio
   from v$filestat a, jh$filestats b
   where a.file#=b.file#) x
where df.file# = fs.file#
and t.file# = fs.file#
group by substr(df.name,1,5), x.totio
order by "Total IO" desc
/

Example output from this query is:

SQL> @c:\scripts\snap_mountio

Table dropped.


Table created.

Waiting......

PL/SQL procedure successfully completed.


MOUNT            Reads     Writes   Total IO  PCT_IO
----------- ---------- ---------- ---------- -------
E:\ORACLE\O          1          1          2  100.00
D:\ORACLE\O          0          0          0     .00

This tells me that over the last 10 seconds, my E: drive got 100% of my I/O activity and my D: drive got none. In a production system, I would expect the I/O to be distributed evenly between the two devices.

[Edited by marist89 on 08-28-2001 at 11:37 AM]

[marist89]

Originally posted by mike73
Hi Jeff,

here it is, can you give some explanation about this result, what can be done to improve it ???

I'm not sure what you mean by improve it...

I ran this query on a test system and got the following results:

SQL> @c:\scripts\tune_io_mount

MOUNT            Reads     Writes   Total IO   % I/O
----------- ---------- ---------- ---------- -------
E:\ORACLE\O        570        122        692   99.14
D:\ORACLE\O          4          2          6     .86

This means that 99.14% of my I/O was going to my E: drive and less than 1% was going to my D: drive. (Which is OK, since this is just a test system.) On a production system, I would expect the I/O to be relatively evenly spread out on all my physical devices.

I'm not sure how you got the negative numbers...

[marist89]

Also, I have scripts that break everything down to the file level instead of the mount point. Just send mail to marist89@excite.com with the either of the subject headings:
SCRIPT: tune_io_file.sql
SCRIPT: snap_fileio.sql

[Mr.Hanky]

Jeff; There was an error with that last script, I changed line 11 to match line 1
FROM group by substr(df.name,1,5), x.totio

TO group by substr(df.name,1,11), x.totio

I think the SUBSTR number have to match, no?
MH