Oracle PL/SQL编程之如何实现程序来统计另一个程序的性能

程序功能：

统计其他程序运行所占用的DB time，DB CPU，session logical reads，physical read IO requests，physical write IO requests。并能够实现优化后和优化前各指标的差值返回给用户。

程序如下：

/*******创建表ct用于保存第一次运行程序采集到的信息和优化后采集到的信息*******/

--drop table ct;
--create table ct(name varchar2(50),firstvalue number(8),secondvalue number(8));

/*******创建表isFirst用于标识要测试的程序是第一次运行还是优化后运行*******/

--drop table isFirst;
--create table isFirst(first number(1));

/************
设计思路：
oracle中创建以上两张表，当第一次运行被测试程序是，本程序采集被测试程序运行信息并存储到表ct中firstvalue列中，
同时修改isFirst表中first的值为1，表示被测试程序已经运行第一次。同时输出采集到的信息。当被测试程序优化后再次
运行，本程序采集被测试程序的运行信息插入到表ct中secondvalue中，同时修改isFirst表中first的值为0，将表中firstvalue
和secondvalue字段比较得出差值输出给用户即可。同时删除表ct中的行，节省空间。
************/

declare
b_isfirst number(1);
i_lr binary_integer :=0;
i_pr binary_integer :=0;
i_pw binary_integer :=0;
i_db_time binary_integer:=0;
i_db_cpu binary_integer:=0;
i_sid binary_integer :=0;
begin
  select sid  into i_sid from v$mystat where rownum = 1;
  select first into b_isfirst from isFirst; --判断是否为第一次运行程序
  
  declare
  begin
    if b_isfirst = 0 then   --第一次
      select value into i_db_time from V$SESS_TIME_MODEL where sid=i_sid and stat_name ='DB time';
      select value into i_db_cpu from V$SESS_TIME_MODEL where sid=i_sid and stat_name ='DB CPU';
      select value into i_lr from v$sesstat a,v$statname b where a.STATISTIC#=b.STATISTIC# and sid=i_sid and name ='session logical reads';   
      select value into i_pr from v$sesstat a,v$statname b where a.STATISTIC#=b.STATISTIC# and sid=i_sid and name ='physical read IO requests';
      select value into i_pw from v$sesstat a,v$statname b where a.STATISTIC#=b.STATISTIC# and sid=i_sid and name ='physical write IO requests';  

      insert into ct(name,firstvalue) values('DB time',i_db_time);
      insert into ct(name,firstvalue) values('DB CPU',i_db_cpu);
      insert into ct(name,firstvalue) values('session logical reads',i_lr);
      insert into ct(name,firstvalue) values('physical read IO requests',i_pr);
      insert into ct(name,firstvalue) values('physical write IO requests',i_pw);
      commit;
      
      dbms_output.put_line('time:'||i_db_time);
      dbms_output.put_line('cpu:'||i_db_cpu);
      dbms_output.put_line('lr:'||i_lr);
      dbms_output.put_line('pr:'||i_pr);
      dbms_output.put_line('pw:'||i_pw);
    
      update isFirst set first=1;
      commit;
    else                 --第二次
      select value into i_db_time from V$SESS_TIME_MODEL where sid=i_sid and stat_name ='DB time';
      select value into i_db_cpu from V$SESS_TIME_MODEL where sid=i_sid and stat_name ='DB CPU';
      select value into i_lr from v$sesstat a,v$statname b where a.STATISTIC#=b.STATISTIC# and sid=i_sid and name ='session logical reads';   
      select value into i_pr from v$sesstat a,v$statname b where a.STATISTIC#=b.STATISTIC# and sid=i_sid and name ='physical read IO requests';
      select value into i_pw from v$sesstat a,v$statname b where a.STATISTIC#=b.STATISTIC# and sid=i_sid and name ='physical write IO requests';  

      update ct set secondvalue=i_db_time where name='DB time';
      update ct set secondvalue=i_db_cpu where name='DB CPU';
      update ct set secondvalue=i_lr where name='session logical reads';    
      update ct set secondvalue=i_pr where name='physical read IO requests';    
      update ct set secondvalue=i_pw where name='physical write IO requests';    
      commit;
      
      declare
        i_tlr binary_integer :=0;
        i_tpr binary_integer :=0;
        i_tpw binary_integer :=0;
        i_tdb_time binary_integer:=0;
        i_tdb_cpu binary_integer:=0;
      begin
        select firstvalue into i_tdb_time from ct where name ='DB time';
        select firstvalue into i_tdb_cpu from ct where name ='DB CPU';
        select firstvalue into i_tlr from ct where name ='session logical reads';
        select firstvalue into i_tpr from ct where name ='physical read IO requests';
        select firstvalue into i_tpw from ct where name ='physical write IO requests';  
 
        i_db_time:=i_db_time-i_tdb_time;
        i_db_cpu:=i_db_cpu - i_tdb_cpu;
        i_lr:=i_lr-i_tlr;
        i_pr:=i_pr-i_tpr;
        i_pw:=i_pw-i_tpw;
      
        dbms_output.put_line('time decrease:'||i_db_time);
        dbms_output.put_line('cpu decrease:'||i_db_cpu);
        dbms_output.put_line('lr decrease:'||i_lr);
        dbms_output.put_line('pr decrease:'||i_pr);
        dbms_output.put_line('pw decrease:'||i_pw);
      end;
    
        update isFirst set first=0;
        commit;
        delete from ct;
        commit;
    end if;
  end;
end;

本程序统计的是自身运行的性能，更改sid即可统计其他程序。程序有很多漏洞，求大神指正。