Re: stats$filestatxs - a query showing IO stats (without div by zero trap)

From: John Kanagaraj <john.kanagaraj_at_gmail.com>
Date: Tue, 25 Sep 2007 09:32:56 -0700
Message-ID: <2ead3a60709250932x62b7c5b1w10379abb41f2a88d@mail.gmail.com>

Tony,

I wrote the following PL/SQL program to extract the LIO per snapshot from existing AWR Data, and it takes Database restarts into account. You can modify it to extract anything you want from the AWR easily.....I have highlighted the pieces that do this. In fact I wrote this originally for extracting data from STATSPACK and modified it for AWR :) Hth,
John Kanagaraj <><
DB Soft Inc
http://www.linkedin.com/in/johnkanagaraj http://jkanagaraj.wordpress.com (Sorry - not an Oracle blog!) ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

rem
rem Show diff in specific statistic from AWR Data rem Rewritten for 10g from Statspack
rem JK/Sep 2007
declare

v_lio                   number;
v_begin_id              sys.wrh$_sysstat.snap_id%TYPE;
v_end_id                sys.wrh$_sysstat.snap_id%TYPE;
v_begin_time            sys.wrm$_snapshot.begin_interval_time%TYPE;
v_end_time              sys.wrm$_snapshot.end_interval_time%TYPE;
v_begin_startup_time    sys.wrm$_snapshot.startup_time%TYPE;
v_end_startup_time      sys.wrm$_snapshot.startup_time%TYPE;
l_FileHandle            UTL_FILE.FILE_TYPE;
l_FileDir               Varchar2(100) := '/tmp';
l_FileName              Varchar2(100) := '';

/* This cursor fetches details of the current snapshot plus the next one

    using the LEAD function. We will use this to     make sure that there was no DB restart inbetween */ cursor snapshot is
select snap_id, lead(snap_id, 1, 0) OVER (ORDER BY snap_id), startup_time, lead(startup_time, 1) OVER (ORDER BY snap_id), begin_interval_time, end_interval_time
from sys.wrm$_snapshot;
cursor sysstat is
select e.value - b.value
from sys.wrh$_sysstat b, sys.wrh$_sysstat e, v$statname n where b.stat_id = n.stat_id

and b.stat_id = e.stat_id
and n.name = 'session logical reads'
and b.snap_id = v_begin_id and e.snap_id = v_end_id;

begin
-- dbms_output.enable (9999999);
l_FileName := 'AWR_session_logical_reads.dat'; l_FileHandle := utl_file.fopen(l_FileDir, l_FileName, 'w'); open snapshot;

   LOOP
    fetch snapshot into v_begin_id, v_end_id,

       v_begin_startup_time, v_end_startup_time,
       v_begin_time, v_end_time;

    exit when snapshot%NOTFOUND;

• Run through only if the startup times for both snaps are same!
• also, avoid the last line (lead will return 0 for end_id) if ( v_begin_startup_time = v_end_startup_time ) and ( v_end_id != 0 ) then open sysstat; loop fetch sysstat into v_lio; exit when sysstat%NOTFOUND; utl_file.put_line(l_FileHandle, v_end_time || ',' || v_lio); end loop; close sysstat; end if; END LOOP; close snapshot; utl_file.fclose(l_FileHandle); end; /

On 9/24/07, Tony Adolph <tony.adolph.dba_at_gmail.com> wrote:
>
> No I didn't, that would bugger up the results as you say :-(
>
> So the scrips not so generic, but still useful if you suspect you're
> suffering from intermittent slow IO.... or persistently slow IO.
>
> I'll bear in mind database bounces for version 3 :-)
>
> Cheers
> Tony
>

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

Received on Tue Sep 25 2007 - 11:32:56 CDT