【SQL开发】使用绑定变量 VS 不使用绑定变量


一 硬解析和软解析

说到软解析(soft prase)和硬解析(hard prase),就不能不说一下Oraclesql的处理过程。当你发出一条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算法来取得该sqlhash值,然后在library cache里查找是否存在该hash值;
假设存在,则将此sqlcache中的进行比较;
假设"相同",就将利用已有的解析树与执行计划,而省略了优化器的相关工作。这也就是软解析的过程。
诚然,如果上面的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

posted @ 2018-05-31 17:42  追梦男生  阅读(421)  评论(0编辑  收藏  举报