[Oracle-l] I/O times

Yechiel,

The issue with this SQL is that you will get incorrect results if you
had a Db restart in between. I use the following segment to get the data
out of STATSPACK tables. Change it as required for Start/end
dates/DBMS_OUTPUT/etc. It will take care of Db restarts and display a
per day I/O summary:

declare

v_fname varchar2(30);
v_rds number;
v_wrt number;
v_blk_rds number;
v_blk_wrt number;
v_begin_id perfstat.stats$filestatxs.snap_id%TYPE;
v_end_id perfstat.stats$filestatxs.snap_id%TYPE;
l_FileHandle UTL_FILE.FILE_TYPE;
l_FileDir Varchar2(100) := '/usr/tmp';
l_FileName Varchar2(100) := '';

v_end_date DATE;
v_curr_date DATE;
/* This Cursor fetches the min and max snap id for a given day. It also

makes sure that there was no DB restart inbetween */
cursor snapshot is
select min(snap_id), max(snap_id)
from perfstat.stats$snapshot
where trunc(snap_time) = trunc(v_curr_date)
and startup_time =
(select min(startup_time)
from perfstat.stats$snapshot
where trunc(snap_time) = trunc(v_curr_date));
/* This Cursor fetches the sysstat values given the min and max snap id

for a given day fetched by the previous cursor */
cursor filestat is
select substr(e.filename,1,40) name,
e.phyrds - b.phyrds phyrds, e.phywrts - b.phywrts phywrts,
e.phyblkrd - b.phyblkrd phyblkrd, e.phyblkwrt - b.phyblkwrt phyblkwrt
from perfstat.stats$filestatxs b, perfstat.stats$filestatxs e
where b.filename = e.filename
and ( e.phyrds - b.phyrds ) > 1000
and b.snap_id = v_begin_id and e.snap_id = v_end_id;
begin
-- dbms_output.enable (9999999);

l_FileName := 'file_io.dat';
l_FileHandle := utl_file.fopen(l_FileDir, l_FileName, 'w');
v_curr_date := to_date('01-JAN-03');
v_end_date := to_date('13-JUL-05');

LOOP

open snapshot;
LOOP

fetch snapshot into v_begin_id, v_end_id;
exit when snapshot%NOTFOUND;

open filestat;
loop
fetch filestat into v_fname, v_rds, v_wrt,
v_blk_rds, v_blk_wrt;
exit when filestat%NOTFOUND;
utl_file.put_line(l_FileHandle, v_curr_date || ',' || v_fname
to_char(v_wrt) || ',' || to_char(v_blk_rds)
end loop;
close filestat;

END LOOP;

close snapshot;
v_curr_date := v_curr_date + 1;
exit when trunc(v_curr_date) = trunc(v_end_date);
END LOOP;

utl_file.fclose(l_FileHandle);
end;
/

Regards,
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)


http://www.klove.com - Positive, encouraging music 24x7 worldwide


The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **