/* ckiostat.sql
show the data file mount points and their activity
*/
set serveroutput on size 10000

declare
v_fsname char(5);
v_fstrds number := 0;
v_fstwrts number := 0;
v_fsrds number := 0;
v_fswrts number := 0;
v_fsrpct number := 0.00;
v_fswpct number := 0.00;
v_fsriobytes number := 0.0;
v_fswiobytes number := 0.0;
v_totfsriobytes number := 0.0;
v_totfswiobytes number := 0.0;
v_rwopsratio number := 000.00;
v_rwbytesratio number := 000.00;
v_sampleon varchar2(20);
v_dbname varchar2(10);
v_server varchar2(20);
v_dbblksize number := 0;

cursor all_fs is
select distinct substr(name,8,(instr(name,'/',9,1) -8)) fsname
from v$datafile;

begin

-- get sample date --
v_sampleon := to_char(sysdate,'mm/dd/yy hh:mi:ssAM');

-- get overall physical reads and writes for percentage calculations
select sum(phyrds), sum(phywrts)
into v_fstrds , v_fstwrts
from v$filestat;

-- get other info
select d.name, s.machine, to_number(p.value)
into v_dbname, v_server, v_dbblksize
from v$database d, v$session s, v$parameter p
where s.sid = 1
and p.name = 'db_block_size';

-- provide general info
dbms_output.put_line('Sample taken on ' || v_sampleon);
dbms_output.put_line('Database:' || v_dbname || chr(9) || 'Server:' || v_server);
-- dbms_output.put_line('Total reads: ' || chr(9) || v_fstrds || chr(9) || ' Total writes: ' || chr(9) || v_fstwrts);
dbms_output.put_line('Mount' || chr(9) ||
'Reads' || chr(9) || 'MBytes' || chr(9) || 'ReadPct' || chr(9) ||
'Writes' || chr(9) || 'MBytes' || chr(9) || 'WrtPct');
dbms_output.put_line('-----' || chr(9) ||
'-------' || chr(9) || '-------' || chr(9) || '-------' || chr(9) ||
'-------' || chr(9) || '-------' || chr(9) || '-------');

-- process each filesystem mount point
for each_fs in all_fs
loop

-- initialize and setup variables for this loop iteration
v_fsname := each_fs.fsname;
v_fsrds := 0;
v_fswrts := 0;

-- get individual filesystem stats
select sum(f.phyrds), sum(f.phywrts),
round((sum(f.phyblkrd) * v_dbblksize /1024 / 1024),1),
round((sum(f.phyblkwrt) * v_dbblksize /1024 / 1024),1)
into v_fsrds, v_fswrts,
v_fsriobytes,
v_fswiobytes
from v$filestat f, v$datafile d
where f.file# = d.file#
and substr(d.name,8,(instr(d.name,'/',9,1) -8)) = v_fsname;

-- calculate this filesystem's pct of total reads and writes
v_fsrpct := round((v_fsrds / v_fstrds * 100),2);
v_fswpct := round((v_fswrts / v_fstwrts * 100),2);

-- accumulate MB reads and writes
v_totfsriobytes := v_totfsriobytes + v_fsriobytes;
v_totfswiobytes := v_totfswiobytes + v_fswiobytes;

-- output this filesystem's stats
dbms_output.put_line(v_fsname || chr(9) ||
lpad(v_fsrds,7,' ') || chr(9) ||
lpad(v_fsriobytes,7,' ') || chr(9) ||
lpad(v_fsrpct,6,' ') || chr(9) ||
lpad(v_fswrts,7,' ') || chr(9) ||
lpad(v_fswiobytes,7,' ') || chr(9) ||
lpad(v_fswpct,6,' '));

end loop;

-- output totals
dbms_output.put_line('-----' || chr(9) ||
'-------' || chr(9) || '-------' || chr(9) || ' ' || chr(9) ||
'-------' || chr(9) || '-------' || chr(9) || ' ');
dbms_output.put_line('Totals:' || chr(9) || lpad(v_fstrds,7,' ') || chr(9) || lpad(v_totfsriobytes,7,' ') ||
chr(9) || chr(9) || lpad(v_fstwrts,7,' ') || chr(9) || lpad(v_totfswiobytes,7,' '));

-- provide read to write ratios
v_rwopsratio := round(v_fstrds / v_fstwrts,2);
v_rwbytesratio := round(v_totfsriobytes / v_totfswiobytes,2);
dbms_output.put_line('Read/Write Ops Ratio : ' || v_rwopsratio);
dbms_output.put_line('Read/Write MB Ratio : ' || v_rwbytesratio);
end;
/