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,");
    }


总结


合理使用绑定变量后,执行的时间将得到了显著的提高,同时缓冲区的命中率得了数量级的提升,等待事件将得到了减少。


相关文章
|
3天前
|
Oracle 关系型数据库 数据库
Flink Sink to Oracle 存在字段CLOB类型,如何处理错误”ORA-01461: 仅能绑定要插入LONG的LONG值“
做Flink CDC同步数据过程中,目标是Oracle数据库,其中某个字段较大被设置为CLOB类型,其中会遇到异常,”ORA-01461: 仅能绑定要插入LONG的LONG值“
|
11月前
|
SQL 缓存 监控
为什么Oracle中包含绑定变量的SQL会有多个游标?(译文)
为了改进包含绑定变量的SQL的执行计划,Oracle在11g版本中引入了一项名为自适应游标共享(ACS,Adaptive Cursor Sharing)的新功能。
110 0
|
12月前
|
SQL Oracle 关系型数据库
Oracle优化09-绑定变量
Oracle优化09-绑定变量
187 0
|
SQL 监控 Oracle
Oracle数据库---怎样获得sql语句执行时的绑定变量
绑定变量是oracle常用的优化技术,有时候我们需要获得sql运行时绑定变量的值,用10046事件跟踪会话即可获得
469 0
|
SQL Oracle 关系型数据库
|
SQL 关系型数据库
PLSQL_性能优化系列18_Oracle Explain Plan解析计划通过Baseline绑定
2015-05-28 Created By BaoXinjian 一、摘要 1. 应用场景 当运行很久的Job突然出现性能问题时,并排除数据量突然变大,可能原因有执行的脚本的某些对应的SQL的解析计划变更 2.
1004 0
|
SQL Oracle 关系型数据库
PLSQL_性能优化系列19_Oracle Explain Plan解析计划通过Profile绑定
20150529 Created By BaoXinjian   一、摘要   1. 应用场景 当运行很久的Job突然出现性能问题时,并排除数据量突然变大,可能原因有执行的脚本的某些对应的SQL的解析计划变更,在Oracle 10g中可以通过绑定profile,在11g中可以通过baseline进行绑定 2.
788 0