Oracle runstats_pkg 过程性能比较

① create view stats as select a.value,'stats'||b.name from v$mystat a,v$statname b where a.statistic#=b.statistic# union
       select 'latch'||name name,gets from v$latch;

       上面是创建stats视图；

② create global temporary table run_stats(runid varchar2(15),name varchar2(80),value int) on commit preserve rows/delete rows;

   这是创建全局临时表；

③ 下面创建runstats_pkg包：
   create or replace package runstats_pkg as
     procedure rs_start;
     procedure rs_middle;
     procedure rs_stop(p_diffrence_threshold in number default 0);
   end;

④ 接着创建包体：

    create or replace package body runstats_pkg as
      g_start number;
      g_run1 number;
      g_run2 number;
procedure rs_start
as
     begin
       delete from run_stats;
       insert into run_stats select 'before',name,value from stats;
       g_start:=dbms_utility.get_time;
     end;                  
procedure rs_middle
as
     begin
       g_run1:=(dbms_utility.get_time-g_start);
       insert into run_stats select 'after 1',name,value from stats;
       g_start:=dbms_utility.get_time;
     end;
procedure rs_stop(p_diffrence_threshold in number default 0)
      as
    begin
     g_run2:=(dbms_utility.get_time-g_start);
     dbms_output.put_line('Run1 ran in'||g_run1||'hsecs');
     dbms_output.put_line('Run2 ran in'||g_run2||'hsecs');
     dbms_output.put_line('run1 ran in'||round((g_run1/g_run2)/100,2)||'% of the time');
     dbms_output.put_line(chr(9));
     insert into run_stats select 'after 2',stats.* from stats;
     dbms_output.put_line(lpad('Name',30)||lpad('Run1',10)||lpad('Run2',10)||lpad('Diff',10));
     for x in
       (select lpad(a.name,30)||
               to_char((b.value-a.value),'9,999,999')||
        to_char((c.value-b.value),'9,999,999')||
        to_char(((c.value-b.value)-(b.value-a.value)),'9,999,999') data
           from run_stats a,run_stats b,run_stats c
               where a.name=b.name
        and
        a.name=b.name
        and
        c.name=b.name
        and
        a.runid='before'
        and
        b.runid='after 1'
        and
        c.runid='after 2'
               and
        (c.value-a.value)>0
        and
        abs((c.value-b.value)-(b.value-a.value))>p_diffrence_threshold
          order by abs((c.value-b.value)-(b.value-a.value))
   )
     loop
     dbms_output.put_line(x.data);
             end loop;
       dbms_output.put_line(chr(9));
       dbms_output.put_line('Run1 latches total versus runs -- diffrence and pct');
       dbms_output.put_line(lpad('Run1',10)||lpad('Run2',10)||lpad('Diff',10)||lpad('Pct',8));
       for x in
          (select to_char(run1,'9,999,999')||
           to_char(run2,'9,999,999')||
    to_char(diff,'9,999,999')||
    to_char(round(run1/run2*100,2),'9,999,999')||
    '%' data
                    from (select sum(b.value-a.value) run1,sum(c.value-b.value) run2,sum((c.value-b.value)-(b.value-a.value)) diff
                     from run_stats a,run_stats b,run_stats c
       where a.name=b.name and
       b.name=c.name and
       a.runid='before'
       and b.runid='after 1'
       and c.runid='after 2'
                                   and a.name like 'LATCH%'
       ) )
                   loop
      dbms_output.put_line(x.data);
      end loop;
      end;
end;
/

⑤ 再下面我们开始使用runstas_pkg包：

   ㈠ create table test as select * from dba_objects where 1=0;

   ㈡ exec runstats_pkg.rs_start;
   ㈢ insert into test select * from dba_objects;
        commit;

   ㈣   exec runstats_pkg.rs_middle;

㈤ begin
for x in (select * from dba_objects) loop
    insert into test values x;
end loop;
   commit;
end;

㈥ exec runstats_pkg.rs_stop;