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


总结

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

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页
实付 9.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值