runstats工具是《 oracle database 9i/10g/11g編程藝術 深入資料庫體繫結構》作者寫的一個統計性能工具，能對做同一件事的兩個方法進行比較，得到孰優孰劣的結果。 (看到runstats想到了db2 里有runstats命令收集統計信息) runststs工具主要測量三個要
runstats工具是《 oracle database 9i/10g/11g編程藝術 深入資料庫體繫結構》作者寫的一個統計性能工具，能對做同一件事的兩個方法進行比較，得到孰優孰劣的結果。
(看到runstats想到了db2 里有runstats命令收集統計信息)
runststs工具主要測量三個要素
- 牆上時鐘（wall clock) 或耗用時間（elapsed time)
- 系統統計結果，會併排地顯示每個方法做某件事（如執行一個解析調用）的次數，並展示出二者之差
- 閂定（latch）這個是報告的關鍵輸出
要使用該工具，需要能訪問V$視圖，並創建一個表來存儲統計結果，還需要創建runstats包，下麵是在scott用戶下創建該工具，以下試驗在ORACLE 11.2.0.1.0上進行
使用SYS用戶登錄，執行以下語句
--預設scott無創建視圖許可權,創建視圖時會報ORA-01031: insufficient privileges grant create view to scott; --將以下4個動態性能視圖原表SELECT許可權賦給scott grant SELECT on v_$statname to scott ; grant SELECT on v_$mystat to scott ; grant SELECT on v_$latch to scott ; grant SELECT on v_$timer to scott ;
scott用戶下登錄，執行以下語句
--創建統計結果表 create or replace view stats as select 'STAT...' || a.name name, b.value from sys.v_$statname a, sys.v_$mystat b where a.statistic# = b.statistic# union all select 'LATCH.' || name, gets from sys.v_$latch union all select 'STAT...Elapsed Time', hsecs from sys.v_$timer; --創建臨時表收集統計結果 create global temporary table run_stats (runid varchar2(15), name varchar2(80), value int) on commit preserve rows; --創建runstats包 -- runstats包含3個API，runstats測試開始時調用rs_start，rs_middle會在測試中調用，完成時調用rs_stop，列印報告 -- rs_stop的p_difference_threshold參數，用來控制最後列印的數據量，輸入這個參數可只查看差值大於參數的統計結果和閂信息，預設為0全部顯示 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_cpu_time; end; procedure rs_middle is begin g_run1 := (dbms_utility.get_cpu_time-g_start); insert into run_stats select 'after 1', stats.* from stats; g_start := dbms_utility.get_cpu_time; end; --列印每次運行累計CPU時間，分別列印兩次運行的統計結果和閂值（只列印超過p_difference_threshold的結果） procedure rs_stop(p_difference_threshold in number default 0) is begin g_run2 := (dbms_utility.get_cpu_time-g_start); dbms_output.put_line ( 'Run1 ran in ' || g_run1 || ' cpu hsecs' ); dbms_output.put_line ( 'Run2 ran in ' || g_run2 || ' cpu 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; /
工具創建好了之後，可以拿個例子來測試一下，把下麵語句寫入test.sql，做成一個SQL文件,sqlplus中執行
drop table testStat;
create table testStat(id varchar2(10));
exec runstats_pkg.rs_start;
exec dbms_output.put_line('rs_start....');
insert into testStat select level from dual connect by level <=500000;
commit;
exec dbms_output.put_line('insert completed....');
exec runstats_pkg.rs_middle;
exec dbms_output.put_line('rs_middle....');
begin
for i in 1 .. 500000
loop
insert into testStat values (i);
end loop;
commit;
end;
/
exec dbms_output.put_line('loop insert....');
exec runstats_pkg.rs_stop(0);
結果如下：
[oracle@RHEL65 test]$ sqlplus scott/oracle@orcl @t.sql SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 30 16:54:09 2016 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Table dropped. Table created. PL/SQL procedure successfully completed. rs_start.... PL/SQL procedure successfully completed. 500000 rows created. Commit complete. insert completed.... PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. rs_middle.... PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. loop insert.... PL/SQL procedure successfully completed. Run1 ran in 66 cpu hsecs Run2 ran in 2217 cpu hsecs run 1 ran in 2.98% of the time Name Run1 Run2 Diff STAT...opened cursors current -1 0 1 STAT...redo synch writes 2 1 -1 STAT...commit txn count during 2 3 1 STAT...IMU Flushes 2 1 -1 STAT...rows fetched via callba 5 4 -1 STAT...cursor authentications 0 1 1 STAT...buffer is pinned count 1 2 1 STAT...parse time elapsed 1 0 -1 LATCH.channel handle pool latc 2 1 -1 LATCH.queued dump request 0 1 1 LATCH.MinActiveScn Latch 0 1 1 LATCH.Shared B-Tree 1 2 1 LATCH.hash table modification 1 0 -1 LATCH.SQL memory manager latch 0 1 1 LATCH.kwqbsn:qsga 0 1 1 LATCH.threshold alerts latch 0 1 1 STAT...IMU pool not allocated 0 2 2 STAT...IMU- failed to get a pr 0 2 2 STAT...SQL*Net roundtrips to/f 10 8 -2 LATCH.ksuosstats global area 0 2 2 LATCH.dml lock allocation 2 4 2 STAT...user calls 15 12 -3 STAT...sorts (memory) 11 8 -3 STAT...sorts (rows) 5 2 -3 LATCH.object stats modificatio 7 4 -3 LATCH.kcbtsemkid latch 0 3 3 LATCH.managed standby latch 0 3 3 LATCH.parameter list 0 3 3 LATCH.session state list latch 3 0 -3 LATCH.session switching 1 5 4 LATCH.ksv allocation latch 0 4 4 LATCH.sort extent pool 0 4 4 LATCH.deferred cleanup latch 0 4 4 LATCH.cp sga latch 0 4 4 LATCH.parallel query alloc buf 1 5 4 LATCH.ncodef allocation latch 0 4 4 LATCH.qmn task queue latch 0 4 4 LATCH.ASM network state latch 0 4 4 STAT...write clones created in 0 5 5 STAT...immediate (CURRENT) blo 14 9 -5 LATCH.resmgr:active threads 0 5 5 LATCH.resmgr:schema config 0 5 5 LATCH.job_queue_processes para 0 5 5 STAT...table scans (short tabl 6 12 6 STAT...table scan blocks gotte 4 10 6 LATCH.FAL Queue 0 6 6 LATCH.alert log latch 0 6 6 LATCH.reservation so alloc lat 0 6 6 LATCH.transaction allocation 15 8 -7 LATCH.OS process allocation 0 9 9 LATCH.KMG MMAN ready and start 0 9 9 LATCH.Change Notification Hash 0 9 9 LATCH.Real-time plan statistic 0 9 9 STAT...redo buffer allocation 0 10 10 STAT...physical read total IO 2 13 11 STAT...physical reads 2 13 11 STAT...physical reads cache 2 13 11 STAT...physical read IO reques 2 13 11 LATCH.cache buffer handles 1,064 1,076 12 LATCH.archive control 0 12 12 LATCH.Reserved Space Latch 0 12 12 LATCH.session timer 0 12 12 LATCH.kks stats 1 15 14 LATCH.shared pool simulator 10 24 14 STAT...Heap Segment Array Upda 23 8 -15 STAT...switch current to new b 31 14 -17 STAT...calls to get snapshot s 188 171 -17 STAT...cluster key scans 51 34 -17 STAT...cluster key scan block 51 34 -17 STAT...index scans kdiixs1 88 105 17 STAT...deferred (CURRENT) bloc 32 14 -18 LATCH.FIB s.o chain latch 0 18 18 STAT...consistent changes 63 44 -19 STAT...table fetch by rowid 31 50 19 STAT...shared hash latch upgra 50 70 20 LATCH.archive process latch 0 21 21 LATCH.space background task la 0 21 21 STAT...consistent gets - exami 888 912 24 STAT...index fetch by key 52 28 -24 LATCH.FOB s.o list latch 2 26 24 STAT...commit cleanouts 867 842 -25 STAT...commit cleanouts succes 861 836 -25 STAT...no work - consistent re 146 171 25 STAT...workarea memory allocat -46 -21 25 LATCH.session idle bit 35 63 28 STAT...hot buffers moved to he 0 29 29 LATCH.In memory undo latch 15 45 30 STAT...buffer is not pinned co 277 312 35 STAT...redo log space requests 0 40 40 LATCH.SGA IO buffer pool latch 2 45 43 LATCH.DML lock allocation 139 93 -46 LATCH.post/wait queue 3 51 48 LATCH.active service list 0 51 51 LATCH.file cache latch 46 108 62 STAT...cleanout - number of kt 744 817 73 STAT...active txn count during 743 816 73 LATCH.call allocation 8 82 74 LATCH.active checkpoint queue 9 84 75 LATCH.session allocation 12 115 103 LATCH.ASM db client latch 2 106 104 LATCH.object queue header heap 22 132 110 LATCH.Consistent RBA 15 129 114 LATCH.lgwr LWN SCN 15 129 114 LATCH.mostly latch-free SCN 15 131 116 STAT...table scan rows gotten 50 176 126 LATCH.message pool operations 4 130 126 STAT...enqueue releases 280 409 129 STAT...enqueue requests 280 409 129 STAT...enqueue conversions 3 147 144 LATCH.JS queue state obj latch 0 180 180 STAT...messages sent 14 202 188 STAT...file io wait time 66 276 210 STAT...non-idle wait count 21 251 230 STAT...redo log space wait tim 0 355 355 STAT...IMU undo allocation siz 712 1,080 368 STAT...change write time 3 403
