TOM runstats脚本

runstats
runstats 是我开发的一个工具，能对做同一件事的两个不同方法进行比较，得出孰优孰劣的结果。
你只需提供两个不同的方法，余下的事情都由 runstats 负责。 runstats 只是测量 3 个要素：
 墙上时钟（ wall clock ）或耗用时间（ elapsed time ）：知道墙上时钟或耗用时间很有用，不过 这
不是最重要的信息。
 系统统计结果：会并排显示每个方法做某件事（如执行一个解析调用）的次数，并展示二者之差 。
 闩定（ latching ）：这是这个报告的关键输出。
你在本书中会了解到，闩（ latch ）是一种轻量级的锁。锁（ lock ）是一种串行化设备，而串行化设
备不支持并发。如果应用不支持并发，可扩缩性就比较差，只能支持较少的用户，而且需要更多的资源。
构建应用时，我们往往希望应用能很好地扩缩，也就是说，为 1 位用户服务与为 1,000 或 10,000 位用户 服
务应该是一样的。应用中使用的闩越少，性能就越好。如果一种方法从墙上时钟来看运行时间较长，但是
只使用了另一种方法 10% 的闩，我可能会选择前者。因为我知道，与使用更多闩的方法相比，使用较少闩
的方法能更好地扩缩。
runstats 最后独立使用，也就是说，最好在一个单用户数据库上运行。我们会测量各个方法的统计
结果和闩定（锁定）活动。 runstats 在运行过程中，不希望其他任务对系统的负载或闩产生影响。只需 一

个小的测试数据库就能很好地完成这些测试。例如，我就经常使用我的台式机或手提电脑进行测试。

要使用 runstats ，需要能访问几个 V$ 视图，并创建一个表来存储统计结果，还要创建 runstats 包 。
为此，需要访问 3 个 V$ 表（就是那些神奇的动态性能表）： V$STATNAME 、 V$MYSTAT 和 V$LATCH 。以下是我
使用的视图：

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;

如果你能得到 V$STATNAME 、 V$MYSTAT 、 V$LATCH 和 V$TIMER 的直接授权，就能直接对这些表执行 SE LECT
操作（相应地可以自行创建视图）；否则，可以由其他人对这些表执行 SELECT 操作为你创建视图，并授予
你在这个视图上执行 SELECT 的权限。
一旦建立视图，接下来只需要一个小表来收集统计结果：
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int )
on commit preserve rows;

最后，需要创建 runstats 包。其中包含 3 个简单的 API 调用：
 runstats 测试开始时调用 RS_STAT （ runstats 开始）。
 正如你想象的， RS_MIDDLE 会在测试之间调用。
 完成时调用 RS_STOP ，打印报告。
创建 runstats 包的规范如下：

ops$tkyte@ORA920> create or replace package runstats_pkg
2 as
3 procedure rs_start;
4 procedure rs_middle;
5 procedure rs_stop( p_difference_threshold in number default 0 );
6 end;
7 /
Package created.

参数 P_DIFFERENCE_THRESHOLD 用于控制最后打印的数据量。 runstats 会收集并得到每次运行的统 计
结果和闩信息，然后打印一个报告，说明每次测试（每个方法）使用了多少资源，以及不同测试（不同方
法）的结果之差。可以使用这个输入参数来控制只查看差值大于这个数的统计结果和闩信息。由于这个参
数默认为 0 ，所以默认情况下可以看到所有输出。
下面我们逐一分析包体中的过程。包前面是一些全局变量，这些全局变量用于记录每次运行的耗用 时间：

下面是 RS_START 例程。这个例程只是清空保存统计结果的表，并填入 “ 上一次 ” （ before ）得到的 统
计结果和闩信息。然后获得当前定时器值，这是一种时钟，可用于计算耗用时间（单位百分之一秒）：
ops$tkyte@ORA920> create or replace package body runstats_pkg
2 as
3
4 g_start number;
5 g_run1 number;
6 g_run2 number;
7

下面是 RS_START 例程。这个例程只是清空保存统计结果的表，并填入 “ 上一次 ” （ before ）得到的 统
计结果和闩信息。然后获得当前定时器值，这是一种时钟，可用于计算耗用时间（单位百分之一秒）：
8 procedure rs_start
9 is
10 begin
11 delete from run_stats;
12
13 insert into run_stats
14 select 'before', stats.* from stats;
15
16 g_start := dbms_utility.get_time;
17 end;
18

接下来是 RS_MIDDLE 例程。这个例程只是把第一次测试运行的耗用时间记录在 G_RUN1 中。然后插入
当前的一组统计结果和闩信息。如果把这些值与先前在 RS_START 中保存的值相减，就会发现第一个方法 使
用了多少闩，以及使用了多少游标（一种统计结果），等等。
最后，记录下一次运行的开始时间：
19 procedure rs_middle
20 is

21 begin
22 g_run1 := (dbms_utility.get_time-g_start);
23
24 insert into run_stats
25 select 'after 1', stats.* from stats;
26 g_start := dbms_utility.get_time;
27
28 end;
29
30 procedure rs_stop(p_difference_threshold in number default 0)
31 is
32 begin

33 g_run2 := (dbms_utility.get_time-g_start);
34
35 dbms_output.put_line
36 ( 'Run1 ran in ' || g_run1 || ' hsecs' );
37 dbms_output.put_line
38 ( 'Run2 ran in ' || g_run2 || ' hsecs' );
39 dbms_output.put_line
40 ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
41 '% of the time' );
42 dbms_output.put_line( chr(9) );
43
44 insert into run_stats

45 select 'after 2', stats.* from stats;
46
47 dbms_output.put_line
48 ( rpad( 'Name', 30 ) || lpad( 'Run1', 10 ) ||
49 lpad( 'Run2', 10 ) || lpad( 'Diff', 10 ) );
50
51 for x in
52 ( select rpad( a.name, 30 ) ||
53 to_char( b.value-a.value, '9,999,999' ) ||
54 to_char( c.value-b.value, '9,999,999' ) ||
55 to_char( ( (c.value-b.value)-(b.value-a.value)) ,
'9,999,999' ) data
56 from run_stats a, run_stats b, run_stats c

57 where a.name = b.name
58 and b.name = c.name
59 and a.runid = 'before'
60 and b.runid = 'after 1'
61 and c.runid = 'after 2'
62 and (c.value-a.value) > 0
63 and abs( (c.value-b.value) - (b.value-a.value) )
64 > p_difference_threshold
65 order by abs( (c.value-b.value)-(b.value-a.value))
66 ) loop
67 dbms_output.put_line( x.data );

68 end loop;
69
70 dbms_output.put_line( chr(9) );
71 dbms_output.put_line
72 ( 'Run1 latches total versus runs -- difference and pct'
);
73 dbms_output.put_line
74 ( lpad( 'Run1', 10 ) || lpad( 'Run2', 10 ) ||
75 lpad( 'Diff', 10 ) || lpad( 'Pct', 8 ) );
76
77 for x in
78 ( select to_char( run1, '9,999,999' ) ||
79 to_char( run2, '9,999,999' ) ||

80 to_char( diff, '9,999,999' ) ||
81 to_char( round( run1/run2*100,2 ), '999.99' )
|| '%' data
82 from ( select sum(b.value-a.value) run1, sum(c.value-
b.value) run2,
83 sum( (c.value-b.value)-(b.value-
a.value)) diff
84 from run_stats a, run_stats b, run_stats c
85 where a.name = b.name
86 and b.name = c.name
87 and a.runid = 'before'
88 and b.runid = 'after 1'
89 and c.runid = 'after 2'

90 and a.name like 'LATCH%'
91 )
92 ) loop
93 dbms_output.put_line( x.data );
94 end loop;
95 end;
96
97 end;
98 /
Package body created.

下面可以使用 runstats 了。我们将通过例子来说明如何使用 runstats 对批量插入（ INSERT ）和逐 行
处理进行比较，看看哪种方法效率更高。首先建立两个表，要在其中插入 1,000,000 行记录：
ops$tkyte@ORA10GR1> create table t1
2 as
3 select * from big_table.big_table
4 where 1=0;
Table created.
ops$tkyte@ORA10GR1> create table t2
2 as
3 select * from big_table.big_table
4 where 1=0;
Table created.

接下来使用第一种方法插入记录，也就是使用单独一条 SQL 语句完成批量插入。首先调用
RUNSTATS_PKG.RS_START ：
ops$tkyte@ORA10GR1> exec runstats_pkg.rs_start;

下面准备执行第二种方法，即逐行地插入数据：
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR1> insert into t1 select * from big_table.big_table;
1000000 rows created.
ops$tkyte@ORA10GR1> commit;
Commit complete.
ops$tkyte@ORA10GR1> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR1> begin
2 for x in ( select * from big_table.big_table )

3 loop
4 insert into t2 values X;
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.

最后生成报告：
Name Run1
Run2 Diff
STAT...recursive calls 8,089
1,015,451 1,007,362
STAT...db block changes 109,355 2,085,0

99 1,975,744
LATCH.library cache 9,914
2,006,563 1,996,649
LATCH.library cache pin 5,609 2,0
03,762 1,998,153
LATCH.cache buffers chains 575,819 5,565,489
4,989,670
STAT...undo change vector size 3,884,940 67,978,932
64,093,992
STAT...redo size 118,854,004 37
8,741,168 259,887,164
Run1 latches total versus runs -- difference and pct

Run1 Run2
Diff Pct
825,530 11,018,773
10,193,243 7.49%
PL/SQL procedure successfully completed.

取自TOM大师的书籍。