在有绑定变化和不绑定变量情况下,latch资源征用的对比测试,测试是让一条sql语句执行10000次,然后给出给子的执行过程中产生的资源使用情况。
SYS@ orcl >create user test identified by test default tablespace users;
User created.
SYS@ orcl >grant dba to test;
Grant succeeded.
SYS@ orcl >grant select on sys.v_$statname to test;
Grant succeeded.
SYS@ orcl >grant select on sys.V_$mystat to test;
Grant succeeded.
SYS@ orcl >grant select on sys.v_$latch to test;
Grant succeeded.
SYS@ orcl >grant select on sys.v_$timer to test;
Grant succeeded.
SYS@ orcl >conn test/test
Connected.
TEST@ 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.
TEST@ orcl >create or replace view stats
2 as select 'STAT...' || a.name name ,b.value
3 from v$statname a ,v$mystat b
4 where a.statistic#=b.statistic#
5 union all
6 select 'LATCH.' || name ,gets
7 from v$latch
8 union all
9 select 'STAT...Elapsed Time ', hsecs from v$timer;
View created.
TEST@ orcl >create or replace view stats
2 as select 'STAT...' || a.name name ,b.value
3 from v$statname a ,v$mystat b
4 where a.statistic#=b.statistic#
5 union all
6 select 'LATCH.' || name ,gets
7 from v$latch
8 union all
9 select 'STAT...Elapsed Time ', hsecs from v$timer;
View created.
TEST@ 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.
TEST@ orcl >
TEST@ 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.
TEST@ orcl >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_time;
end;
procedure rs_middle
is
begin
g_run1 := (dbms_utility.get_time-g_start);
insert into run_stats
select 'after 1', stats.* from stats;
g_start := dbms_utility.get_time;
end;
procedure rs_stop(p_difference_threshold in number default 0)
is
begin
g_run2 := (dbms_utility.get_time-g_start);
dbms_output.put_line
( 'Run1 ran in ' || g_run1 || ' hsecs' );
dbms_output.put_line
( 'Run2 ran in ' || g_run2 || ' hsecs' );
dbms_output.put_line
( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
'% of the time' );
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', 10 ) ||
lpad( 'Run2', 10 ) || lpad( 'Diff', 10 ) );
for x in
( select rpad( a.name, 30 ) ||
to_char( b.value-a.value, '9,999,999' ) ||
to_char( c.value-b.value, '9,999,999' ) ||
to_char( ( (c.value-b.value)-(b.value-a.value)), '9,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', 10 ) || lpad( 'Run2', 10 ) ||
lpad( 'Diff', 10 ) || lpad( 'Pct', 8 ) );
for x in
( select to_char( run1, '9,999,999' ) ||
to_char( run2, '9,999,999' ) ||
to_char( diff, '9,999,999' ) ||
to_char( round( run1/run2*100,2 ), '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;
/
下面开始做测试对比,先创建一个t表
TEST@ orcl >create table t (x int);
Table created.
创建第一个存储过程p1,不使用绑定变量方式执行sql 10000次
TEST@ orcl >create or replace procedure p1
2 as
3 l_cnt number;
4 begin
5 for i in 1 .. 10000
6 loop
7 execute immediate 'select count(*) from t where x='|| i into l_cnt;
8 end loop;
9 end;
10 /
Procedure created.
TEST@ orcl >create or replace procedure p1
as
l_cnt number;
begin
for i in 1 .. 10000
loop
execute immediate 'select * from t where x='|| i ;
end loop;
end;
/
创建第二个存储过程p2,使用绑定变量形式执行sql 10000次
TEST@ orcl >create or replace procedure p2
2 as
3 l_cnt number;
4 begin
5 for i in 1 .. 10000
6 loop
select count(*) into l_cnt from t where x= i ;
8 end loop;
9 end;
10 /
Procedure created.
TEST@ orcl >exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
TEST@ orcl >exec p1;
PL/SQL procedure successfully completed.
TEST@ orcl >exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
TEST@ orcl >exec p2;
PL/SQL procedure successfully completed.
TEST@ orcl >exec runstats_pkg.rs_stop;
Run1 ran in 2835 hsecs
Run2 ran in 932 hsecs
run 1 ran in 304.18% of the time
Name Run1 Run2 Diff
STAT...redo ordering marks 0 1 1
STAT...calls to kcmgas 0 1 1
STAT...redo subscn max counts 0 1 1
LATCH.Consistent RBA 2 1 -1
LATCH.archive control 0 1 1
LATCH.SQL memory manager latch 1 0 -1
LATCH.FAL Queue 0 1 1
LATCH.MinActiveScn Latch 1 0 -1
STAT...session cursor cache co 0 1 1
STAT...user I/O wait time 1 0 -1
STAT...non-idle wait time 1 0 -1
LATCH.managed standby latch 0 1 1
LATCH.FOB s.o list latch 1 0 -1
STAT...physical read IO reques 2 0 -2
STAT...physical reads cache 2 0 -2
STAT...physical reads 2 0 -2
STAT...physical read total IO 2 0 -2
LATCH.threshold alerts latch 2 0 -2
STAT...bytes received via SQL* 1,188 1,186 -2
LATCH.dummy allocation 3 1 -2
LATCH.ksz_so allocation latch 2 0 -2
LATCH.OS process: request allo 2 0 -2
LATCH.channel handle pool latc 3 1 -2
LATCH.resmgr:free threads list 2 0 -2
LATCH.parameter table manageme 2 0 -2
LATCH.ksuosstats global area 2 0 -2
LATCH.process group creation 2 0 -2
LATCH.process allocation 2 0 -2
LATCH.archive process latch 0 3 3
LATCH.undo global data 7 4 -3
LATCH.job_queue_processes para 5 2 -3
LATCH.resmgr:schema config 5 2 -3
LATCH.session state list latch 3 0 -3
LATCH.Real-time plan statistic 4 0 -4
LATCH.parallel query alloc buf 5 1 -4
LATCH.resmgr:actses change sta 17 13 -4
LATCH.resmgr:active threads 7 2 -5
LATCH.active checkpoint queue 9 4 -5
STAT...active txn count during 0 6 6
LATCH.OS process 7 1 -6
LATCH.In memory undo latch 11 5 -6
STAT...free buffer requested 12 6 -6
STAT...cleanout - number of kt 0 6 6
LATCH.mostly latch-free SCN 9 3 -6
LATCH.lgwr LWN SCN 9 3 -6
LATCH.session timer 10 3 -7
LATCH.session allocation 17 10 -7
LATCH.session idle bit 23 16 -7
LATCH.KMG MMAN ready and start 10 3 -7
LATCH.Change Notification Hash 10 3 -7
STAT...workarea memory allocat 8 0 -8
STAT...shared hash latch upgra 8 0 -8
LATCH.OS process allocation 12 3 -9
LATCH.session switching 11 1 -10
LATCH.object queue header heap 19 9 -10
LATCH.ASM db client latch 18 6 -12
LATCH.space background task la 22 7 -15
STAT...non-idle wait count 19 4 -15
LATCH.redo allocation 31 12 -19
STAT...consistent changes 60 40 -20
STAT...db block changes 75 55 -20
STAT...db block gets from cach 58 38 -20
STAT...db block gets 58 38 -20
LATCH.redo writing 34 14 -20
STAT...rows fetched via callba 22 1 -21
STAT...workarea executions - o 24 3 -21
LATCH.active service list 52 20 -32
STAT...cluster key scans 34 0 -34
STAT...cluster key scan block 35 0 -35
STAT...table fetch continued r 43 0 -43
STAT...CCursor + sql area evic 45 0 -45
STAT...undo change vector size 3,268 3,220 -48
STAT...redo size 4,380 4,332 -48
STAT...sorts (memory) 58 1 -57
STAT...index fetch by key 69 1 -68
LATCH.channel operations paren 135 43 -92
LATCH.JS queue state obj latch 180 72 -108
LATCH.call allocation 128 2 -126
STAT...opened cursors cumulati 10,157 10,015 -142
STAT...sorts (rows) 153 3 -150
STAT...execute count 10,191 10,025 -166
LATCH.messages 247 80 -167
STAT...calls to get snapshot s 10,196 10,021 -175
STAT...index scans kdiixs1 198 17 -181
LATCH.simulator hash latch 212 9 -203
LATCH.checkpoint queue latch 325 113 -212
LATCH.object queue header oper 254 19 -235
STAT...buffer is pinned count 247 7 -240
STAT...consistent gets - exami 313 25 -288
STAT...table fetch by rowid 387 36 -351
LATCH.SQL memory manager worka 628 204 -424
LATCH.shared pool sim alloc 452 1 -451
STAT...consistent gets from ca 693 64 -629
STAT...no work - consistent re 687 56 -631
STAT...buffer is not pinned co 983 88 -895
STAT...parse time cpu 925 2 -923
STAT...consistent gets from ca 1,016 89 -927
STAT...consistent gets 1,016 89 -927
STAT...session logical reads 1,074 127 -947
STAT...file io wait time 953 0 -953
STAT...parse time elapsed 983 1 -982
STAT...recursive cpu usage 1,122 62 -1,060
STAT...CPU used when call star 1,174 70 -1,104
STAT...CPU used by this sessio 1,176 66 -1,110
STAT...DB time 1,179 68 -1,111
STAT...Elapsed Time 2,840 934 -1,906
LATCH.cache buffers chains 4,104 328 -3,776
STAT...session cursor cache hi 155 10,021 9,866
STAT...sql area evicted 9,904 4 -9,900
STAT...parse count (hard) 10,011 4 -10,007
STAT...enqueue releases 10,018 5 -10,013
STAT...enqueue requests 10,019 5 -10,014
STAT...parse count (total) 10,128 26 -10,102
STAT...physical read total byt 16,384 0 -16,384
STAT...cell physical IO interc 16,384 0 -16,384
STAT...physical read bytes 16,384 0 -16,384
LATCH.enqueue hash chains 20,582 179 -20,403
LATCH.enqueues 20,580 176 -20,404
STAT...recursive calls 42,524 10,194 -32,330
STAT...session uga memory max 123,452 65,512 -57,940
LATCH.kks stats 64,702 27 -64,675
STAT...session uga memory 65,512 0 -65,512
LATCH.shared pool simulator 106,061 58 -106,003
LATCH.row cache objects 121,559 200 -121,359
LATCH.shared pool 473,093 10,336 -462,757
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
813,606 12,138 -801,468#######%
PL/SQL procedure successfully completed.
看run_stats对比的结果,就可以看出绑定变量的优势,当然绑定变量也有缺点,在对建有索引的字段(包括字段集),且字段(集)的集的势非常大时,使用绑定变量可能会导致查询计划错误,因而会使查询效率非常低,可以继续做相关的实验。
==============================================================
附run_stats 环境搭建
TEST@ 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.
TEST@ orcl >create or replace view stats
2 as select 'STAT...' || a.name name ,b.value
3 from v$statname a ,v$mystat b
4 where a.statistic#=b.statistic#
5 union all
6 select 'LATCH.' || name ,gets
7 from v$latch
8 union all
9 select 'STAT...Elapsed Time ', hsecs from v$timer;
View created.
TEST@ orcl >create or replace view stats
2 as select 'STAT...' || a.name name ,b.value
3 from v$statname a ,v$mystat b
4 where a.statistic#=b.statistic#
5 union all
6 select 'LATCH.' || name ,gets
7 from v$latch
8 union all
9 select 'STAT...Elapsed Time ', hsecs from v$timer;
View created.
TEST@ 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.
TEST@ orcl >
TEST@ 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.
TEST@ orcl >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_time;
end;
procedure rs_middle
is
begin
g_run1 := (dbms_utility.get_time-g_start);
insert into run_stats
select 'after 1', stats.* from stats;
g_start := dbms_utility.get_time;
end;
procedure rs_stop(p_difference_threshold in number default 0)
is
begin
g_run2 := (dbms_utility.get_time-g_start);
dbms_output.put_line
( 'Run1 ran in ' || g_run1 || ' hsecs' );
dbms_output.put_line
( 'Run2 ran in ' || g_run2 || ' hsecs' );
dbms_output.put_line
( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
'% of the time' );
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', 10 ) ||
lpad( 'Run2', 10 ) || lpad( 'Diff', 10 ) );
for x in
( select rpad( a.name, 30 ) ||
to_char( b.value-a.value, '9,999,999' ) ||
to_char( c.value-b.value, '9,999,999' ) ||
to_char( ( (c.value-b.value)-(b.value-a.value)), '9,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', 10 ) || lpad( 'Run2', 10 ) ||
lpad( 'Diff', 10 ) || lpad( 'Pct', 8 ) );
for x in
( select to_char( run1, '9,999,999' ) ||
to_char( run2, '9,999,999' ) ||
to_char( diff, '9,999,999' ) ||
to_char( round( run1/run2*100,2 ), '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;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15489979/viewspace-736447/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15489979/viewspace-736447/