码硬解析的改进方法之二使用绑定变量

绑定变量要求变量名称，数据类型以及长度是一致，否则无法使用软解析

绑定变量（bind variable）是指在DML语句中使用一个占位符，即使用冒号后面紧跟变量名的形式，如下

select * from emp where empno=7788 --未使用绑定变量

select * from emp where empono=:eno --:eno即为绑定变量

在第二个查询中，变量值在查询执行时被提供。该查询只编译一次，随后会把查询计划存储在一个共享池（库缓存）中，以便以后获取

和重用这个查询计划。

下面使用了绑定变量，但两个变量其实质是不相同的，对这种情形，同样使用硬解析

select * from emp where empno=:eno;

select * from emp where empno=:emp_no

使用绑定变量时要求不同的会话中使用了相同的回话环境，以及优化器的规则等。

使用绑定变量的例子(参照了TOM大师的Oracle 9i&10g编程艺术)

scott@ASMDB> create table tb_test(col int); --创建表tb_test

scott@ASMDB> create or replace procedure proc1 --创建存储过程proc1使用绑定变量来插入新记录

2 as

3 begin

4 for i in 1..10000

5 loop

6 execute immediate 'insert into tb_test values(:n)' using i;

7 end loop;

8 end;

9 /

Procedure created.

scott@ASMDB> create or replace procedure proc2 --创建存储过程proc2，未使用绑定变量，因此每一个SQL插入语句都会硬解析

2 as

3 begin

4 for i in 1..10000

5 loop

6 execute immediate 'insert into tb_test values('||i||')';

7 end loop;

8 end;

9 /

Procedure created.

scott@ASMDB> exec runstats_pkg.rs_start

PL/SQL procedure successfully completed.

scott@ASMDB> exec proc1;

PL/SQL procedure successfully completed.

scott@ASMDB> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

scott@ASMDB> exec proc2;

PL/SQL procedure successfully completed.

scott@ASMDB> exec runstats_pkg.rs_stop(1000);

Run1 ran in 1769 hsecs

Run2 ran in 12243 hsecs --run2运行的时间是run1的/1769≈倍

run 1 ran in 14.45% of the time

Name Run1 Run2 Diff

LATCH.SQL memory manager worka 410 2,694 2,284

LATCH.session allocation 532 8,912 8,380

LATCH.simulator lru latch 33 9,371 9,338

LATCH.simulator hash latch 51 9,398 9,347

STAT...enqueue requests 31 10,030 9,999

STAT...enqueue releases 29 10,030 10,001

STAT...parse count (hard) 4 10,011 10,007 --硬解析的次数，前者只有四次

STAT...calls to get snapshot s 55 10,087 10,032

STAT...parse count (total) 33 10,067 10,034

STAT...consistent gets 247 10,353 10,106

STAT...consistent gets from ca 247 10,353 10,106

STAT...recursive calls 10,474 20,885 10,411

STAT...db block gets from cach 10,408 30,371 19,963

STAT...db block gets 10,408 30,371 19,963

LATCH.enqueues 322 21,820 21,498 --闩的队列数比较

LATCH.enqueue hash chains 351 21,904 21,553

STAT...session logical reads 10,655 40,724 30,069

LATCH.library cache pin 40,348 72,410 32,062 --库缓存pin

LATCH.kks stats 8 40,061 40,053

LATCH.library cache lock 318 61,294 60,976

LATCH.cache buffers chains 51,851 118,340 66,489

LATCH.row cache objects 351 123,512 123,161

LATCH.library cache 40,710 234,653 193,943

LATCH.shared pool 20,357 243,376 223,019

Run1 latches total versus runs -- difference and pct

Run1 Run2 Diff Pct

157,159 974,086 816,927 16.13% --proc2使用闩的数量也远远多于proc1，其比值是.13%

PL/SQL procedure successfully completed.

由上面的示例可知，在未使用绑定变量的情形下，不论是解析次数，闩使用的数量，队列，分配的内存，库缓存，行缓存远远高于绑定

变量的情况。因此尽可能的使用绑定变量避免硬解析产生所需的额外的系统资源。

绑定变量的优点

减少SQL语句的硬解析，从而减少因硬解析产生的额外开销(CPU,Shared pool,latch)。其次提高编程效率，减少数据库的访问次数。

绑定变量的缺点

优化器就会忽略直方图的信息，在生成执行计划的时候可能不够优化。SQL优化相对比较困难