动态SQL和静态SQL及绑定变量性能对比

古城堡

　　动态SQL和静态SQL及绑定变量性能对比
　　1、测试样例
　　下面的三个存储过程，分别使用了动态SQL、绑定变量、静态SQL三种编程方式。具体存储过程内容如下：
　　l）动态SQL
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视图中的结果
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）绑定变量
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视图中的结果如下
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
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存储过程需要在执行过程中再解析，这样节省了一些时间，具体的测试结果如下
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是后期执行时才编译绑定。