runstats 為 Oracle 經典效能比較套件，可量測兩段 PL/SQL 程式碼的：
create global temporary table run_stats (
runid varchar2(15), name varchar2(80), value int
) on commit preserve rows;
create or replace view stats as
select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets from v$latch
union all
select 'STAT...Elapsed Time', hsecs from v$timer;
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_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);
dbms_output.put_line('Run1 ran in ' || g_run1 || ' hsecs');
dbms_output.put_line('Run2 ran in ' || g_run2 || ' 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 c.runid='after 2'
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;
/
drop table t purge;
create table t (x int);
-- Run1: 動態 SQL 拼接 (硬解析)
create or replace procedure p1 as
l_cnt number;
begin
for i in 1 .. 10000 loop
execute immediate 'select count(*) from t where x = ' || i into l_cnt;
end loop;
end;
/
-- Run2: 靜態 SQL 綁定變數 (軟解析)
create or replace procedure p2 as
l_cnt number;
begin
for i in 1 .. 10000 loop
select count(*) into l_cnt from t where x = i;
end loop;
end;
/
-- 執行比較
set serveroutput on;
exec runstats_pkg.rs_start;
exec p1;
exec runstats_pkg.rs_middle;
exec p2;
exec runstats_pkg.rs_stop(1000);
Run1 ran in 311 hsecs Run2 ran in 30 hsecs run 1 ran in 1036.67% of the time Name Run1 Run2 Diff STAT...parse count (hard) 10,011 5 -10,006 STAT...parse count (total) 10,041 14 -10,027 LATCH.library cache 216,965 20,474 -196,491 LATCH.shared pool 206,050 10,230 -195,820 LATCH.library cache lock 60,292 179 -60,113 LATCH.row cache objects 150,201 356 -149,845 ... (更多統計差異) Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 854,415 111,834 -742,581 764.00%
runstats 能直觀量化「寫法差異」帶來的系統資源消耗差距，是 PL/SQL 調優利器。
