Oracle-绑定变量binding variable解读
Oracle-绑定变量binding variable解读
文章目录
绑定变量概述
Oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.
一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.
硬解析不仅仅耗费大量的cpu，更重要的是会占据重要的门闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行）， 而且引起的问题不能通过增加内存条和cpu的数量来解决。
之所以这样是因为latch是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。
引申一下 latch:
Latch是用于保护SGA区中共享数据结构的一种串行化锁定机制。
Latch就像是内存上的锁，可以由一个进程非常快速地激活和释放，用于防止对一个共享内存结构进行并行访问。
如果latch不可用，那么将记录latch释放失败。
绝大多数latch问题都与没有使用绑定变量（library-cache latch（库缓存latch））、重做日志生成问题（redo-allocation latch（重做日志的分配latch ））、缓存竞争问题（cache-buffers LRU-chain latch（缓存的最近最少使用链latch））及缓存中的热块（cache-buffers chain latch（缓存链latch））有关。
当一个SQL语句提交后，Oracle 在接收到这些SQL后，会先对这个SQL做一个hash 函数运算，得到一个Hash值，然后到共享池中寻找是否有和这个hash 值匹配的SQL存在。 如果找到了，Oracle将直接使用已经存在的SQL 的执行计划去执行当前的SQL，然后将结果返回给用户。 如果在共享池中没有找到相同Hash 值的SQL，oracle 会认为这是一条新的SQL, 会进行硬解析。
而唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。
绑定变量的本质就是本来需要做Oracle 硬解析的SQL 变成软解析，以减少ORACLE 花费在SQL解析上的时间和资源。
绑定变量只是起到占位的作用，同名的绑定变量并不意味着在它们是同样的，在传递时要考虑的是传递的值与绑定变量出现顺序的对位，而不是绑定变量的名称。
简单的说，绑定变量就是拿一个变量来代替谓词常量，让Oracle每次对用户发来的SQL做hash 运算时，运算出的结果都是同样的Hash值，于是将所有的用户发来的SQL看作是同一个SQL来对象。
绑定变量是在通常情况下能提升效率，非正常的情况如下：
在字段(包括字段集)建有索引，且字段(集)的集的势非常大（也就是有个值在字段中出现的比例特别的大）的情况下，使用绑定变量可能会导致查询计划错误，因而会使查询效率非常低。这种情况最好不要使用绑定变量。
但是并不是任何情况下都需要使用绑定变量，下面是两种例外情况：
- 1．对于隔相当一段时间才执行一次的SQL语句，这是利用绑定变量的好处会被不能有效利用优化器而抵消
- 2．数据仓库的情况下。
绑定变量不能当作嵌入的字符串来使用，只能当作语句中的变量来用。不能用绑定变量来代替表名、过程名、字段名等.
从效率来看，由于oracle10G放弃了RBO，全面引入CBO，因此，在10G中使用绑定变量效率的提升比9i中更为明显。
绑定变量详解
绑定变量使用限制条件
为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享.
因此,当你执行一个SQL语句时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用。
数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.
当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等).
共享的语句必须满足三个条件:
字符级的比较
当前被执行的语句和共享池中的语句必须完全相同. 例如:
SELECT * FROM EMP;和下列每一个都不同
SELECT * from EMP;
Select * From Emp;
SELECT * FROM EMP;两个语句所指的对象必须完全相同
两个SQL语句中必须使用相同的名字的绑定变量(bind variables)
比如：
第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值) a.
select pin , name from people where pin = :blk1.pin;
select pin , name from people where pin = :blk1.pin;b.
select pin , name from people where pin = :blk1.ot_ind;
select pin , name from people where pin = :blk1.ov_ind;查询使用绑定变量可以受益的SQL
在V$SQL视图中有个 FORCE_MATCHING_SIGNATURE字段，可以识别使用绑定变量可能会获益的SQL，如果SQL已经使用绑定变量 ，则 FORCE_MATCHING_SIGNATURE对其进行标识时，将给出同样的签名。
也就是说，如果有两条或两条以上的SQL语句，除了字面量之外，其他的都是相同的，则它们将拥有相同的 FORCE_MATCHING_SIGNATURE值。使用这个特性，下面，我给出一条SQL，用来查询可以使用绑定变量进行获益的SQL语句
with match_info
as
(
select force_matching_signature,
count(*) matches,
max(sql_id || child_number) max_sql_child,
dense_rank() over(order by count(*) desc) rk
from v$sql
where force_matching_signature <> 0
and parsing_schema_name <> 'SYS'
group by force_matching_signature
having count(*) > 5
)
select sql_id, matches, parsing_schema_name schema, sql_text
from v$sql
join match_info
on (sql_id || child_number) = max_sql_child
/* where rk <= 5*/
order by matches desc;绑定变量栗子
http://blog.csdn.net/yangshangwei/article/details/53310802#t14
在Java中的使用绑定变量
String v_id = 'xxxxx';
String v_sql = 'select name from table_a where id = ? '; //嵌入绑定变量
stmt = con.prepareStatement( v_sql );
stmt.setString(1, v_id ); //为绑定变量赋值
stmt.executeQuery();在Java中，结合使用setXXX 系列方法，可以为不同数据类型的绑定变量进行赋值，从而大大优化了SQL 语句的性能。
实际工作中的应用
// 此处需要增加 工单ID 查询条件
if(MapUtils.getString(paramMap, "workorderId")!=null
&& !MapUtils.getString(paramMap, "workorderId").equals("")){
// 1,不使用绑定变量，写死的情况
qryStr.append(" AND WO.WORKORDER_ID=").append(MapUtils.getString(paramMap, "workorderId"));
// 2,使用绑定变量的写法
/**whereStr的写法，表示使用paramMap里面的workorderId去set这个绑定变量
* whereStr最后的逗号可以不切割，集成的方法中会做统一处理
* qryStr和whereStr必须相辅相成，一个 ? 号一个变量。
*/
qryStr.append(" AND WO.WORKORDER_ID=?");
whereStr.append("workorderId,");
}总结
合理使用绑定变量后，执行的时间将得到了显著的提高，同时缓冲区的命中率得了数量级的提升，等待事件将得到了减少。