RE: I/O times

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Mon, 5 Jun 2006 15:22:05 -0700
Message-ID: <BEE6A332AA61424EAE305CF89D6F75C84BB4C6@USSCCEVS101.corp.hds.com>

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_rds) || ',' ||
           to_char(v_wrt) || ',' || to_char(v_blk_rds)
           || ',' || to_char(v_blk_wrt));
      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 **

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Yechiel Adar Sent: Monday, June 05, 2006 1:17 AM
To: ORACLE-L_at_freelists.org
Subject: I/O times

Hello

I am doing a gross checking of performence. Since we do regular snapshots, I wanted to see the I/O times during March and June.
Is the following good enough:

• Mb stands for Multi Block, SB stands for Single Block.

select to_char(snap_time,'MM/DD') day ,

    sum(phyrds) MB_read , sum(readtim) MB_time , sum(phyrds) / sum(readtim) single_mb_time ,

    sum(SINGLEBLKRDS) SB_read, sum(SINGLEBLKRDTIM) SB_time, sum(SINGLEBLKRDS) / sum(SINGLEBLKRDTIM) single_SB_time,

    sum(phywrts) WRITES, sum(writetim) WRITES_TIME, sum(phywrts) / sum(writetim) SINGLE_WRITE_TIME

from STATS$FILESTATXS a , STATS$SNAPSHOT b

where tsname = 'USERS'

    and a.snap_id = b.snap_id and TO_CHAR(b.snap_time, 'MM') in ('03','06')

    and to_char(snap_time,'HH') < '08'

group by to_char(snap_time,'MM/DD')

order by to_char(snap_time,'MM/DD')

--
Adar Yechiel
Rechovot, Israel

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 05 2006 - 17:22:05 CDT