执行这两个sql文件,一比较,发现使用绑定变量后,速度快多了</p> --bindtest1.sql, did not use bind variable
) U! C& m0 g' \) r% Y K* L% Z5 X declare& s' B8 w: s8 e' E: M3 K4 X
type rc is ref cursor;
0 O) i ?' f' c) |/ b( D l_rc rc;
7 h f+ h1 ?3 @% e l_dummy all_objects.object_name%type;
! Y/ L X7 ^$ } P3 v l_start number default dbms_utility.get_time;" S2 I3 k& R/ l- c
begin% R: _9 h6 O! O0 t! G' r
for i in 1..1000
5 c m. J4 a, P \, w loop2 z% N3 n/ a! f0 K
open l_rc for7 O! K$ H2 a, M# {
'select object_name from all_objects where object_id='||i;' H# V- x0 [3 T& S& u3 U+ C4 f
fetch l_rc into l_dummy;/ @/ ^/ H- j4 ]3 s
close l_rc;' c# n/ H$ H1 B/ F& n2 ^$ T
end loop; }0 ?$ P$ V" G
dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||'seconds...');3 u- [% p# E* L6 N% R3 d
end;9 B9 V1 L9 P: Q, [% v# c
/ $ ?0 G9 w# V) c; d
--bindtest2.sql, use bind variable
% M/ E6 n" V5 b9 e) c' d; { declare
* c* c! ?7 U, a( l7 }8 } type rc is ref cursor;, E5 j+ S& }. |2 P. N8 l' h% n: S' f
l_rc rc;3 v/ M( q; b1 {* q
l_dummy all_objects.object_name%type;) E. Y" L N' h9 V& C0 S1 s& y
l_start number default dbms_utility.get_time;/ Y& n# p o7 N
begin
9 J, x5 z2 f' e for i in 1..1000& Z- ^* h* \$ V% U% h/ l* @; b: h
loop
/ u" H! B6 n% h0 d! A open l_rc for - w: x. A6 R$ d! w$ Z i
'select object_name from all_objects where object_id=:x'
4 n1 t* f; `3 w( Z" r8 d$ y+ L0 ? using i;
}1 {$ p! ^4 w fetch l_rc into l_dummy;* u2 z1 V1 c8 [* d9 [; O! T/ B3 ]
close l_rc;" J1 w7 ?5 N# O
end loop;% L; X& n$ S0 d, ^0 I
dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)||'seconds...');5 ~. k% r7 ]! d z* x7 [: S
end;
$ D Y4 @3 p4 w* \ /
: @; T; Z/ h/ @/ N5 d Y# E4 @' a Oracle封锁策略% v/ h+ C7 Y Z$ n
Oracle体系结构的3个主要组件:8 H4 V% [" m$ B2 W( g% i& Q+ \+ h
1.文件:组成数据库实例的5个文件(参数文件,控制文件,数据文件,临时数据文件,重做日志文件)
* W. {8 }' ?8 {4 q9 Y 2.系统全局区域SGA( System Global Area): Java池,共享池等
7 b! U9 P+ `) E3 J( q 3.物理进程与线程: 在数据库上运行3种不同类型的进程(服务器server进程,后台backgroud进程,从属slave进程) |