runstats是一个工具，能对做同一件事的两个不同方法进行比较，得出孰优孰劣的结果。只需提供两个不同的方法， 余下的事情都由runstats负责。runstats只是测量3个要素：
? 耗用时间（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的直接授权，就能直接对这些表执行SELECT操作（相应地可以自行创建 视图）；否则，可以由其他人对这些表执行SELECT操作为你创建视图，并授予你在这个视图上执行SELECT的权限。
一旦建立视图，接下来只需要一个小表来收集统计结果：
sys@DEVELOP> create global temporary table run_stats 2 ( runid varchar2(15), 3 name varchar2(80), 4 value int ) 5 on commit preserve rows; Table created.
最后，需要创建runstats包。其中包含3个简单的API调用：
runstats测试开始时调用RS_STAT（runstats开始）。
正如你想象的，RS_MIDDLE会在测试之间调用。
完成时调用RS_STOP，打印报告。
创建runstats包的规范如下：
sys@DEVELOP> 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）得到的统计结果和闩信息。然后获得 当前定时器值，这是一种时钟，可用于计算耗用时间（单位百分之一秒）：
接下来是RS_MIDDLE过程。这个过程只是把第一次测试运行的耗用时间记录在G_RUN1中。然后插入当前的一组统计结果和闩信息。 如果把这些值与先前在RS_START中保存的值相减，就会发现第一个方法使用了多少闩，以及使用了多少游标（一种统计结果）， 等等。
最后，记录下一次运行的开始时间：
完整的包体创建语句如下:
sys@DEVELOP> create or replace package body runstats_pkg 2 as 3 4 g_start number; 5 g_run1 number; 6 g_run2 number; 7 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 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）和逐行处理进行比较，
看看哪种方法效率更高。首先建立两个表，要在其中插入795,680行记录：
sys@DEVELOP> create table t1 as select * from hnsic.ac01 where 1=0; Table created. sys@DEVELOP> create table t2 as select * from hnsic.ac01 where 1=0; Table created.
接下来使用第一种方法插入记录，也就是使用单独一条SQL语句完成批量插入。首先调用RUNSTATS_PKG.RS_START：
sys@DEVELOP> exec runstats_pkg.rs_start; PL/SQL procedure successfully completed. sys@DEVELOP> insert into t1 select * from hnsic.ac01; 795680 rows created. sys@DEVELOP> commit; Commit complete.
下面准备执行第二种方法，即逐行地插入数据：
sys@DEVELOP> exec runstats_pkg.rs_middle; PL/SQL procedure successfully completed. sys@DEVELOP> begin 2 for x in (select * from hnsic.ac01) 3 loop 4 insert into t2 values x; 5 end loop; 6 commit; 7 end; 8 / PL/SQL procedure successfully completed.
最后生成报告:
sys@DEVELOP> exec runstats_pkg.rs_stop; Run1 ran in 4835 hsecs Run2 ran in 14497 hsecs run 1 ran in 33.35% of the time Name Run1 Run2 Diff LATCH.space background state object latch 1 0 -1 LATCH.file cache latch 93 94 1 LATCH.ktm global data 5 4 -1 LATCH.KTF sga latch 1 0 -1 LATCH.dml lock allocation 1 2 1 LATCH.tablespace key chain 1 2 1 LATCH.deferred cleanup latch 1 2 1 LATCH.kcbtsemkid latch 2 1 -1 LATCH.threshold alerts latch 1 2 1 LATCH.cp sga latch 1 2 1 LATCH.hash table modification latch 1 2 1 STAT...parse count (hard) 1 2 1 STAT...parse count (total) 5 4 -1 STAT...sql area evicted 1 0 -1 LATCH.ASM network state latch 1 2 1 STAT...write clones created in foreground 3 2 -1 STAT...shared hash latch upgrades - no wait 93 94 1 LATCH.ncodef allocation latch 1 2 1 STAT...deferred (CURRENT) block cleanout applications 1 2 1 STAT...commit txn count during cleanout 0 1 1 STAT...IMU Flushes 0 1 1 STAT...table fetch by rowid 94 95 1 STAT...index scans kdiixs1 94 95 1 STAT...Requests to/from client 6 4 -2 STAT...session cursor cache count 5 3 -2 STAT...redo log space wait time 2 0 -2 STAT...redo log space requests 4 2 -2 LATCH.ksv class latch 3 5 2 STAT...non-idle wait time 2 0 -2 STAT...calls to get snapshot scn: kcmgss 184 186 2 STAT...SQL*Net roundtrips to/from client 6 4 -2 LATCH.MinActiveScn Latch 0 2 2 STAT...db block gets from cache (fastpath) 103 105 2 LATCH.ksv allocation latch 4 6 2 STAT...redo entries 849,543 849,545 2 LATCH.message pool operations parent latch 4 1 -3 STAT...parse time cpu 1 4 3 STAT...user calls 9 6 -3 LATCH.kwqbsn:qsga 2 5 3 LATCH.Shared B-Tree 3 6 3 LATCH.interrupt manipulation 1 5 4 LATCH.SQL memory manager latch 1 5 4 LATCH.kokc descriptor allocation latch 2 6 4 STAT...redo buffer allocation retries 5 1 -4 LATCH.multiblock read objects 6 2 -4 LATCH.object stats modification 8 13 5 STAT...enqueue releases 3,711 3,716 5 STAT...enqueue requests 3,710 3,716 6 STAT...redo subscn max counts 23,411 23,405 -6 LATCH.corrupted undo seg latch 18 12 -6 STAT...heap block compress 7 0 -7 STAT...free buffer requested 23,406 23,414 8 LATCH.ksuosstats global area 5 14 9 STAT...calls to kcmgas 10,299 10,289 -10 STAT...redo ordering marks 10,201 10,191 -10 STAT...calls to kcmgcs 33,174 33,187 13 LATCH.loader state object freelist 14 28 14 LATCH.qmn task queue latch 15 30 15 LATCH.job_queue_processes parameter latch 11 31 20 LATCH.parallel query alloc buffer 7 31 24 STAT...consistent changes 31 56 25 STAT...db block gets from cache 870,280 870,307 27 STAT...db block gets 870,280 870,307 27 STAT...db block changes 1,672,435 1,672,465 30 STAT...active txn count during cleanout 16,027 15,995 -32 STAT...cleanout - number of ktugct calls 16,027 15,995 -32 STAT...consistent gets - examination 16,032 16,000 -32 LATCH.session timer 16 48 32 LATCH.Change Notification Hash table latch 16 48 32 LATCH.KMG MMAN ready and startup request latch 16 49 33 STAT...workarea memory allocated 40 -5 -45 STAT...messages sent 370 322 -48 LATCH.sort extent pool 2 61 59 STAT...undo change vector size #################### 60 LATCH.ASM db client latch 36 99 63 LATCH.simulator lru latch 6,903 6,977 74 LATCH.parameter list 36 125 89 LATCH.FOB s.o list latch 6 97 91 LATCH.active checkpoint queue latch 143 237 94 LATCH.global tx hash mapping 19 129 110 LATCH.transaction branch allocation 32 162 130 LATCH.space background task latch 89 270 181 LATCH.post/wait queue 68 251 183 LATCH.SGA IO buffer pool latch 193 442 249 STAT...change write time 1,393 1,648 255 LATCH.Real-time plan statistics latch 383 763 380 LATCH.shared pool simulator 106 508 402 STAT...bytes sent via SQL*Net to client 1,438 948 -490 LATCH.JS queue state obj latch 360 1,044 684 STAT...bytes received via SQL*Net from client 2,313 1,623 -690 LATCH.lgwr LWN SCN 401 1,402 1,001 LATCH.Consistent RBA 399 1,401 1,002 LATCH.session switching 7 1,009 1,002 LATCH.resmgr:actses change group 9 1,018 1,009 LATCH.resmgr group change latch 7 1,016 1,009 LATCH.compile environment latch 8 1,017 1,009 LATCH.global KZLD latch for mem in SGA 7 1,016 1,009 LATCH.mostly latch-free SCN 408 1,427 1,019 LATCH.PL/SQL warning settings 53 1,086 1,033 LATCH.In memory undo latch 298 1,793 1,495 LATCH.channel handle pool latch 15 2,024 2,009 LATCH.dummy allocation 14 2,024 2,010 LATCH.ksz_so allocation latch 13 2,023 2,010 LATCH.OS process: request allocation 13 2,023 2,010 LATCH.resmgr:active threads 13 2,023 2,010 LATCH.resmgr:free threads list 13 2,023 2,010 LATCH.process allocation 14 2,024 2,010 LATCH.process group creation 13 2,023 2,010 LATCH.list of block allocation 1,665 3,678 2,013 LATCH.Event Group Locks 17 2,035 2,018 LATCH.session state list latch 13 2,042 2,029 LATCH.transaction allocation 24 2,079 2,055 STAT...IMU undo allocation size 3,256 1,080 -2,176 LATCH.DML lock allocation 73 2,251 2,178 LATCH.parameter table management 69 2,249 2,180 LATCH.OS process allocation 113 2,315 2,202 LATCH.redo allocation 2,664 5,249 2,585 LATCH.sequence cache 21 3,048 3,027 LATCH.undo global data 23,400 26,617 3,217 LATCH.active service list 146 3,396 3,250 LATCH.cache buffer handles 42 3,356 3,314 LATCH.redo writing 1,389 4,733 3,344 LATCH.channel operations parent latch 273 3,729 3,456 STAT...CPU used when call started 2,405 6,330 3,925 STAT...CPU used by this session 2,403 6,330 3,927 STAT...DB time 2,416 6,351 3,935 LATCH.OS process 29 4,041 4,012 LATCH.call allocation 26 4,252 4,226 LATCH.session allocation 200 4,567 4,367 STAT...free buffer inspected 9,080 13,902 4,822 LATCH.messages 1,938 7,030 5,092 STAT...hot buffers moved to head of LRU 3,719 9,033 5,314 STAT...recursive cpu usage 6 5,494 5,488 LATCH.client/application info 52 7,115 7,063 STAT...consistent gets from cache 49,254 57,049 7,795 STAT...consistent gets 49,254 57,049 7,795 STAT...table scan blocks gotten 16,318 24,129 7,811 STAT...no work - consistent read gets 16,507 24,320 7,813 STAT...buffer is not pinned count 16,695 24,510 7,815 STAT...session logical reads 919,534 927,356 7,822 STAT...consistent gets from cache (fastpath) 33,128 40,954 7,826 LATCH.enqueue hash chains 8,891 17,620 8,729 LATCH.object queue header heap 12,561 21,765 9,204 STAT...session cursor cache hits 97 9,347 9,250 STAT...IMU Redo allocation size 476 11,228 10,752 STAT...redo size #################### 17,552 LATCH.session idle bit 5,575 23,796 18,221 LATCH.cache buffers lru chain 18,487 42,303 23,816 LATCH.checkpoint queue latch 17,631 49,092 31,461 LATCH.object queue header operation 122,486 188,006 65,520 STAT...session uga memory max 123,488 31,848 -91,640 LATCH.simulator hash latch 237,377 443,767 206,390 LATCH.SQL memory manager workarea list latch 1,219 237,704 236,485 STAT...session pga memory max 0 262,144 262,144 STAT...execute count 101 795,782 795,681 STAT...opened cursors cumulative 102 795,789 795,687 STAT...recursive calls 848 804,498 803,650 LATCH.shared pool 7,037 884,303 877,266 LATCH.row cache objects 25,994 1,205,408 1,179,414 LATCH.cache buffers chains #################### 7,690,411 STAT...logical read bytes from cache ############################## Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct ############################## 53.64% PL/SQL procedure successfully completed.
本文参考Expert.Oracle.Database.Architecture.9i.and.10g.Programming.Techniques.and.Solutions