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

  • From: "John Kanagaraj" <john.kanagaraj@xxxxxxxxx>
  • To: "Tony Adolph" <tony.adolph.dba@xxxxxxxxx>
  • Date: Tue, 25 Sep 2007 09:32:56 -0700

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@xxxxxxxxx> 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
>

Other related posts: