【SQL开发】使用绑定变量 VS 不使用绑定变量
一 硬解析和软解析
说到软解析(soft prase)和硬解析(hard prase),就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle,在执行和获取结果前,Oracle对此sql将进行几个步骤的处理过程:
1)语法检查(syntax check)
检查此sql的拼写是否语法。
2)语义检查(semantic check)
诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。
3)对sql语句进行解析(prase)
利用内部算法对sql进行解析,生成解析树(parse tree)及执行计划(execution plan)。
4)执行sql,返回结果(execute and return)
其中,软、硬解析就发生在第三个过程里:
Oracle利用内部的hash算法来取得该sql的hash值,然后在library cache里查找是否存在该hash值;
假设存在,则将此sql与cache中的进行比较;
假设"相同",就将利用已有的解析树与执行计划,而省略了优化器的相关工作。这也就是软解析的过程。
诚然,如果上面的2个假设中任有一个不成立,那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。
创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作,所以,应当极力避免硬解析,尽量使用软解析。
这就是在很多项目中,倡导开发设计人员对功能相同的代码要努力保持代码的一致性,以及要在程序中多使用绑定变量的原因。
二 不使用绑定变量 VS 使用绑定变量
1 不使用绑定变量
1)创建表并测试
SQL> set timing on
SQL> create table t1(id int);
Table created.
Elapsed: 00:00:01.18
SQL> begin
2 for i in 1 .. 100000
3 loop
4 execute immediate 'insert into t1 values('||i||')';
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:34.06
2)查看硬解析次数以及执行次数
SQL> set linesize 200
SQL> col sql_text for a40
SQL> col sql_id for a15
SQL> select sql_text,sql_id,executions,parse_calls from v$sql
2 where sql_text like 'insert into t1 values%'
3 and rownum <= 10;
SQL_TEXT SQL_ID EXECUTIONS PARSE_CALLS
---------------------------------------- --------------- ---------- -----------
insert into t1 values(99739) 4hwu069b7w058 1 1
insert into t1 values(99795) c3wc2p1y440n6 1 1
insert into t1 values(99600) ggxrmk462s138 1 1
insert into t1 values(99610) 8m3jrkgshh1bj 1 1
insert into t1 values(99857) a8wn2bw9cw1ck 1 1
insert into t1 values(99809) 87uzu2cggw1hq 1 1
insert into t1 values(99714) bddx0bx62n1qz 1 1
insert into t1 values(99559) cbgyw5tudc1yq 1 1
insert into t1 values(99745) 2xngw3w9b829n 1 1
insert into t1 values(99826) 7s29ajyy3h2nh 1 1
10 rows selected.
Elapsed: 00:00:00.01
SQL> select count(*) from v$sql
2 where sql_text like 'insert into t1 values%';
COUNT(*)
----------
461
Elapsed: 00:00:00.15
SQL>
思考:为何只有461次?
shared_pool大小有限,无法保存每条解析过的sql,会通过LRU算法踢出冷块。
2 使用绑定变量
1)清空缓存,测试
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.07
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.46
SQL> begin
2 for i in 1 .. 100000
3 loop
4 execute immediate 'insert into t1 values (:X)' using i;
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:12.06
2)查看硬解析次数以及执行次数
SQL> select sql_text,sql_id,executions,parse_calls from v$sql
2 where sql_text like 'insert into t1 values%';
SQL_TEXT SQL_ID EXECUTIONS PARSE_CALLS
---------------------------------------- --------------- ---------- -----------
insert into t1 values (:X) d1f3fv8rt9j8t 100000 1
Elapsed: 00:00:00.11