runstats只测量三件重要的东西:
挂钟或占用的时间
系统统计数据
栓锁(此信息是该报告的关键)
方法中出现的栓锁越少,情况越好。本人可以选择一种方法,它的执行时间更长但只使用10%的栓锁。我知道使用少量栓锁的方法将比使用较多栓锁的方法更好。
runstats最好在单用户数据库中使用。
安装runstats:
SQL> conn /as sysdba
已连接。
SQL> create user test identified by test;
用户已创建。
SQL> grant dba to test;
授权成功。
SQL> grant select on sys.v_$timer to test;
授权成功。
SQL> grant select on v_$mystat to test;
授权成功。
SQL> grant select on sys.v_$statname to test;
授权成功。
SQL> grant select on sys.v_$latch to test;
授权成功。
SQL> conn test/test
已连接。
SQL> create global temporary table run_stats
2 ( runid varchar2(15),
3 name varchar2(80),
4 value int
5 )on commit preserve rows;
表已创建。
SQL> create or replace view stats as
2 select 'STAT..' || a.name name, b.value
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select 'LATCH.' || name, gets from v$latch
7 union all
8 select 'STAT...Elapsed Time', hsecs from v$timer;
视图已创建。
下面是runstats脚本:
create or replace package runstats as
procedure rs_start;
procedure rs_middle;
procedure rs_stop(p_difference_threshold in number default 0);
end;
/
--p_difference_threshold参数的含义是:前后两个相同的项的统计结果相差的数目的绝对值大于等于该参数设定的值,该统计项才会被显示。默认值是0表示所有的结果都显示。
create or replace package body runstats 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_time;
end;
procedure rs_middle is
begin
g_run1 := (dbms_utility.get_time - g_start);
insert into run_stats
select 'after 1', stats.* from stats;
g_start := dbms_utility.get_time;
end;
procedure rs_stop(p_difference_threshold in number default 0) is
begin
g_run2 := (dbms_utility.get_time - g_start);
--add a line here to avoid ora-20000
dbms_output.enable(480000);
dbms_output.put_line('Run1 ran in ' || g_run1 || ' hsecs');
dbms_output.put_line('Run2 ran in ' || g_run2 || ' hsecs');
dbms_output.put_line('run 1 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(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 c.runid = 'after 2'
-- and (c.value-a.value) > 0
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 / 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;
/
至此,Runstats包安装完毕。下面开始做测试:
SQL> create table x(id number);
表已创建。
SQL> set serverout on
SQL> exec runstats.rs_start;
PL/SQL 过程已成功完成。
SQL> begin
2 for i in 1..1000 Loop
3 insert into x values(i);
4 end loop;
5 end;
6 /
PL/SQL 过程已成功完成。
SQL> exec runstats.rs_middle;
PL/SQL 过程已成功完成。
SQL> begin
2 for i in 1..1000 Loop
3 execute immediate 'insert into x values('||i||')';
4 end loop;
5 end;
6 /
PL/SQL 过程已成功完成。
SQL> exec runstats.rs_stop(1000);
Run1 ran in 4357 hsecs
Run2 ran in 11323 hsecs
run 1 ran in 38.48% of the time
Name Run1 Run2 Diff
STAT..session cursor cache hit 1,010 4 -1,006
STAT..undo change vector size 72,632 71,204 -1,428
LATCH.SQL memory manager worka 1,008 2,482 1,474
STAT..db block gets from cache 1,140 3,062 1,922
STAT..db block gets 1,140 3,062 1,922
STAT..db block gets from cache 22 2,000 1,978
LATCH.kks stats 18 2,019 2,001
LATCH.shared pool simulator 44 2,564 2,520
STAT..recursive calls 1,240 4,079 2,839
STAT..session logical reads 1,232 4,093 2,861
LATCH.enqueue hash chains 799 3,890 3,091
LATCH.enqueues 762 3,866 3,104
STAT..redo size 255,104 249,264 -5,840
LATCH.cache buffers chains 5,787 11,627 5,840
STAT...Elapsed Time 4,359 11,325 6,966
STAT..physical read total byte 8,192 0 -8,192
STAT..physical read bytes 8,192 0 -8,192
STAT..cell physical IO interco 8,192 0 -8,192
LATCH.row cache objects 773 18,724 17,951
LATCH.shared pool 1,677 41,385 39,708
STAT..session uga memory max 123,452 73,084 -50,368
STAT..session uga memory 0 196,536 196,536
STAT..session pga memory 0 262,144 262,144
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
14,109 93,135 79,026 15.15%
PL/SQL 过程已成功完成。