fiomon.sql

一个Oracle性能监视工具

set serveroutput on
set verify  off
set feedback off

declare

type CurrStatTabTyp is table of v$filestat%ROWTYPE index by binary_integer;
type PrevStatTabTyp is table of v$filestat%ROWTYPE index by binary_integer;
Curr_Tab CurrStatTabTyp;
Prev_Tab PrevStatTabTyp;
t_records  number := &1;
t_interval number := &2;
t_count    number := &3;
t_delta    varchar2(20) :='&4';
t_filename v$dbfile.name%TYPE;

counter0 BINARY_INTEGER := 0;
counter1 BINARY_INTEGER := 0;
counter2 BINARY_INTEGER := 0;

cursor c1 IS
select *
  from v$filestat
 order by file#;
  
begin
 for c1rec IN c1 loop
   Prev_Tab(counter1) := c1rec;
   counter1 := counter1 + 1;
 end loop; 

 if t_delta = 'delta'
 then
   dbms_lock.sleep(t_interval);

   for c1rec in c1 loop
     Curr_Tab(counter2) := c1rec;
     counter2 := counter2 + 1;
   end loop; 
   dbms_output.put_line('ID  FILENAME                          (in this interval) READS  WRITES');
   counter2 := 0;

   while counter2 < counter1 loop
    select name
      into t_filename
      from v$dbfile d
     where d.file# = Prev_Tab(counter2).file#;
    dbms_output.put_line(rpad(ltrim(to_char(Prev_Tab(counter2).file#,'990')),4)            ||
                         rpad(ltrim(t_filename),50)                                        ||
                         lpad(ltrim(to_char(Curr_Tab(counter2).phyrds - 
                                            Prev_Tab(counter2).phyrds,'9999990')),8)       ||
                         lpad(ltrim(to_char(Curr_Tab(counter2).phywrts - 
                                            Prev_Tab(counter2).phywrts,'9999990')),8) );
       counter2 := counter2 + 1;
   end loop; 
 else
   dbms_output.put_line('ID  FILENAME                    (since database startup) READS  WRITES');
   counter2 := 0;

   while counter2 < counter1 loop
    select name
      into t_filename
      from v$dbfile d
     where d.file# = Prev_Tab(counter2).file#;
    dbms_output.put_line(rpad(ltrim(to_char(Prev_Tab(counter2).file#,'990')),4)            ||
                         rpad(ltrim(t_filename),50)                                        ||
                         lpad(ltrim(to_char(Prev_Tab(counter2).phyrds,'9999990')),8)       ||
                         lpad(ltrim(to_char(Prev_Tab(counter2).phywrts,'9999990')),8) );
       counter2 := counter2 + 1;
   end loop; 
 end if;
END;
/