文档章节

绑定变量及其优缺点

彭博
 彭博
发布于 2012/03/09 16:32
字数 2290
阅读 39
收藏 0

    绑定变量是Oracle解决硬解析的首要利器,能解决OLTP系统中library cache的过度耗用以提高性能。然刀子磨的太快,使起来锋利,却容
易折断。凡事皆有利弊二性,因地制宜,因时制宜,全在如何权衡而已。本文讲述了绑定变量的使用方法,以及绑定变量的优缺点、使用场合。

 

一、绑定变量

    提到绑定变量,就不得不了解硬解析与软解析。硬解析简言之即一条SQL语句没有被运行过,处于首次运行,则需要对其进行语法分析,语
义识别,跟据统计信息生成最佳的执行计划,然后对其执行。而软解析呢,则是由于在library cache已经存在与该SQL语句一致的SQL语句文本
、运行环境,即有相同的父游标与子游标,采用拿来主义,直接执行即可。软解析同样经历语法分析,语义识别,且生成hash value ,接下来
在library cache搜索相同的hash value ,如存在在实施软解析。有关更多的硬解析与软解析以及父游标,子游标请作如下参考:
   
    有关硬解析与软解析,请参考:Oracle 硬解析与软解析
    有关父游标、子游标,请参考:父游标、子游标与共享游标
   
    绑定变量
      首先其实质是变量,有些类似于我们经常使用的替代变量,替代变量使用&占位符,只不过绑定变量使用:
      替代变量使用时为 &variable_para,相应的绑定变量则为 :bind_variable_para
      通常一个SQL语句包含动态部分和静态部分,占位符实质是SQL语句中容易发生变化的部分,通常为其条件或取值范围。动态部分在一般情
      况下(数据倾斜除外),对执行计划的生成的影响是微乎其微的。故同一SQL语句不同的动态部分产生的执行计划都是相同的。
       
二、绑定变量的使用
    1、在SQLPlus中使用绑定变量

SQL> variable eno number;                           -->使用variable定义变量                                            
SQL> exec :eno:=7788;                                                                                                  
SQL> select ename,job,sal from emp where empno=:eno;                                                                   
                                                                                                                       
ENAME      JOB              SAL                                                                                        
---------- --------- ----------                                                                                        
SCOTT      ANALYST         3000                                                                                        
                                                                                                                       
SQL> col sql_text format a55                                                                                           
SQL> select sql_id,sql_text,executions from v$sqlarea   -->首次查询后在v$sqlarea保存父游标且执行次数EXECUTIONS为1      
  2  where sql_text like '%select ename,job,sal%' and sql_text not like '%from v$sql%';                                
                                                                                                                       
SQL_ID        SQL_TEXT                                                EXECUTIONS                                       
------------- ------------------------------------------------------- ----------                                       
dbc6vx6z6n1zv select ename,job,sal from emp where empno=:eno                   1                                       
                                                                                                                       
SQL> select sql_id,hash_value,child_number,sql_text from v$sql -->查询视图v$sql查看该SQL对应的子游标,且CHILD_NUMBER为0
  2  where sql_text like '%select ename,job,sal%' and sql_text not like '%from v$sql%';                                
                                                                                                                       
SQL_ID        HASH_VALUE CHILD_NUMBER SQL_TEXT                                                                         
------------- ---------- ------------ -------------------------------------------------------                          
dbc6vx6z6n1zv 3194619899            0 select ename,job,sal from emp where empno=:eno                                   
                                                                                                                       
SQL> exec :eno:=7369;                                                                                                  
SQL> select ename,job,sal from emp where empno=:eno;  -->再次对变量赋值并查询                                          
                                                                                                                       
ENAME      JOB              SAL                                                                                        
---------- --------- ----------                                                                                        
SMITH      CLERK            800                                                                                        
                                                                                                                       
SQL> exec :eno:=7521                                                                                                   
SQL> select ename,job,sal from emp where empno=:eno;                                                                   
                                                                                                                       
ENAME      JOB              SAL                                                                                        
---------- --------- ----------                                                                                        
WARD       SALESMAN        1250                                                                                        
                                                                                                                       
SQL> select sql_id,sql_text,executions from v$sqlarea -->视图v$sqlarea中EXECUTIONS值为3,对应的SQL被执行了3次          
  2  where sql_text like '%select ename,job,sal%' and sql_text not like '%from v$sql%';                                
                                                                                                                       
SQL_ID        SQL_TEXT                                                EXECUTIONS                                       
------------- ------------------------------------------------------- ----------                                       
dbc6vx6z6n1zv select ename,job,sal from emp where empno=:eno                   3                                       
                                                                                                                       
-->视图v$sql中对应的子游标也实现了完全共享,保持CHILD_NUMBER为0                                                        
SQL> select sql_id,hash_value,child_number,sql_text from v$sql                                                         
  2  where sql_text like '%select ename,job,sal%' and sql_text not like '%from v$sql%';                                
                                                                                                                       
SQL_ID        HASH_VALUE CHILD_NUMBER SQL_TEXT                                                                         
------------- ---------- ------------ -------------------------------------------------------                          
dbc6vx6z6n1zv 3194619899            0 select ename,job,sal from emp where empno=:eno

    2、PL/SQL块中使用绑定变量

SQL> create table t(id number,val number);  -->首先创建表t                                                             
                                                                                                                       
SQL> get get_parse.sql                                                                                                 
  1  select name,value from v$mystat a join v$statname b                                                               
  2* on a.statistic#=b.statistic# where b.name like 'parse count%';                                                    
                                                                                                                       
SQL> @get_parse.sql   -->获得当前的解析情况,此时hard parase 为63                                                      
                                                                                                                       
NAME                           VALUE                                                                                   
------------------------- ----------                                                                                   
parse count (total)              394                                                                                   
parse count (hard)                63                                                                                   
parse count (failures)             1                                                                                   
                                                                                                                       
-->下面的pl/sql代码中,Oracle实现自动变量自动绑定,执行了30次的insert操作,但oracle认为每次执行的语句都是一样的        
/**************************************************/                                                                   
/* Author: Robinson Cheng                         */                                                                   
/* Blog:   http://blog.csdn.net/robinson_0612     */                                                                   
/* MSN:    robinson_0612@hotmail.com              */                                                                   
/* QQ:     645746311                              */                                                                   
/**************************************************/                                                                   
                                                                                                                       
SQL> begin                     -->执行pl/sql代码,向表t中插入30条记录                                                  
  2  for i in 1..30 loop                                                                                               
  3  insert into t values(i,i*2);                                                                                      
  4  end loop;                                                                                                         
  5  commit;                                                                                                           
  6  end;                                                                                                              
  7  /                                                                                                                 
                                                                                                                       
PL/SQL procedure successfully completed.                                                                               
                                                                                                                       
SQL>  @get_parse              -->代码执行后的结果,硬解析数量仅仅增加了3次                                             
                                                                                                                       
NAME                           VALUE                                                                                   
------------------------- ----------                                                                                   
parse count (total)              401                                                                                   
parse count (hard)                67                                                                                   
parse count (failures)             1

    3、在存储过程或包中使用绑定变量

-->存储过程和保重,对参数的传递即是使用自动绑定变量来实现,因此编程人员无须操心绑定变量问题,如下例所示:             
SQL> create or replace procedure ins_t(p_id in number,p_value in number) -->创建一个过程用于向表t插入记录             
  2  as                                                                                                               
  3    begin                                                                                                          
  4      insert into t values(p_id,p_value);                                                                          
  5      commit;                                                                                                      
  6    end;                                                                                                           
  7  /                                                                                                                
                                                                                                                      
Procedure created.                                                                                                    
                                                                                                                      
SQL> select sid,serial# from v$session where username='SCOTT';  -->获得当前用户的sid,serial#                          
                                                                                                                      
       SID    SERIAL#                                                                                                 
---------- ----------                                                                                                 
      1084        938                                                                                                 
                                                                                                                      
SQL> exec dbms_monitor.session_trace_enable(session_id=>1084,serial_num=>938);  -->对当前的session启用跟踪            
                                                                                                                      
PL/SQL procedure successfully completed.                                                                              
                                                                                                                      
SQL> exec ins_t(31,62);               -->执行存储过程                                                                 
                                                                                                                      
PL/SQL procedure successfully completed.                                                                              
                                                                                                                      
SQL> exec ins_t(32,64);                                                                                               
                                                                                                                      
PL/SQL procedure successfully completed.                                                                              
                                                                                                                      
SQL> exec dbms_monitor.session_trace_disable(session_id=>1084,serial_num=>938); -->关闭对session的跟踪                
                                                                                                                      
PL/SQL procedure successfully completed.                                                                              
                                                                                                                      
SQL> SET LINESIZE 180                                                                                                 
SQL> COLUMN trace_file FORMAT A100                                                                                    
SQL> SELECT s.sid,                   -->获得跟踪文件位置                                                              
  2  s.serial#,                                                                                                       
  3  p.spid,                                                                                                          
  4  pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||                                              
  5  '_ora_' || p.spid || '.trc' AS trace_file                                                                        
  6  FROM v$session s,                                                                                                
  7  v$process p,                                                                                                     
  8  v$parameter pa                                                                                                   
  9  WHERE pa.name = 'user_dump_dest'                                                                                 
 10  AND s.paddr = p.addr                                                                                             
 11  AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');                                                              
                                                                                                                      
       SID    SERIAL# SPID         TRACE_FILE                                                                         
---------- ---------- ------------ --------------------------------------------------------------                     
      1084        938 10883        /u02/database/CNMMBO/udump/cnmmbo_ora_10883.trc                                    
                                                                                                                      
SQL> SQL>                                                                                                             
SQL> ho pwd                                                                                                           
/users/oracle                                                                                                         
                                                                                                                      
-->使用tkprof工具格式化跟踪文件便于阅读                                                                               
SQL> ho tkprof /u02/database/CNMMBO/udump/cnmmbo_ora_10883.trc /users/oracle/ins_t.txt explain=goex_admin/goex_admin  
                                                                                                                      
TKPROF: Release 10.2.0.3.0 - Production on Fri Sep 9 12:55:18 2011                                                    
                                                                                                                      
Copyright (c) 1982, 2005, Oracle.  All rights reserved.                                                               
                                                                                                                      
SQL> ho cat /users/oracle/ins_t.txt  -->查看跟踪文件                                                                  
......                                                                                                                
BEGIN ins_t(31,62); END;                                                                                              
......                                                                                                                
INSERT INTO T      -->可以看到insert into语句中使用了绑定变量                                                         
VALUES                                                                                                                
(:B2 ,:B1 )                                                                                                           
                                                                                                                      
call     count       cpu    elapsed       disk      query    current        rows                                      
------- ------  -------- ---------- ---------- ---------- ----------  ----------                                      
Parse        0      0.00       0.00          0          0          0           0                                      
Execute      2      0.11       0.11          2        281         27           2                                      
.......

    4、在动态SQL中是使用绑定变量    

-->动态SQL中不能自动使用绑定变量,需要手动设定绑定变量                                                                 
SQL> @get_parse     -->获得当前hard parse解析情况,此时为120                                                           
                                                                                                                       
NAME                           VALUE                                                                                   
------------------------- ----------                                                                                   
parse count (total)              533                                                                                   
parse count (hard)               120                                                                                   
parse count (failures)             1                                                                                   
                                                                                                                       
SQL> begin                                                                                                             
  2  for i in 1..30 loop                                                                                               
  3  execute immediate 'insert into t values(:1,:2)' using i,i+i-2;  -->动态SQL使用绑定变量,该语句将执行30次          
  4  end loop;                                                                                                         
  5  commit;                                                                                                           
  6  end;                                                                                                              
  7  /                                                                                                                 
                                                                                                                       
PL/SQL procedure successfully completed.                                                                               
                                                                                                                       
SQL> @get_parse     --> 动态SQL执行后,尽管执行了30次,但硬解析数量仅仅增加了2次                                       
                                                                                                                       
NAME                           VALUE                                                                                   
------------------------- ----------                                                                                   
parse count (total)              537                                                                                   
parse count (hard)               122                                                                                   
parse count (failures)             1                                                                                   
                                                                                                                       
SQL> set serveroutput on;                                                                                              
SQL> get get_sal.sql    -->下面的pl/sql中使用了绑定变量                                                                
  1   DECLARE                                                                                                          
  2   TYPE emp_cur IS REF CURSOR;                                                                                      
  3   my_emp_cur emp_cur;                                                                                              
  4   my_emp_rec emp%ROWTYPE;                                                                                          
  5   BEGIN                                                                                                            
  6   OPEN my_emp_cur FOR 'select * from emp where deptno=:dno' USING 10;                                              
  7   LOOP                                                                                                             
  8   FETCH my_emp_cur INTO my_emp_rec;                                                                                
  9   EXIT WHEN my_emp_cur%NOTFOUND;                                                                                   
 10   dbms_output.put_line(my_emp_rec.ename||'''s salary is : '||my_emp_rec.sal);                                      
 11   END LOOP;                                                                                                        
 12*  END;                                                                                                             
 13  /                                                                                                                 
CLARK's salary is : 4900                                                                                               
KING's salary is : 5000                                                                                                
MILLER's salary is : 1300                                                                                              
                                                                                                                       
PL/SQL procedure successfully completed.                                                                               
                                                                                                                       
SQL> /                                                                                                                 
CLARK's salary is : 4900                                                                                               
KING's salary is : 5000                                                                                                
MILLER's salary is : 1300                                                                                              
                                                                                                                       
PL/SQL procedure successfully completed.                                                                               
                                                                                                                       
SQL> select sql_text,executions,sql_id from v$sqlarea where sql_text like 'select * from emp where deptno=:dno%';      
                                                                                                                       
SQL_TEXT                                      EXECUTIONS SQL_ID                                                        
--------------------------------------------- ---------- -------------                                                 
select * from emp where deptno=:dno                    2 c1nx6x02h655a

       
三、绑定变量的优缺点及使用场合
    优点:
        可以在library cache中共享游标,避免硬解析以及与之相关的额外开销
        在大批量数据操作时将呈数量级来减少闩锁的使用,避免闩锁的竞争
     
    缺点:
        绑定变量被使用时,查询优化器会忽略其具体值,因此其预估的准确性远不如使用字面量值真实,尤其是在表存在数据倾斜(表上的数
        据非均匀分布)的列上会提供错误的执行计划。从而使得非高效的执行计划被使用。
   
    使用场合:
        OLTP
            在OLTP系统中SQL语句重复执行频度高,但处理的数据量较少,结果集也相对较小,尤其是使用表上的索引来缩小中间结果集,其
            解析时间通常会接近或高于执行时间,因此该场合适合使用绑定变量。
       
        OLAP
            在OLAP系统中,SQL语句执行次数相对较少,但返回的数据量较大,因此多数情况下倾向于使用权标扫描更高效,其SQL语句执行时
            间远高于其解析时间,因此使用绑定变量对于总响应时间影响不大。而且增加生成低效执行计划的风险。即在在OLAP系统中使用字
            面量的性能高于使用绑定变量。
   
    注意:
        对于实际的数据库对象,如(表,视图,列等),不能使用绑定变量替换,只能替换字面量。如果对象名是在运行时生成的,则需要对其
        用字符串拼接,同时,sql只会匹配已经在共享池中相同的对象名。

 

四、相关参考

    Oracle 硬解析与软解析   
    父游标、子游标与共享游标
    启用用户进程跟踪
    PL/SQL --> 动态SQL
    PL/SQL --> 动态SQL的常见错误


原文链接: http://blog.csdn.net/robinson_0612/article/details/6904229

本文转载自:http://blog.csdn.net/robinson_0612/article/details/6904229

共有 人打赏支持
彭博

彭博

粉丝 393
博文 1071
码字总数 6801
作品 2
深圳
程序员
私信 提问
React中 `锁定`this的N种‘facade pattern’模式

不管是什么样的历史原因,或者是基于什么样的考虑。反正现在我们已经接受了中的的多面性,以及乐此不疲的使用这种多面性,来编写灵活的代码,比如借用其他对象的方法,改变回调函数的调用者等...

洛夕枫
2018/11/17
0
0
批量SQL之 BULK COLLECT 子句

BULK COLLECT 子句会批量检索结果,即一次性将结果集绑定到一个集合变量中,并从SQL引擎发送到PL/SQL引擎。通常可以在SELECT INTO、 FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。本...

0o清风徐来o0
2013/06/29
0
0
关于JavaScript事件绑定的方式

JavaScript事件绑定 本文介绍一些JavaScript事件绑定的常用方法及其优缺点,同时在最后展示一个由 Dean Edwards 写的一个比较完美的事件绑定方案。 传统方式 element.onclick = function(e){...

阿振
2015/12/04
64
0
React事件绑定几种方法测试

前提es6写法的类方法默认没有绑定this,不手动绑定this值为undefined。 因此讨论以下几种绑定方式。 一、构造函数constructor中用bind绑定 二、在调用的时候使用bind绑定this 三、在调用的时...

peakedness丶
2018/10/26
0
0
12种JavaScript MVC框架的优缺点比较

这篇文章是2012年就已经翻译了的,艾玛现在都2014中了。。。。 Gordon L. Hempton是西雅图的一位黑客和设计师,他花费了几个月的时间研究和比较了12种流行的JavaScript MVC框架,并在博客中总...

上赶的大老鼠
2014/04/16
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Httpd 整合 Tomcat 步骤

环境:Tomcat8 + Httpd2.4 工作原理:借助于Tomcat的AJP连接器实现Apache与Tomcat的通信 配置步骤: 1. 配置httpd.conf 新增: Include conf/extra/mod_jk.conf 修改:添加 index.jsp <IfM...

ZeroneLove
昨天
1
0
Docker笔记3——容器命令(未写完,明天整理接着写)

未写完,明天整理接着写 新建并启动容器 docker run docker run [OPTIONS] IMAGE [COMMEND] [ARG...] OPTIONS: --name=[容器新名字] :为容器指定一个名称 -d:后台运行容器,并返回容器ID,...

HappyBKs
昨天
1
0
2018个人年终总结

感谢领导的信任和指导,新的一年获得了很多成长和提高,改掉了很多不好的习惯。 在这一年里,我在领导的帮助下,主要完成了以下功能: 1、完成上海银行版本投资营销相关功能的开发。 2、完成车...

万山红遍
昨天
9
0
保密工作与linux系统的发展

保密工作从性质上可以分成商业方面的保密和国家安全方面的保密。由于自己从事的是IT方面的工作,工作中必然会接触涉及到计算机信息方面的相关文件。加上单位已近通过武器装备科研生产单位二级...

linux-tao
昨天
2
0
Spark共享变量

概述 Spark程序的大部分操作都是RDD操作,通过传入函数给RDD操作函数来计算。这些函数在不同的节点上并发执行,但每个内部的变量有不同的作用域,不能相互访问,所以有时会不太方便,Spark提...

仟昭
昨天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部