一个硬解析需要经解析，制定执行路径，优化访问计划等步骤，不仅仅会耗费大量的cpu，更重要的是会占据重要的闩(latch)资源。唯一使得oracle能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是使用变量来代替sql语句中的常量。绑定变量能够使得每次提交的sql语句都完全一样。
drop table t purge;
create table t(x int);
select * from v$mystat where rownum=1;
set timing on
begin
for i in 1 .. 100000
loop
execute immediate
'insert into t values ( '||i||')';
end loop;
commit;
end;
/
耗时：00:00:41.21
begin
for i in 1..100000
loop
execute immediate
'insert into t values(:x)' using i;
end loop;
commit;
end;
/
耗时：00:00:06.36
准备环境：
SQL> drop table t;
Table dropped.
SQL> create table t(x int);
Table created.
SQL> set linesize 266
SQL> set pagesize 5000
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
未使用绑定变量：
SQL> begin
2 for i in 1..100000
3 loop execute immediate
4 'insert into t values('||i||')';
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> alter session set events '10046 trace name context off';
Session altered.
通过如下命令可以查出生成的trc文件:
select d.value
|| '/'
|| LOWER (RTRIM(i.INSTANCE, CHR(0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
from (select p.spid
from v$mystat m,v$session s, v$process p
where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
(select t.INSTANCE
FROM v$thread t,v$parameter v
WHERE v.name='thread'
AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
(select value
from v$parameter
where name='user_dump_dest') d;
TRACE_FILE_NAME
------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5624.trc
tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5624.trc home/10046_no_bind.txt sys=no sort=prsela,exeela,fchela
未使用绑定变量的执行结果:
查看10046.txt,发现总共是花费4.38秒，其中解析时间占用了3.32秒
再用10046跟踪使用绑定变量：
drop table t purge;
create table t ( x int );
set linesize 266
set pagesize 5000
alter system flush shared_pool;
alter system flush buffer_cache;
alter session set events '10046 trace name context forever,level 12';
begin
for i in 1 .. 10000
loop
execute immediate
'insert into t values ( :x )' using i;
end loop;
commit;
end;
/
alter session set events '10046 trace name context off';
用上面同样的语句查出trace文件。
查看10046.txt,发现总共是花费2.75秒，其中解析时间占用了0.03秒
注意：
1. sqlplus中通过define定义的并不是变量，而只是字符常量，define定义之后，再通过&或&&引用的时候就不需要再输入了，oracle在执行的时候会自动用定义的值进行替换，仅此而已，并不是绑定变量（&&和&一样的功能，不过&&替代过一次之后就不需要再输入了，可以多次替代）
2. 如果define定义的是字符类型，在引用时需要加上单引号；
3. oracle在解析sql时会把plsql中定义的变量转为绑定变量；
4. 存储过程中的参数会自动转化为绑定变量；
