绑定变量在OLTP环境下，被广泛的使用；这源于OLTP的特点和sql语句的执行过程，OLTP典型的事务短，类似的sql语句执行率高，并发大；Oracle在执行sql语句前会对sql语句进行hash运算，将得到的hash值和share pool中的library cache中对比，如果未命中，则这条sql语句需要执行硬解析，如果命中，则只需要进行软解析；硬解析的执行过程是先进行语义，语法分析，然后生成执行计划，最后执行sql语句，在OLTP系统中使用绑定变量可以很好的解决这个问题！
一：oltp环境下，使用绑定变量和不使用绑定变量对比
1：创建测试数据
[oracle@dg53 ~]$ sqlplus hr/hr SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 14 16:54:46 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> create table t1 as select object_id,object_name from dba_objects; Table created. SQL> create index i_t1 on t1(object_id); Index created. SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE); PL/SQL procedure successfully completed.
2：不使用绑定变量情况下，进行sql trace分析，执行1万次，需要硬解析10003次，其中包含递归解析，解析时间为19.37s,cpu消耗为17.62
SQL> alter session set tracefile_identifier='HR01'; Session altered. SQL> alter session set sql_trace=TRUE; Session altered. SQL> begin 2 for i in 1..10000 3 loop 4 execute immediate 'select * from t1 where object_id='||i; 5 end loop; 6* end; PL/SQL procedure successfully completed. SQL> alter session set sql_trace=FALSE; Session altered. OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 10003 17.62 19.37 0 0 0 0 Execute 10003 0.48 0.54 0 0 0 0 Fetch 7 0.00 0.01 1 13 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 20013 18.10 19.92 1 13 0 4 Misses in library cache during parse: 10000 10003 user SQL statements in session. 3 internal SQL statements in session. 10006 SQL statements in session. 0 statements EXPLAINed in this session. ******************************************************************************** Trace file: dg53_ora_24818_HR01.trc Trace file compatibility: 10.01.00 Sort options: default 0 session in tracefile. 10003 user SQL statements in trace file. 3 internal SQL statements in trace file. 10006 SQL statements in trace file. 10006 unique SQL statements in trace file. 80071 lines in trace file. 78 elapsed seconds in trace file. 