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 privilegesgrant create view to scott;--将以下4个动态性能视图原表SELECT权限赋给scottgrant 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 statsas 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_pkgas 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.sqlSQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 30 16:54:09 2016Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsTable 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 hsecsRun2 ran in 2217 cpu hsecsrun 1 ran in 2.98% of the timeName Run1 Run2 DiffSTAT...opened cursors current -1 0 1STAT...redo synch writes 2 1 -1STAT...commit txn count during 2 3 1STAT...IMU Flushes 2 1 -1STAT...rows fetched via callba 5 4 -1STAT...cursor authentications 0 1 1STAT...buffer is pinned count 1 2 1STAT...parse time elapsed 1 0 -1LATCH.channel handle pool latc 2 1 -1LATCH.queued dump request 0 1 1LATCH.MinActiveScn Latch 0 1 1LATCH.Shared B-Tree 1 2 1LATCH.hash table modification 1 0 -1LATCH.SQL memory manager latch 0 1 1LATCH.kwqbsn:qsga 0 1 1LATCH.threshold alerts latch 0 1 1STAT...IMU pool not allocated 0 2 2STAT...IMU- failed to get a pr 0 2 2STAT...SQL*Net roundtrips to/f 10 8 -2LATCH.ksuosstats global area 0 2 2LATCH.dml lock allocation 2 4 2STAT...user calls 15 12 -3STAT...sorts (memory) 11 8 -3STAT...sorts (rows) 5 2 -3LATCH.object stats modificatio 7 4 -3LATCH.kcbtsemkid latch 0 3 3LATCH.managed standby latch 0 3 3LATCH.parameter list 0 3 3LATCH.session state list latch 3 0 -3LATCH.session switching 1 5 4LATCH.ksv allocation latch 0 4 4LATCH.sort extent pool 0 4 4LATCH.deferred cleanup latch 0 4 4LATCH.cp sga latch 0 4 4LATCH.parallel query alloc buf 1 5 4LATCH.ncodef allocation latch 0 4 4LATCH.qmn task queue latch 0 4 4LATCH.ASM network state latch 0 4 4STAT...write clones created in 0 5 5STAT...immediate (CURRENT) blo 14 9 -5LATCH.resmgr:active threads 0 5 5LATCH.resmgr:schema config 0 5 5LATCH.job_queue_processes para 0 5 5STAT...table scans (short tabl 6 12 6STAT...table scan blocks gotte 4 10 6LATCH.FAL Queue 0 6 6LATCH.alert log latch 0 6 6LATCH.reservation so alloc lat 0 6 6LATCH.transaction allocation 15 8 -7LATCH.OS process allocation 0 9 9LATCH.KMG MMAN ready and start 0 9 9LATCH.Change Notification Hash 0 9 9LATCH.Real-time plan statistic 0 9 9STAT...redo buffer allocation 0 10 10STAT...physical read total IO 2 13 11STAT...physical reads 2 13 11STAT...physical reads cache 2 13 11STAT...physical read IO reques 2 13 11LATCH.cache buffer handles 1,064 1,076 12LATCH.archive control 0 12 12LATCH.Reserved Space Latch 0 12 12LATCH.session timer 0 12 12LATCH.kks stats 1 15 14LATCH.shared pool simulator 10 24 14STAT...Heap Segment Array Upda 23 8 -15STAT...switch current to new b 31 14 -17STAT...calls to get snapshot s 188 171 -17STAT...cluster key scans 51 34 -17STAT...cluster key scan block 51 34 -17STAT...index scans kdiixs1 88 105 17STAT...deferred (CURRENT) bloc 32 14 -18LATCH.FIB s.o chain latch 0 18 18STAT...consistent changes 63 44 -19STAT...table fetch by rowid 31 50 19STAT...shared hash latch upgra 50 70 20LATCH.archive process latch 0 21 21LATCH.space background task la 0 21 21STAT...consistent gets - exami 888 912 24STAT...index fetch by key 52 28 -24LATCH.FOB s.o list latch 2 26 24STAT...commit cleanouts 867 842 -25STAT...commit cleanouts succes 861 836 -25STAT...no work - consistent re 146 171 25STAT...workarea memory allocat -46 -21 25LATCH.session idle bit 35 63 28STAT...hot buffers moved to he 0 29 29LATCH.In memory undo latch 15 45 30STAT...buffer is not pinned co 277 312 35STAT...redo log space requests 0 40 40LATCH.SGA IO buffer pool latch 2 45 43LATCH.DML lock allocation 139 93 -46LATCH.post/wait queue 3 51 48LATCH.active service list 0 51 51LATCH.file cache latch 46 108 62STAT...cleanout - number of kt 744 817 73STAT...active txn count during 743 816 73LATCH.call allocation 8 82 74LATCH.active checkpoint queue 9 84 75LATCH.session allocation 12 115 103LATCH.ASM db client latch 2 106 104LATCH.object queue header heap 22 132 110LATCH.Consistent RBA 15 129 114LATCH.lgwr LWN SCN 15 129 114LATCH.mostly latch-free SCN 15 131 116STAT...table scan rows gotten 50 176 126LATCH.message pool operations 4 130 126STAT...enqueue releases 280 409 129STAT...enqueue requests 280 409 129STAT...enqueue conversions 3 147 144LATCH.JS queue state obj latch 0 180 180STAT...messages sent 14 202 188STAT...file io wait time 66 276 210STAT...non-idle wait count 21 251 230STAT...redo log space wait tim 0 355 355STAT...IMU undo allocation siz 712 1,080 368STAT...change write time 3 403 400LATCH.redo writing 49 486 437STAT...bytes sent via SQL*Net 2,379 1,894 -485LATCH.row cache objects 1,062 1,577 515STAT...non-idle wait time 2 534 532LATCH.SQL memory manager worka 7 610 603LATCH.channel operations paren 15 694 679LATCH.redo allocation 52 751 699STAT...bytes received via SQL* 3,942 3,228 -714STAT...calls to kcmgcs 1,795 1,027 -768STAT...consistent gets from ca 2,060 1,290 -770STAT...consistent gets 2,060 1,290 -770STAT...consistent gets from ca 1,087 287 -800STAT...db block gets from cach 2,252 1,216 -1,036LATCH.messages 63 1,136 1,073LATCH.enqueues 348 1,422 1,074LATCH.enqueue hash chains 566 1,722 1,156STAT...recursive cpu usage 5 1,912 1,907STAT...CPU used when call star 66 2,217 2,151STAT...CPU used by this sessio 66 2,217 2,151STAT...Elapsed Time 68 2,751 2,683STAT...DB time 67 2,751 2,684STAT...Heap Segment Array Inse 2,779 14 -2,765STAT...free buffer requested 1,063 5,148 4,085STAT...redo subscn max counts 1,021 5,124 4,103STAT...calls to kcmgas 300 4,409 4,109STAT...redo ordering marks 233 4,369 4,136LATCH.simulator lru latch 114 4,520 4,406LATCH.undo global data 1,065 5,517 4,452STAT...free buffer inspected 0 4,479 4,479LATCH.cache buffers lru chain 187 5,401 5,214LATCH.checkpoint queue latch 221 6,512 6,291STAT...IMU Redo allocation siz 540 11,212 10,672LATCH.object queue header oper 2,563 25,027 22,464LATCH.simulator hash latch 949 36,561 35,612STAT...session cursor cache hi 78 41,324 41,246STAT...physical read bytes 16,384 106,496 90,112STAT...cell physical IO interc 16,384 106,496 90,112STAT...physical read total byt 16,384 106,496 90,112STAT...HSC Heap Segment Block 2,813 500,029 497,216STAT...redo entries 6,853 504,649 497,796STAT...recursive calls 1,443 501,196 499,753STAT...execute count 172 500,170 499,998STAT...opened cursors cumulati 159 500,162 500,003LATCH.shared pool 219 500,325 500,106STAT...session logical reads 12,488 516,953 504,465STAT...db block gets 10,428 515,663 505,235STAT...db block gets from cach 10,428 515,663 505,235STAT...session pga memory 524,288 -196,608 -720,896STAT...session uga memory 785,856 -196,464 -982,320STAT...db block changes 10,145 1,009,164 999,019LATCH.cache buffers chains 41,525 2,576,396 2,534,871STAT...session pga memory max 24,772,608 0 -24,772,608STAT...session uga memory max 24,843,448 0 -24,843,448STAT...undo change vector size 1,210,048 34,006,444 32,796,396STAT...redo size 8,857,832 124,592,660 115,734,828Run1 latches total versus runs -- difference and pctRun1 Run2 Diff Pct50,679 3,171,932 3,121,253 1.60%PL/SQL procedure successfully completed.scott@ORCL>
原标题:oracle编程艺术
关键词:oracle