/* ckiostat.sql
     show the datafile 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;
/