效率测试小工具runstats学习及应用示例

本文介绍了一款名为runstats的Oracle基准测试工具,详细展示了如何通过一系列步骤配置环境并使用该工具进行性能测试,包括视图创建、临时表设置、测试包定义等。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

runstats是tom写的一款很好的基准测试小工具。其安装及测试示例如下。

以下测试工具为SQL TOOLS。

1./*以sys登陆,给u1cp授权四个视图的权限*/
grant select on v_$latch to u1cp;
grant select on v_$mystat to u1cp;
grant select on V_$timer to u1cp;
grant select on v_$statname to u1cp;

2./*在u1cp下建立sys视图的同义词*/
CREATE SYNONYM v_$latch FOR sys.v_$latch;
CREATE SYNONYM v_$mystat FOR sys.v_$mystat;
CREATE SYNONYM V_$timer FOR sys.V_$timer;
CREATE SYNONYM v_$statname FOR sys.v_$statname;


3./*以u1cp登陆创建自己的统计视图。v$为v_$的同义词,v_$才是实际的底层视图,之前授权的就是v_$*/
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
union all
select 'STAT...Elapsed time', hsecs from v$timer;


4./*创建run_stats临时表*/
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int
)on commit preserve rows;


5./*创建runstat包*/
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;
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/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; 



--=================================测试==================================

create table t1 (id number);
create table t2 (id number);

exec runstats_pkg.rs_start;
Begin
for i in 1..10000 Loop
insert into t1 values(i);
end loop;
end; 


exec runstats_pkg.rs_middle;
Begin
for i in 1..10000 Loop
Execute Immediate 'insert into t2 values('||i||')';
end loop;
end;--SQL拼接
exec runstats_pkg.rs_stop;


output:

18  PL/SQL block, executed in 0.172 sec.                              
    Run1 ran in 2 cpu hsecs                                           
    Run2 ran in 744 cpu hsecs                                         
    Run 1 ran in .27 % of the time                                    
                                                                      
    Name                                  Run1        Run2        Diff
    LATCH.job_queue_processes para           0           1           1
    LATCH.ncodef allocation latch            0           1           1
    LATCH.kwqbsn:qsga                        1           0          -1
    LATCH.threshold alerts latch             1           0          -1
    LATCH.active checkpoint queue            5           4          -1
    LATCH.transaction branch alloc           0           1           1
    LATCH.sort extent pool                   0           1           1
    LATCH.resmgr:actses change gro           0           1           1
    LATCH.Shared B-Tree                      1           0          -1
    STAT..parse count (failures)             1           0          -1
    LATCH.session switching                  0           1           1
    LATCH.ksuosstats global area             0           1           1
    LATCH.event group latch                  0           1           1
    LATCH.FOB s.o list latch                 0           1           1
    STAT..rows fetched via callbac           0           1           1
    STAT..session cursor cache cou           0          -1          -1
    LATCH.ncodef allocation latch            0           1           1
    LATCH.kwqbsn:qsga                        0           1           1
    LATCH.threshold alerts latch             0           1           1
    STAT..messages sent                      1           0          -1
    LATCH.transaction branch alloc           0           1           1
    LATCH.sort extent pool                   0           1           1
    LATCH.resmgr:actses change gro           0           1           1
    LATCH.Shared B-Tree                      0           1           1
    LATCH.job_queue_processes para           0           1           1
    LATCH.session switching                  0           1           1
    LATCH.ksuosstats global area             0           1           1
    LATCH.event group latch                  0           1           1
    LATCH.FOB s.o list latch                 0           1           1
    LATCH.active checkpoint queue            4           5           1
    STAT..rows fetched via callbac           0           1           1
    STAT..session cursor cache cou           0          -1          -1
    STAT..parse count (failures)             0           1           1
    STAT..messages sent                      1           0          -1
    LATCH.session timer                      2           4           2
    LATCH.KMG MMAN ready and start           2           4           2
    LATCH.checkpoint queue latch            68          70           2
    LATCH.list of block allocation           1           3           2
    LATCH.transaction allocation             0           2           2
    LATCH.user lock                          0           2           2
    LATCH.post/wait queue                    4           2          -2
    LATCH.process allocation                 0           2           2
    LATCH.process group creation             0           2           2
    LATCH.parameter table allocati           0           2           2
    LATCH.channel handle pool latc           0           2           2
    LATCH.OS process: request allo           0           2           2
    STAT..cursor authentications             2           0          -2
    STAT..redo buffer allocation r           2           0          -2
    STAT..redo log space requests            2           0          -2
    LATCH.list of block allocation           1           3           2
    LATCH.transaction allocation             0           2           2
    LATCH.user lock                          0           2           2
    LATCH.post/wait queue                    4           2          -2
    LATCH.process allocation                 0           2           2
    LATCH.process group creation             0           2           2
    LATCH.parameter table allocati           0           2           2
    LATCH.channel handle pool latc           0           2           2
    LATCH.OS process: request allo           0           2           2
    STAT..redo buffer allocation r           2           0          -2
    STAT..redo log space requests            2           0          -2
    LATCH.JS slv state obj latch             0           3           3
    LATCH.mostly latch-free SCN              3           6           3
    LATCH.lgwr LWN SCN                       3           6           3
    LATCH.Consistent RBA                     3           6           3
    LATCH.JS slv state obj latch             0           3           3
    LATCH.parallel query alloc buf           4           0          -4
    LATCH.compile environment latc           5           9           4
    LATCH.session state list latch           0           4           4
    LATCH.qmn task queue latch               4           0          -4
    LATCH.PL/SQL warning settings           13           9          -4
    LATCH.cache buffers lru chain           18          22           4
    LATCH.dummy allocation                   0           4           4
    LATCH.resmgr:actses active lis           0           4           4
    LATCH.resmgr:free threads list           0           4           4
    STAT..buffer is pinned count             0           4           4
    LATCH.session state list latch           0           4           4
    LATCH.qmn task queue latch               0           4           4
    LATCH.dummy allocation                   0           4           4
    LATCH.resmgr:actses active lis           0           4           4
    LATCH.resmgr:free threads list           0           4           4
    LATCH.session timer                      1           5           4
    LATCH.parallel query alloc buf           0           4           4
    LATCH.compile environment latc           5           9           4
    LATCH.KMG MMAN ready and start           1           5           4
    LATCH.cache buffers lru chain           18          22           4
    STAT..heap block compress                6          10           4
    STAT..buffer is pinned count             0           4           4
    LATCH.object queue header oper         130         135           5
    LATCH.redo writing                      23          28           5
    LATCH.resmgr group change latc           0           5           5
    STAT..active txn count during           25          20          -5
    STAT..cleanout - number of ktu          25          20          -5
    STAT..calls to kcmgcs                   25          20          -5
    LATCH.resmgr group change latc           0           5           5
    LATCH.Consistent RBA                     2           7           5
    STAT..calls to kcmgcs                   20          25           5
    STAT..active txn count during           20          25           5
    STAT..cleanout - number of ktu          20          25           5
    STAT..SQL*Net roundtrips to/fr          12           6          -6
    STAT..heap block compress               11           5          -6
    STAT..SQL*Net roundtrips to/fr           6          12           6
    STAT..workarea executions - op          13          20           7
    LATCH.OS process allocation              3          10           7
    LATCH.mostly latch-free SCN              1           8           7
    LATCH.lgwr LWN SCN                       1           8           7
    LATCH.object queue header oper         129         136           7
    LATCH.session idle bit                  49          57           8
    STAT..workarea memory allocate          -1           7           8
    LATCH.PL/SQL warning settings            7          15           8
    STAT..redo entries                  10,108      10,116           8
    STAT..workarea memory allocate          -1           7           8
    LATCH.OS process                         0           9           9
    LATCH.OS process allocation              2          11           9
    LATCH.channel operations paren          55          64           9
    LATCH.OS process                         0           9           9
    STAT..user calls                        10          20          10
    STAT..change write time                  2          12          10
    STAT..redo entries                  10,117      10,107         -10
    STAT..user calls                        20          10         -10
    STAT..change write time                  2          12          10
    LATCH.active service list               18          30          12
    STAT..shared hash latch upgrad           4          16          12
    STAT..index scans kdiixs1                4          16          12
    STAT..redo log space wait time          12           0         -12
    STAT..shared hash latch upgrad           4          16          12
    STAT..redo log space wait time          12           0         -12
    STAT..index scans kdiixs1                4          16          12
    LATCH.library cache pin alloca           0          13          13
    LATCH.library cache pin alloca           0          13          13
    LATCH.redo allocation                   71          55         -16
    STAT..table fetch by rowid               0          19          19
    STAT..workarea executions - op           7          26          19
    STAT..consistent changes                40          21         -19
    LATCH.undo global data                  40          59          19
    STAT..table fetch by rowid               0          19          19
    STAT..consistent changes                21          40          19
    LATCH.library cache lock alloc           3          24          21
    LATCH.redo writing                      15          36          21
    LATCH.library cache lock alloc           3          24          21
    LATCH.client/application info            0          25          25
    LATCH.client/application info            0          25          25
    LATCH.messages                          60          86          26
    STAT..db block changes              20,258      20,230         -28
    STAT..db block changes              20,230      20,258          28
    LATCH.active service list               10          38          28
    LATCH.undo global data                  35          64          29
    STAT..cluster key scans                  4          37          33
    LATCH.In memory undo latch              10          43          33
    STAT..cluster key scans                  4          37          33
    LATCH.redo allocation                   82          44         -38
    STAT..index fetch by key                 5          44          39
    STAT..index fetch by key                 5          44          39
    STAT..sorts (memory)                     7          47          40
    STAT..cluster key scan block g           6          47          41
    STAT..cluster key scan block g           6          47          41
    STAT..sorts (memory)                     6          48          42
    STAT..session cursor cache hit          10          55          45
    LATCH.dml lock allocation               21          66          45
    LATCH.In memory undo latch               2          51          49
    STAT..execute count                 10,024      10,074          50
    LATCH.session idle bit                  27          79          52
    STAT..session cursor cache hit           6          59          53
    STAT..opened cursors cumulativ          27          83          56
    STAT..no work - consistent rea           8          67          59
    STAT..no work - consistent rea           8          67          59
    STAT..execute count                 10,018      10,080          62
    LATCH.channel operations paren          28          91          63
    LATCH.library cache load lock            8          74          66
    LATCH.library cache load lock            8          74          66
    STAT..opened cursors cumulativ          21          89          68
    LATCH.JS queue state obj latch          36         108          72
    STAT..buffer is not pinned cou           6          81          75
    STAT..buffer is not pinned cou           6          81          75
    LATCH.checkpoint queue latch            31         107          76
    LATCH.dml lock allocation                5          82          77
    LATCH.messages                          34         112          78
    STAT..consistent gets - examin          40         126          86
    STAT..consistent gets - examin          35         131          96
    STAT...Elapsed time                    819       1,087         268
    LATCH.SQL memory manager worka          75         355         280
    STAT..parse time elapsed                 5         636         631
    STAT..parse time cpu                     2         639         637
    STAT..parse time elapsed                 0         641         641
    STAT..parse time cpu                     0         641         641
    STAT..recursive cpu usage               40         717         677
    STAT..DB time                           65         745         680
    STAT..recursive cpu usage               35         722         687
    STAT..CPU used when call start          49         745         696
    STAT..DB time                           57         753         696
    STAT..CPU used by this session          47         751         704
    STAT..CPU used when call start          44         750         706
    STAT..CPU used by this session          42         756         714
    LATCH.simulator lru latch                7         727         720
    LATCH.simulator hash latch              10         730         720
    LATCH.simulator lru latch                5         729         724
    LATCH.simulator hash latch               8         732         724
    STAT...Elapsed time                    398       1,508       1,110
    STAT..bytes sent via SQL*Net t       2,120         882      -1,238
    STAT..bytes sent via SQL*Net t         882       2,120       1,238
    STAT..bytes received via SQL*N       2,807       1,426      -1,381
    STAT..bytes received via SQL*N       1,393       2,840       1,447
    LATCH.session allocation               236       1,979       1,743
    LATCH.session allocation               236       1,979       1,743
    STAT..undo change vector size      643,096     645,148       2,052
    STAT..undo change vector size      645,156     643,088      -2,068
    STAT..sorts (rows)                   4,685       2,421      -2,264
    STAT..sorts (rows)                   2,343       4,763       2,420
    STAT..redo size                  2,378,192   2,380,624       2,432
    STAT..redo size                  2,380,900   2,377,916      -2,984
    STAT..enqueue releases                  19      10,024      10,005
    STAT..enqueue requests                  20      10,025      10,005
    STAT..parse count (hard)                 4      10,010      10,006
    STAT..enqueue releases                  18      10,025      10,007
    STAT..enqueue requests                  19      10,026      10,007
    STAT..parse count (hard)                 3      10,011      10,008
    STAT..parse count (total)               26      10,043      10,017
    STAT..parse count (total)               19      10,050      10,031
    STAT..calls to get snapshot sc          32      10,084      10,052
    STAT..calls to get snapshot sc          31      10,085      10,054
    STAT..consistent gets                   85      10,233      10,148
    STAT..consistent gets from cac          85      10,233      10,148
    STAT..consistent gets from cac          74      10,244      10,170
    STAT..consistent gets                   74      10,244      10,170
    STAT..recursive calls               10,193      20,871      10,678
    STAT..recursive calls               10,191      20,873      10,682
    STAT..db block gets from cache      10,385      30,341      19,956
    STAT..db block gets                 10,385      30,341      19,956
    STAT..db block gets from cache      10,362      30,364      20,002
    STAT..db block gets                 10,362      30,364      20,002
    LATCH.enqueues                         197      20,222      20,025
    LATCH.enqueue hash chains              221      20,295      20,074
    LATCH.enqueues                         115      20,304      20,189
    LATCH.enqueue hash chains              122      20,394      20,272
    STAT..session logical reads         10,470      40,574      30,104
    STAT..session logical reads         10,436      40,608      30,172
    LATCH.kks stats                          6      37,782      37,776
    LATCH.kks stats                          6      37,782      37,776
    LATCH.library cache pin             20,337      70,613      50,276
    LATCH.library cache pin             20,271      70,679      50,408
    LATCH.library cache lock               239      60,453      60,214
    LATCH.library cache lock               221      60,471      60,250
    LATCH.cache buffers chains          51,373     113,607      62,234
    LATCH.cache buffers chains          51,251     113,729      62,478
    LATCH.row cache objects                151     120,611     120,460
    LATCH.row cache objects                133     120,629     120,496
    LATCH.library cache                 20,574     234,325     213,751
    LATCH.library cache                 20,473     234,426     213,953
    LATCH.shared pool                   10,155     229,543     219,388
    LATCH.shared pool                   10,111     229,587     219,476
    STAT..session pga memory                 0     262,144     262,144
    STAT..session pga memory                 0     262,144     262,144
                                                                      
    Run1 latches total versus runs -- difference and pct              
            Run1        Run2        Diff       Pct                    
         207,967   1,825,217   1,617,250     11.39%                   
    Total execution time 0.219 sec.                                   
 

ps:runstats_pkg.rs_stop不带参数会输出全部讯息,也可带参数(参数表示前后差异数超过的数目)

eg. runstats_pkg.rs_stop(1000)表示前后差异数超过1000的讯息(不包含1000)。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值