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;