grant select on v_$mystat to &&username ;
grant select on v_$latch to &&username ;
grant select on v_$timer to &&username ;
2. 创建临时表
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int )
on commit preserve rows;
3. 创建相应的runstat包
create or replace package runstats_pkg as
procedure rs_start;
procedure rs_middle;
procedure rs_stop(p_difference_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 is
begin
delete from run_stats;
insert into run_stats
select 'before', stats.* from stats;
g_start := dbms_utility.get_cpu_time;
end;
procedure rs_middle is
begin
g_run1 := (dbms_utility.get_cpu_time - g_start);
insert into run_stats
select 'after 1', stats.* from stats;
g_start := dbms_utility.get_cpu_time;
end;
procedure rs_stop(p_difference_threshold in number default 0) is
begin
g_run2 := (dbms_utility.get_cpu_time - g_start);
dbms_output.put_line('Run1 ran in ' || g_run1 || ' cpu hsecs');
dbms_output.put_line('Run2 ran in ' || g_run2 || ' cpu hsecs');
if (g_run2 <> 0) then
dbms_output.put_line('run 1 ran in ' ||
round(g_run1 / g_run2 * 100, 2) ||
'% of the time');
end if;
dbms_output.put_line(chr(9));
insert into run_stats
select 'after 2', stats.* from stats;
dbms_output.put_line(rpad('Name', 30) || lpad('Run1', 12) ||
lpad('Run2', 12) || lpad('Diff', 12));
for x in (select rpad(a.name, 30) ||
to_char(b.value - a.value, '999,999,999') ||
to_char(c.value - b.value, '999,999,999') ||
to_char(((c.value - b.value) - (b.value - a.value)),
'999,999,999') data
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 abs((c.value - b.value) - (b.value - a.value)) >
p_difference_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 -- difference and pct');
dbms_output.put_line(lpad('Run1', 12) || lpad('Run2', 12) ||
lpad('Diff', 12) || lpad('Pct', 10));
for x in (select to_char(run1, '999,999,999') ||
to_char(run2, '999,999,999') ||
to_char(diff, '999,999,999') ||
to_char(round(run1 /
decode(run2, 0, to_number(0), run2) * 100,
2),
'99,999.99') || '%' 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;
/
4. 用法
exec runstats_pkg.rs_start;
p1
exec runstats_pkg.rs_middle;
p2
exec runstats_pkg.rs_stop(1000);
示例：
dexter@REPO>exec runstats_pkg.rs_start;
PL/SQL 过程已成功完成。
dexter@REPO>delete from t1 ;
已删除 1 行。
dexter@REPO>exec runstats_pkg.rs_middle;
PL/SQL 过程已成功完成。
dexter@REPO>insert into t1 select level from dual connect by level <= 10000 ;
已创建 10000 行。
dexter@REPO>exec runstats_pkg.rs_stop(100);
Run1 ran in 1 cpu hsecs
Run2 ran in 4 cpu hsecs
run 1 ran in 25% of the time
Name Run1 Run2 Diff
LATCH.session idle bit 331 207 -124
STAT...redo entries 15 167 152
LATCH.SQL memory manager worka 1,012 807 -205
STAT...db block changes 77 300 223
LATCH.enqueue hash chains 1,035 794 -241
LATCH.cache buffers chains 2,182 2,425 243
STAT...db block ge