分类： 数据库开发技术
2011-09-28 17:01:36
runstats是一个开发工具，能对同一件事的两个不同方法进行比较，得出孰优孰劣的结果。你只要提供两个不同的方法，余下的事情都由runstats负责。runstats只是测量3个要素：
闩（latch）是一种轻量级的锁。锁（lock）是一种串行化设备，而串行化设备不支持并发。如果应用不支持并发，可扩缩性就比较差，只能支持较少的用户，而且需要更多的资源。构建应用时，我们往往希望应用能很好地扩缩，也就是说，为1位用户服务与为1000或10 000位用户服务应该是一样的。应用中使用的闩越少，性能就越好。如果一种方法从墙上时钟来看运行时间较长，但是只使用了另一种方法10%的闩，这时候就可能会选择前者。与使用更多闩的方法相比，使用较少闩的方法能更好地扩缩。
runstats最好独立使用，最好在一个单用户数据库上运行。我们会测量各个方法的统计结果和闩定（锁定）活动。runstats在运行过程中，不希望其他任务对系统的负载或闩产生影响。只需一个小的测试数据库就能很好地完成这些测试。
要使用runstats需要能访问几个V$视图，并创建一个表来存储统计结果，还要创建runstats包。为此，需要访问3个V$表（就是那些神奇的动态性能表）：V$STATNAME、V$MYSTAT和V_$LATCH。
1.创建视图：
先要授权给system
SQL> grant select on v_$session to system;
授权成功。
SQL> grant select on v_$statname to system;
授权成功。
SQL> grant select on v_$mystat to system;
授权成功。
SQL> grant select on v_$latch to system;
授权成功。
SQL> grant select on v_$timer to system;
授权成功。
SQL> create or replace view stats
2 as
3 select 'STAT...' || a.name name, b.value
4 from v_$statname a, v_$mystat b
5 where a.statistic# = b.statistic#
6 union all
7 select 'LATCH.' || name, gets
8 from v_$latch;
视图已创建。
2.建立一个小表收集统计结果：
SQL> create global temporary table run_stats
2 (runid varchar2(15),
3 name varchar2(80),
4 value int)
5 on commit preserve rows;
表已创建。
3.创建runstats包。其中包含3个简单的API调用：
runstats包：
SQL> 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 /
程序包已创建。
参数P_DIFFERENCE_THRESHOLD用于控制最后打印的数据量。runstats会收集并得到每次运行的统计结果和闩信息，然后打印一个报告，说明每次测试（每个方法）使用了多少资源，以及不同测试（不同方法）的结果之差。可以使用这个输入参数来控制只查看差值大于这个数的统计结果闩信息。由于这个参数默认为0，所以默认情况下可以看到所有输出。
分析1：包前面是一堆全局变量，这些全局变量用于记录每次运行的耗用时间：
SQL> create or replace package body runstats_pkg
2 as
3
4 g_start number;
5 g_run1 number;
6 g_run2 number;
7
8 /*RS_start例程。清空保存统计结果的表，并填入"上一次"(before)得到的
9 统计结果和闩信息。然后获得当前定时器值，这是一种时钟，可用于计算耗用时间
10 */
11 procedure rs_start
12 is
13 begin
14 delete from run_stats;
15
16 insert into run_stats
17 select 'before', stats.*
18 from stats;
19
20 g_start := dbms_utility.get_time;
21 end;
22
23 /*RS_MIDDLE例程
24 把第一次测试运行的耗用时间记录在g_run1中。
25 然后插入当前的一组统计结果和闩信息。
26 如果把这些值与先前在RS_START中保存的值相减，就会发现第一个方法使用了
27 多少闩，以及使用了多少游标（一种统计结果）
28 */
29 procedure rs_middle
30 is
31 begin
32 g_run1 := (dbms_utility.get_time - g_start);
33
34 insert into run_stats
35 select 'after 1', stats.*
36 from stats;
37
38 g_start := dbms_utility.get_time;
39 end;
40
41 procedure rs_stop(p_difference_threshold in number default 0)
42 is
43 begin
44 g_run2 := (dbms_utility.get_time - g_start);
45
46 dbms_output.put_line
47 ('Run1 ran in ' || g_run1 || ' hsecs');
48 dbms_output.put_line
49 ('Run2 ran in ' || g_run2 || ' hsecs');
50 dbms_output.put_line
51 ('Run 1 ran in ' || round(g_run1 / g_run2 * 100, 2) ||
52 '% of the time');
53 dbms_output.put_line(chr(9));
54
55 insert into run_stats
56 select 'after 2', stats.*
57 from stats;
58
59 dbms_output.put_line
60 (rpad('Name',30) || lpad('Run1',10) ||
61 lpad('Run2',10) || lpad('Diff',10));
62
63 for x in
64 (select rpad(a.name,30) ||
65 to_char(b.value - a.value, '9,999,999') ||
66 to_char(c.value - b.value, '9,999,999') ||
67 to_char(((c.value - b.value) - (b.value - a.value)),'9,999,999') data
68 from run_stats a, run_stats b, run_stats c
69 where a.name = b.name
70 and b.name = c.name
71 and a.runid = 'before'
72 and b.runid = 'after 1'
73 and c.runid = 'after 2'
74 and (c.value - a.value) > 0
75 and abs((c.value - b.value) - (b.value - a.value))
76 > p_difference_threshold
77 order by abs((c.value - b.value) - (b.value - a.value))
78 )loop
79 dbms_output.put_line(x.data);
80 end loop;
81
82 dbms_output.put_line(chr(9));
83
84 dbms_output.put_line
85 ('Run1 latches total versus runs -- difference and pct');
86 dbms_output.put_line
87 (lpad('Run1',10) || lpad('Run2',10) ||
88 lpad('Diff',10) || lpad('Pct',8));
89
90 for x in
91 (select to_char(run1,'9,999,999') ||
92 to_char(run2,'9,999,999') ||
93 to_char(diff,'9,999,999') ||
94 to_char(round(run1/run2 * 100,2), '999.99') || '%' data
95 from (select sum(b.value - a.value) run1, sum(c.value - b.value) run2,
96 sum((c.value - b.value) - (b.value - a.value)) diff
97 from run_stats a, run_stats b, run_stats c
98 where a.name = b.name
99 and b.name = c.name
100 and a.runid = 'before'
101 and b.runid = 'after 1'
102 and c.runid = 'after 2'
103 and a.name like 'LATCH%'
104 )
105 )loop
106 dbms_output.putline(x.data);
107 end loop;
108 end;
109
110 end;
111 /
程序包体已创建。
下面使用runstats,通过例子说明如何使用runstats对批量插入（INSERT）和逐行处理进行比较，看看哪种方法效率更高。
首先建立两个表，需要插入1 000 000行记录：
SQL> @C:\Users\Binio\Desktop\scripts\chapter00\big_table.sql
表已创建。
表已更改。
输入 1 的值: 1000000
原值 3: l_rows number := &1;
新值 3: l_rows number := 1000000;
输入 1 的值: 1000000
原值 9: where rownum <= &1;
新值 9: where rownum <= 1000000;
PL/SQL 过程已成功完成。
表已更改。
PL/SQL 过程已成功完成。
COUNT(*)
----------
1000000
SQL> create table t1
2 as
3 select * from big_table
4 where 1 = 0;
表已创建。
SQL> create table t2
2 as
3 select * from big_table
4 where 1 = 0;
表已创建。
现在使用第一种方法插入记录，使用单独一条SQL语句完成批量插入。
首先调用RUNSTATS_PKG.RS_START:
SQL> exec runstats_pkg.rs_start;
PL/SQL 过程已成功完成。
SQL> insert into t1 select * from big_table;
已创建1000000行。
SQL> commit;
提交完成。
SQL> exec runstats_pkg.rs_middle;
PL/SQL 过程已成功完成。
SQL> begin
2 for x in (select * from big_table)
3 loop
4 insert into t2 values X;
5 end loop;
6 commit;
7 end;
8 /
PL/SQL 过程已成功完成。
SQL> exec runstats_pkg.rs_stop;
Run1 ran in 4916 hsecs
Run2 ran in 19192 hsecs
Run 1 ran in 25.61% of the time
Name Run1 Run2 Diff
LATCH.global KZLD latch for me 1 2 1
STAT...table fetch by rowid 3 2 -1
STAT...IMU Flushes 0 1 1
STAT...parse time elapsed 2 3 1
STAT...index fetch by key 88 87 -1
STAT...rows fetched via callba 3 2 -1
LATCH.archive control 0 2 2
LATCH.event group latch 2 4 2
STAT...global undo segment hin 0 2 2
STAT...SQL*Net roundtrips to/f 6 4 -2
STAT...parse count (hard) 1 3 2
STAT...buffer is not pinned co 176 174 -2
STAT...consistent changes 103 105 2
STAT...deferred (CURRENT) bloc 85 88 3
STAT...parse time cpu 1 4 3
LATCH.JS slv state obj latch 3 6 3
STAT...user calls 9 6 -3
LATCH.threshold alerts latch 2 5 3
STAT...commit txn count during 6 9 3
LATCH.resmgr:actses change gro 2 5 3
LATCH.kks stats 0 4 4
LATCH.job_queue_processes para 2 6 4
LATCH.OS process: request allo 3 8 5
LATCH.channel handle pool latc 3 8 5
LATCH.parameter table allocati 2 7 5
LATCH.process group creation 3 8 5
LATCH.process allocation 3 8 5
LATCH.Shared B-Tree 2 7 5
LATCH.list of block allocation 469 474 5
LATCH.kwqbsn:qsga 2 7 5
STAT...redo buffer allocation 17 22 5
LATCH.session state list latch 9 14 5
LATCH.transaction allocation 3 9 6
STAT...write clones created in 2 9 7
LATCH.FOB s.o list latch 2 9 7
LATCH.KWQMN job cache list lat 3 11 8
LATCH.resmgr:free threads list 5 14 9
LATCH.resmgr:actses active lis 5 14 9
LATCH.dummy allocation 5 14 9
LATCH.parameter list 10 0 -10
LATCH.ncodef allocation latch 1 11 10
LATCH.transaction branch alloc 1 11 10
LATCH.sort extent pool 1 11 10
LATCH.user lock 4 14 10
LATCH.library cache pin alloca 8 18 10
LATCH.session switching 1 11 10
LATCH.ksuosstats global area 3 13 10
LATCH.resmgr group change latc 7 18 11
STAT...commit cleanouts succes 4,745 4,760 15
LATCH.PL/SQL warning settings 24 39 15
STAT...commit cleanouts 4,749 4,764 15
LATCH.OS process 9 24 15
LATCH.parallel query alloc buf 8 24 16
STAT...redo log space requests 6 23 17
LATCH.qmn task queue latch 8 28 20
LATCH.library cache lock alloc 38 84 46
LATCH.KMG MMAN ready and start 16 64 48
LATCH.post/wait queue 48 100 52
LATCH.session timer 17 71 54
LATCH.client/application info 36 92 56
LATCH.OS process allocation 23 80 57
STAT...enqueue conversions 0 63 63
LATCH.AWR Alerted Metric Eleme 66 1 -65
STAT...user I/O wait time 157 235 78
LATCH.loader state object free 0 96 96
STAT...physical read total mul 631 527 -104
LATCH.SGA IO buffer pool latch 631 527 -104
STAT...calls to kcmgcs 14,367 14,481 114
STAT...physical read total IO 643 528 -115
STAT...physical read IO reques 643 528 -115
STAT...hot buffers moved to he 0 119 119
LATCH.shared pool 1,490 1,622 132
LATCH.shared pool simulator 108 241 133
STAT...session cursor cache hi 172 359 187
STAT...table scans (short tabl 85 273 188
STAT...parse count (total) 176 365 189
STAT...opened cursors cumulati 176 371 195
LATCH.multiblock read objects 1,278 1,056 -222
LATCH.ASM db client latch 45 295 250
LATCH.file cache latch 85 346 261
STAT...physical reads cache pr 8,172 7,901 -271
LATCH.Consistent RBA 118 411 293
LATCH.lgwr LWN SCN 125 428 303
LATCH.active service list 111 414 303
LATCH.mostly latch-free SCN 125 429 304
LATCH.compile environment latc 60 385 325
STAT...messages sent 103 484 381
STAT...physical reads cache 8,815 8,429 -386
STAT...physical reads 8,815 8,429 -386
STAT...bytes sent via SQL*Net 1,165 766 -399
STAT...bytes received via SQL* 1,664 1,188 -476
LATCH.dml lock allocation 395 1,045 650
STAT...enqueue releases 3,339 4,001 662
STAT...enqueue requests 3,338 4,001 663
LATCH.In memory undo latch 155 846 691
STAT...calls to get snapshot s 442 1,221 779
LATCH.session idle bit 353 1,156 803
STAT...redo log space wait tim 20 886 866
LATCH.JS queue state obj latch 360 1,368 1,008
STAT...IMU undo allocation siz 2,012 880 -1,132
LATCH.library cache lock 805 2,181 1,376
LATCH.channel operations paren 388 1,956 1,568
STAT...change write time 258 2,072 1,814
LATCH.library cache pin 976 2,800 1,824
LATCH.redo allocation 673 2,553 1,880
LATCH.object queue header heap 411 2,432 2,021
LATCH.active checkpoint queue 413 2,472 2,059
STAT...dirty buffers inspected 2 3,182 3,180
LATCH.redo writing 808 3,998 3,190
LATCH.SQL memory manager worka 1,082 4,386 3,304
LATCH.library cache 1,883 5,399 3,516
LATCH.enqueues 7,213 11,448 4,235
LATCH.enqueue hash chains 7,766 12,549 4,783
LATCH.row cache objects 2,217 7,305 5,088
STAT...recursive cpu usage 13 5,323 5,310
LATCH.cache buffer handles 5,644 0 -5,644
STAT...CPU used when call star 501 6,297 5,796
STAT...CPU used by this sessio 498 6,297 5,799
STAT...DB time 730 7,689 6,959
LATCH.messages 1,653 8,857 7,204
STAT...free buffer requested 23,732 31,005 7,273
STAT...redo ordering marks 3,371 11,044 7,673
STAT...calls to kcmgas 3,544 11,244 7,700
STAT...redo subscn max counts 14,843 22,549 7,706
STAT...consistent gets from ca 45,349 53,516 8,167
STAT...consistent gets 45,349 53,516 8,167
LATCH.session allocation 135 9,309 9,174
STAT...Cached Commit SCN refer 14,359 24,157 9,798
STAT...no work - consistent re 14,662 24,866 10,204
STAT...table scan blocks gotte 14,583 24,787 10,204
STAT...IMU Redo allocation siz 312 11,072 10,760
STAT...active txn count during 1,585 13,166 11,581
STAT...cleanout - number of kt 1,591 13,173 11,582
STAT...consistent gets - exami 1,855 13,442 11,587
LATCH.undo global data 2,175 15,648 13,473
STAT...free buffer inspected 5,257 30,769 25,512
LATCH.checkpoint queue latch 6,194 48,042 41,848
LATCH.cache buffers lru chain 30,722 95,001 64,279
LATCH.simulator lru latch 9,106 99,487 90,381
LATCH.simulator hash latch 14,246 106,080 91,834
LATCH.object queue header oper 49,171 155,017 105,846
STAT...table scan rows gotten 1,001,020 1,689,466 688,446
STAT...redo entries 73,679 1,047,947 974,268
STAT...buffer is pinned count 985,587 0 -985,587
STAT...db block gets 80,674 1,071,949 991,275
STAT...db block gets from cach 80,674 1,071,949 991,275
STAT...session logical reads 126,023 1,125,465 999,442
STAT...execute count 176 1,000,364 1,000,188
STAT...recursive calls 2,638 1,014,318 1,011,680
STAT...db block changes 118,639 2,074,040 1,955,401
STAT...physical read total byt####################-3,162,112
STAT...physical read bytes ####################-3,162,112
LATCH.cache buffers chains 519,544 5,676,060 5,156,516
STAT...undo change vector size 3,722,088####################
STAT...redo size ##############################
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
669,540 6,285,021 5,615,481 10.65%
PL/SQL 过程已成功完成。
