FAQ
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

Search Discussions

  • John Kanagaraj at Jun 5, 2006 at 10:22 pm
    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
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 5, '06 at 8:17a
activeJun 5, '06 at 10:22p
posts2
users2
websiteoracle.com

2 users in discussion

Yechiel Adar: 1 post John Kanagaraj: 1 post

People

Translate

site design / logo © 2022 Grokbase