tom的runstats性能比较工具 测试实验
runStats是Tom大师写的一个脚本,用于比较两个语句的时间,统计数据和栓锁使用情况。
下面使用绑定变量和不使用绑定变量查询做对比，查看时间、统计数据和拴锁使用情况。
1.先看runstats脚本内容
----1.1创建使用视图
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;
----1.2视图创建后，创建一个小表来收集统计结果
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int )
on commit preserve rows;
----1.3创建runstats包。包含3个简单的API调用
create or replace package runstats_pkg
as
procedure rs_start;
procedure rs_middle;
procedure rs_stop( p_difference_threshold in number default 0 );
end;
/
---- 参数p_difference_threshold用于控制最后打印的数据量
----1.4分析包体中的过程。包前面是一些全局变量，这些全局变量用于记录每次运行的耗用时间
create or replace package body runstats_pkg
as
g_start number;
g_run1 number;
g_run2 number;
----下面是rs_start例程
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;
----下面是rs_middle例程
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;
----这个包是最后一个过程，是rs_stop例程
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 (c.value-a.value) > 0
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(null), 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;
/
----下面就可以使用runstats了
2.使用注意
要使用runstats，需要能访问几个v$视图，还要创建runstats包。
授权中使用的对象名应该以v_$而不是v$开头。这些v$名只是同义词，他们分别指向名字以v_$开头的底层视图。
需要授予访问权限的v$视图为：v$statname、v$mystat、v$latch、v$timer
3.实验开始，创建runstats
sys@ORCL>create or replace view stats
2 as select 'STAT...' || a.name name, b.value
3 from sys.v_$statname a, sys.v_$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select 'LATCH.' || name, gets
7 from sys.v_$latch
8 union all
9 select 'STAT...Elapsed Time', hsecs from sys.v_$timer;
View created.
sys@ORCL>create global temporary table run_stats
2 ( runid varchar2(15),
3 name varchar2(80),
4 value int )
5 on commit preserve rows;
Table created.
sys@ORCL>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.
sys@ORCL>create or replace package body runstats_pkg
2 as
3
4 g_start number;
5 g_run1 number;
6 g_run2 number;
7 procedure rs_start
8 is
9 begin
10 delete from run_stats;
11
12 insert into run_stats
13 select 'before', stats.* from stats;
14
15 g_start := dbms_utility.get_cpu_time;
16 end;
procedure rs_middle
is
18 19 begin
20 g_run1 := (dbms_utility.get_cpu_time-g_start);
21
22 insert into run_stats
23 select 'after 1', stats.* from stats;
24 g_start := dbms_utility.get_cpu_time;
25
26 end;
27 procedure rs_stop(p_difference_threshold in number default 0)
28 is
29 begin
30 g_run2 := (dbms_utility.get_cpu_time-g_start);
31
32 dbms_output.put_line
33 ( 'Run1 ran in ' || g_run1 || ' cpu hsecs' );
34 dbms_output.put_line
35 ( 'Run2 ran in ' || g_run2 || ' cpu hsecs' );
36 if ( g_run2 <> 0 )
37 then
38 dbms_output.put_line
39 ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
40 '% of the time' );
41 end if;
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', 12 ) ||
49 lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );
50
51 for x in
52 ( select rpad( a.name, 30 ) ||
53 to_char( b.value-a.value, '999,999,999' ) ||
54 to_char( c.value-b.value, '999,999,999' ) ||
55 to_char( ( (c.value-b.value)-(b.value-a.value)), '999,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', 12 ) || lpad( 'Run2', 12 ) ||
75 lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );
76
77 for x in
78 ( select to_char( run1, '999,999,999' ) ||
79 to_char( run2, '999,999,999' ) ||
80 to_char( diff, '999,999,999' ) ||
81 to_char( round( run1/decode( run2, 0, to_number(null), run2) *100,2 ), '99,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.
4.创建测试用户并授权
sys@ORCL>create user shall identified by shall;
User created.
sys@ORCL>grant connect,resource to shall;
Grant succeeded.
sys@ORCL>grant select on sys.v_$statname to shall;
Grant succeeded.
sys@ORCL>grant select on sys.v_$mystat to shall;
Grant succeeded.
sys@ORCL>grant select on sys.v_$latch to shall;
Grant succeeded.
sys@ORCL>grant select on sys.v_$timer to shall;
Grant succeeded.
sys@ORCL>grant execute on sys.runstats_pkg to shall;
Grant succeeded.
5.创建测试表
shall@ORCL>create table test(zhong int);
Table created.
shall@ORCL>insert into test select rownum from dual connect by level<=100000;
100000 rows created.
6.测试过程
----（1）没有绑定变量的测试过程
shall@ORCL>create or replace procedure p_test1
2 as
3 l_cnt number;
4 begin
5 for i in 1..100000 loop
6 execute immediate 'select count(*) from test where zhong=' || i into l_cnt;
7 end loop;
8 end;
9 /
Procedure created.
----（2）使用绑定变量的测试过程
shall@ORCL>create or replace procedure p_test2
2 as
3 l_cnt number;
4 begin
5 for i in 1..100000 loop
6 select count(*) into l_cnt from test where zhong=i;
7 end loop;
8 end;
9 /
Procedure created.
7.开始测试
sys@ORCL>alter system flush shared_pool;
System altered.
shall@ORCL>set serveroutput on;
----首先调用runstats_pkg.rs_start，执行没有使用绑定变量的存储过程
shall@ORCL>exec sys.runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
shall@ORCL>exec p_test1;
PL/SQL procedure successfully completed.
----然后调用runstats_pks.rs_middle，执行绑定变量存储过程
shall@ORCL>exec sys.runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
shall@ORCL>exec p_test2;
PL/SQL procedure successfully completed.
----最后生成报告
shall@ORCL>exec sys.runstats_pkg.rs_stop;
8.测试结果对比
可以看到第一个过程硬解析近10w次，执行语句花费了5分多中，而第二个过程仅仅硬解析了7次，执行语句花费了不到2分钟。（时间换算，31750/100/60）
非绑定变量的方式导致了大量的拴锁使用。
----两次语句执行分析后，执行runstats_pkg.rs_stop会将不同的信息打印到屏幕上，当然也可以指定打印出数值大于多少的信息。
shall@ORCL>exec sys.runstats_pkg.rs_stop;
Run1 ran in 31372 cpu hsecs
Run2 ran in 10888 cpu hsecs
run 1 ran in 288.13% of the time
Name Run1 Run2 Diff
STAT...parse time cpu 20,053 2 -20,051
STAT...recursive cpu usage 31,149 10,851 -20,298
STAT...parse time elapsed 20,321 2 -20,319
STAT...CPU used when call star 31,378 10,893 -20,485
STAT...CPU used by this sessio 31,377 10,889 -20,488
STAT...DB time 31,750 10,986 -20,764
LATCH.checkpoint queue latch 26,947 3,072 -23,875
LATCH.AWR Alerted Metric Eleme 25,267 0 -25,267
LATCH.SQL memory manager worka 41,363 4,145 -37,218
STAT...session cursor cache hi 34,579 -31,047 -65,626
STAT...sql area evicted 97,400 3 -97,397
STAT...table scans (short tabl 200,000 100,001 -99,999
STAT...parse count (hard) 100,014 7 -100,007
STAT...enqueue releases 100,020 8 -100,012
STAT...enqueue requests 100,021 8 -100,013
STAT...opened cursors cumulati 200,119 100,023 -100,096
STAT...execute count 200,145 100,030 -100,115
STAT...calls to get snapshot s 200,151 100,030 -100,121
STAT...session uga memory 58,008 -58,008 -116,016
STAT...Elapsed Time 174,792 17,960 -156,832
STAT...session pga memory 65,536 -131,072 -196,608
STAT...parse count (total) 200,111 24 -200,087
LATCH.call allocation 203,206 41 -203,165
LATCH.enqueue hash chains 213,910 853 -213,057
LATCH.shared pool simulator 408,267 459 -407,808
STAT...recursive calls 701,048 100,171 -600,877
STAT...calls to kcmgcs 1,700,029 800,034 -899,995
LATCH.row cache objects 3,380,124 5,490 -3,374,634
STAT...table scan blocks gotte 25,900,000 21,200,047 -4,699,953
STAT...no work - consistent re 25,900,547 21,200,108 -4,700,439
STAT...consistent gets from ca 27,600,557 22,000,126 -5,600,431
STAT...consistent gets 27,600,806 22,000,166 -5,600,640
STAT...consistent gets from ca 27,600,806 22,000,166 -5,600,640
STAT...session logical reads 27,600,877 22,000,216 -5,600,661
LATCH.shared pool 6,053,904 102,111 -5,951,793
LATCH.cache buffers chains 55,488,477 44,006,437 -11,482,040
STAT...table scan rows gotten ####################################
STAT...logical read bytes from####################################
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
67,150,571 45,332,252 -21,818,319 148.13%
PL/SQL procedure successfully completed.
reference http://blog.itpub.net/29254281/viewspace-1273911/
9.插入测试（琢行插入和批量插入10000条记录）
----1）环境准备
sys@ORCL>select count(*) from dba_objects;
COUNT(*)
----------
86563
sys@ORCL>grant select on dba_objects to shall;
Grant succeeded.
shall@ORCL>create table t1 as select * from dba_objects where 1=0;
Table created.
shall@ORCL>create table t2 as select * from dba_objects where 1=0;
Table created.
shall@ORCL>select count(*) from t1;
COUNT(*)
----------
0
shall@ORCL>select count(*) from t2;
COUNT(*)
----------
0
----2）开始测试
----批量插入
shall@ORCL>exec sys.runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
shall@ORCL>insert into t1 select * from dba_objects;
86565 rows created.
shall@ORCL>commit;
Commit complete.
----琢行插入
shall@ORCL>exec sys.runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
shall@ORCL>begin
2 for i in (select * from dba_objects) loop
3 insert into t2 values i;
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
----3）结果对比（只显示数值大于100000的信息）
shall@ORCL>exec sys.runstats_pkg.rs_stop(100000);
Run1 ran in 37 cpu hsecs
Run2 ran in 144 cpu hsecs
run 1 ran in 25.69% of the time
Name Run1 Run2 Diff
STAT...session uga memory 0 130,976 130,976
STAT...db block changes 9,801 180,901 171,100
LATCH.cache buffers chains 49,805 484,073 434,268
STAT...undo change vector size 340,568 5,891,368 5,550,800
STAT...redo size 10,131,092 32,885,812 22,754,720
STAT...logical read bytes from 128,696,320 842,702,848 714,006,528
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
69,352 620,766 551,414 11.17%
PL/SQL procedure successfully completed.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2101594/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30130773/viewspace-2101594/
扫一扫
3425
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
