1、测试样例
下面的三个存储过程，分别使用了动态SQL、绑定变量、静态SQL三种编程方式。具体存储过程内容如下：
l）动态SQL
|
1
2
3
4
5
6
7
|
create
or
replace
procedure proc1
as
begin
for
i
in
1
..
100000
loop
execute immediate
'insert into t values ('
|| i ||
')'
;
commit;
end loop;
end proc1;
|
执行proc1后：
Proc1存储过程使用了动态SQL，这样就会在每次执行insert语句时，要对每一个insert语句进行硬解析，这样就增加了共享池的硬解析开销，下面是v$sqlarea视图中的结果
|
1
2
|
19
:
23
:
20
SYS@ prod> select sql_text ,PARSE_CALLS ,EXECUTIONS
from
v$sqlarea
where
sql_text like
'insert into t%'
;
|
SQL_TEXT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
insert into t values (56386) 1 1
insert into t values (58271) 1 1
insert into t values (57503) 1 1
insert into t values (58762) 1 1
insert into t values (58158) 1 1
insert into t values (57081) 1 1
insert into t values (57574) 1 1
insert into t values (56146) 1 1
insert into t values (58674) 1 1
insert into t values (56186) 1 1
insert into t values (56548) 1 1
insert into t values (57817) 1 1
insert into t values (56534) 1 1
insert into t values (56678) 1 1
insert into t values (56758) 1 1
insert into t values (57501) 1 1
insert into t values (56959) 1 1
2）绑定变量
|
1
2
3
4
5
6
|
create
or
replace
procedure proc2
as
begin
for
i
in
1
..
100000
loop
execute immediate
'insert into t values(:X)'
using i; commit;
end loop;
end proc2;
|
执行proc2：
Proc2存储过程使用了绑定变量，这样在执行过程上，就会减少硬解析的开销，降低共享池的争用。在执行过程中，v$sqlarea视图中的结果如下
|
1
2
|
19
:
29
:
21
SYS@ prod>select sql_text ,PARSE_CALLS ,EXECUTIONS
from
v$sqlarea
where
sql_text like
'insert into t%'
;
|
SQL_TEXT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
insert into t values(:X) 1 100000
Elapsed: 00:00:00.08
insert语句并没有解析1次执行1次，而是解析了1次，执行了10万次。
3）静态SQL
|
1
2
3
4
5
6
|
create
or
replace
procedure proc3
as
begin
for
i
in
1
..
100000
loop
insert
into
t values(i);
end loop;
end proc3;
|
执行proc3：
Proc3存储过程使用了静态SQL，这样在编译过程中insert语句就解析好了，而不像proc2存储过程需要在执行过程中再解析，这样节省了一些时间，具体的测试结果如下
|
1
2
|
19
:
40
:
46
SYS@ prod>select sql_text ,PARSE_CALLS ,EXECUTIONS
from
v$sqlarea
19
:
40
:
59
2
where
sql_text like
'INSERT INTO T%'
;:
|
SQL_TEXT PARSE_CALLS EXECUTIONS
-------------------------------------------------- ----------- ----------
INSERT INTO T VALUES(:B1 ) 0 100000
Elapsed: 00:00:00.05
在静态SQL中，insert语句也是使用了绑定变量，所以也是解析1次，然后多次执行。
2、测试总结
动态SQL适用于表名及查询字段名未知的情况。在已知查询字段名及表名的情况下，使用动态SQL(字符串拼接方式)会增加硬解析的开销，在这种情况下，建议使用静态SQL，这样可以提高执行效率。在存储过程用拼凑的动态sql效率并不高，有时候还不如程序直接传递sql.静态SQL是前置编译绑定，动态SQL是后期执行时才编译绑定。
