精华内容
下载资源
问答
  • oracle绑定变量

    2020-12-14 20:22:29
     变量绑定是OLTP系统一个非常值得关注的技术。良好的变量绑定会使OLTP系统数据库的SQL 执行速度飞快,内存效率极高;不使用绑定变量可能会使OLTP 数据库不堪重负,资源被SQL解析严重耗尽,系统运行缓慢。  ...
  • Oracle优化09-绑定变量

    千次阅读 2016-12-17 00:03:29
    思维导图系列博文Oracle-绑定变量...良好的变量绑定会使OLTP系统数据库的SQL执行的飞快,内存效率极高。 不绑定变量有可能会使OLTP数据库不堪负重,资源被SQL解析严重消耗,系统显得缓慢。本博文的案例基于Orac

    思维导图

    这里写图片描述


    系列博文

    Oracle-绑定变量binding variable解读

    Oracle-Soft Parse/Hard Parse/Soft Soft Parse解读


    概述

    绑定变量是OLTP系统中一个非常值得关注的技术点。良好的变量绑定会使OLTP系统数据库中的SQL执行的飞快,内存效率极高。 不绑定变量有可能会使OLTP数据库不堪负重,资源被SQL解析严重消耗,系统显得缓慢。

    本博文的案例基于Oracle Database 11g Enterprise Edition Release 11.2.0.4.0


    SQL究竟是如何被执行的?

    这里写图片描述

    在介绍绑定变量之前,我们需要知道SQL究竟是如何被执行的?

    当一个用户与数据库建立连接后,向数据库发送SQL语句,Oracle在接到这条SQL后,首先会将这个SQL做一个Hash函数运算,得到一个Hash值,然后到共享池中寻找是否有和这个hash值匹配的SQL存在。

    如果找到了,Oracle会直接使用已经存在的SQL的执行计划去执行当前的SQL,然后将结果返回给用户。

    如果没有找到,Oracle会认为这是一条新的SQL, 将会按照下面的顺序来执行:


    1 .语法分析

    SQL 是否符Oracle规定的语法,如果有语法错误,则向用户抛出错误信息

    这里写图片描述


    2. 语义分析

    语法分析通过之后,Oracle会对这条SQL做一些对象、权限方面的检查,查看SQL中操作的表是否存在,表中的列是否正确,用户是否有权限操作这个对象的权限等

    这里写图片描述


    3 .生成执行计划

    这个过程Oracle在经过一些列的操作之后,来做出SQL最后的执行计划,比如查看操作对象的统计信息,动态采样等等。

    如何生成执行计划的详细信息,可以参考10053事件


    4.执行SQL

    Oracle按照上一步生成的执行计划,执行SQL,并将结果返回给用户。


    以上的这些工作,我们通常称为硬分析(hard parse),其实是十分消耗系统资源的。 而对于相同Hash值的SQL已经存在于共享池中则称为软分析(soft parse)。


    绑定变量 what ,why

    绑定变量就起本质而言就是说把本来需要Oracle做硬分析的SQL变成了软分析,以减少Oracle花费在SQL解析上的时间和资源。


    是否采用绑定变量在资源消耗上对比

    下面我们来对下同一条SQL被执行10000次,绑定变量和非绑定变量在资源消耗上的情况

    采用绑定变量

    打开SQL_TRACE

    Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
    Connected as xx@xgj
    
    ##打开时间统计
    SQL> set timing on;
    
    ##为防止干扰信息,先手动的清空共享池中的信息(若生产环境慎重)
    SQL> alter system flush shared_pool;
    
    System altered
    
    Executed in 0.078 seconds
    
    ##建表
    SQL> create table t as select * from dba_objects; 
    
    Table created
    
    Executed in 0.281 seconds
    
    ##设置trace文件标识
    SQL> alter session set  tracefile_identifier='xgj_var_bind';
    
    Session altered
    
    Executed in 0 seconds
    
    ##打开SQL_TRACE
    SQL> alter session set sql_trace=true;
    
    Session altered
    
    Executed in 0.015 seconds
    
    ##执行SQL块
    SQL> begin
      2    for i  in 1 .. 10000  loop
      3        execute immediate 'select * from t where t.object_id = :i' using i ;
      4    end loop;
      5  end;
      6  /
    
    PL/SQL procedure successfully completed
    
    Executed in 0.578 seconds
    
    ##关闭SQL_TRACE
    SQL> alter session set sql_trace=false ;
    
    Session altered
    
    Executed in 0 seconds
    
    SQL> 

    对原始trace文件进行tkprof分析汇总

    在Oracle服务器端获取到trace文件后,使用tkprof进行分析汇总,查看

    oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$ls *xgj_var_bind*
    cc_ora_32363_xgj_var_bind.trc  cc_ora_32363_xgj_var_bind.trm
    
    oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$tkprof cc_ora_32363_xgj_var_bind.trc  xgj_var_bind.txt sys=no
    
    TKPROF: Release 11.2.0.4.0 - Development on Sat Dec 17 21:13:11 2016
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    

    我们截取分析汇总后的关键部分来看

    这里写图片描述

    这里写图片描述

    整个过程加上产生的递归SQL,我们可以看到整个语句的:

    ALL NON-RECURSIVE STATEMENTS + ALL RECURSIVE STATEMENTS

    • 执行时间(elapsed):0.48+0.10 = 0.58 (和刚才输出的时间大致一致)
    • CPU时间(cpu):0.48+0.09=0.57
    • 分析次数(parse):8+2=10
    • 执行次数(execute):9+10017=10025

    不采用绑定变量

    SQL> set timing on ;
    SQL> alter session set  tracefile_identifier='xgj_var_unbind';
    
    Session altered
    
    Executed in 0.016 seconds
    SQL> alter session set sql_trace=true;
    
    Session altered
    
    Executed in 0.016 seconds
    SQL> begin
      2    for x in 1 .. 10000 loop
      3        execute immediate 'select * from t where t.object_id ='||x;
      4    end loop;
      5  end;
      6  /
    
    PL/SQL procedure successfully completed
    
    Executed in 16.672 seconds  -----耗时很长
    
    SQL> 

    执行tkprof分析汇总

    这里写图片描述

    查看xgj_var_unbind.txt关键部分

    这里写图片描述

    ………… 中间省略一万万字

    这里写图片描述

    可以看到 每一条都是hard parse,非常消耗系统资源,耗时很长。

    这里写图片描述

    同样的我们统计下执行信息:

    ALL NON-RECURSIVE STATEMENTS + ALL RECURSIVE STATEMENTS

    • 执行时间(elapsed):1.28+15.38 =16.66
    • CPU时间(cpu):1.22+15.31
    • 分析次数(parse): 3+20000
    • 执行次数(execute):4+20000

    通过对比我们可以发现,在OLTP系统中,使用绑定变量的SQL资源消耗要与那远少于未绑定变量SQL的资源消耗,SQL执行的次数越多,差距越明显。

    未绑定变量SQL的资源主要消耗在产生的递归SQL上,这些SQL主要是对SQL语句做hard parse时使用的。

    试想,当一个数据库有成千上万甚至更多的用户同时执行这样的SQL,而ORACLE只做一次硬分析,后面相同的SQL只执行SQL的执行操作,势必将大大减轻数据库的资源开销。

    这就是绑定变量的由来,它并不神秘,不过是拿一个变量来代替谓词常量,让ORACLE每次对用户发送的SQL做hash运算,运算出相同的hash值,于是Oracle便将这些SQL看做同一个SQL对待而已。


    OLTP和OLAP系统中是否需要绑定变量分析

    如果你使用Oracle的图形化工具DBCA创建数据库,应该有印象,其中有一步是要求你选择数据库的类型是OLTP还是OLAP。 其实这就说明了OLTP和OLAP数据库是有很大的差异的,Oracle需要知道你选择的系统架构,以便于按照系统的架构对相应的参数值做设定,比如初始化参数。

    OLTP和OLAP的请参考之前梳理的文章 Oracle-OLAP和OLTP解读
    http://blog.csdn.net/yangshangwei/article/details/52949378


    OLTP栗子

    数据:

    SQL> drop table t;
    
    Table dropped
    
    SQL> create table t as select a.OBJECT_ID ,a.OBJECT_NAME from dba_objects a ;
    
    Table created
    
    SQL> select count(1)  from t;
    
      COUNT(1)
    ----------
         35234
    
    SQL> 循环300次,写入更多的数据   每50次提交一次数据
    SQL> begin
      2   for i in 1 .. 300  loop
      3     execute immediate 'insert into t select a.OBJECT_ID ,a.OBJECT_NAME from dba_objects a';
      4     if  mod(i,50)=0 then
      5       commit;
      6       end if;
      7   end loop;
      8  end;
      9  /
    
    
    PL/SQL procedure successfully completed
    
    SQL> select count(1)  from t;
    
      COUNT(1)
    ----------
      10605434
    
    SQL> 在object_id字段上创建索引
    SQL> create index  idx_t  on t(object_id);
    
    Index created

    操作步骤

    SQL> alter session set tracefile_identifier='xgj_oltp';
    
    Session altered
    
    SQL> alter session set sql_trace =true;
    
    Session altered
    
    
    执行两遍SQL 
    
    
    ##强制走全表扫描执行计划
    SQL> select /*+ full(t)*/  * from t where object_id = 188;
    
     OBJECT_ID OBJECT_NAME
    ---------- ---------------------
           188 SQLLOG$_PKEY
           ...........
      301 rows selected
    
    ##让Oracle自己选择执行计划
    SQL>  select   * from t where t.object_id = 188;
    
     OBJECT_ID OBJECT_NAME
    ---------- ---------------------
           188 SQLLOG$_PKEY
           ...........
      301 rows selected
    
    SQL> alter session set sql_trace=false;
    
    Session altered
    

    tkprof汇总分析

    这里写图片描述

    这里写图片描述

    这里写图片描述

    可以看到 全表扫描执行计划的SQL扫描过的数据块明显大于使用索引执行的SQL计划。

    从trace文件中可以看到,在fetch阶段,全表扫描读取了42093多个数据块,而走索引的,在fetch阶段,仅仅读取了308个数据块。


    OLAP栗子

    OLAP系统在SQL的操作中就复杂的多,OLAP数据库上大多数的时候运行是一些报表SQL,这些SQL经常会用到聚合查询(比如group by),而且结果集也是非常庞大,在这种情况下,索引并不是必然的选择,甚至有些时候全表扫描的性能会由于索引,即使相同的SQL,如果谓词条件不同,执行计划都可能不同

    数据

    SQL>  create table t2(object_id, object_name) partition by range (object_id)
      2    (partition p1 values less than (5000),
      3     partition p2 values less than (15000),
      4     partition p3 values less than (25000),
      5     partition p4 values less than (maxvalue))
      6   as select object_id, object_name from dba_objects;
    
    Table created
    
    SQL> 
    SQL> begin
      2    for x in 1 .. 300 loop
      3      insert into t2 select object_id ,object_name from dba_objects ;
      4      if mod(x,50)=0 then
      5         commit;
      6      end if;
      7    end loop;
      8  end;
      9  /
    
    PL/SQL procedure successfully completed
    
    SQL> select count(1) from t2;
    
      COUNT(1)
    ----------
      10609046
    
    SQL> select count(1) from t2 partition(p1);
    
      COUNT(1)
    ----------
       1504097
    
    SQL> select count(1) from t2 partition(p2);
    
      COUNT(1)
    ----------
       2691241
    
    SQL> select count(1) from t2 partition(p3);
    
      COUNT(1)
    ----------
             0
    
    SQL> select count(1) from t2 partition(p4);
    
      COUNT(1)
    ----------
       6413708
    
    SQL> 在分区表上建立本地索引
    SQL> create index idx_t_id on t2(object_id)  local;
    
    Index created
    
    
    ##对表做一次分析(cascade => true ,索引也会被一同分析)
    SQL>  exec dbms_stats.gather_table_stats(user,'t2',cascade => true);
    
    PL/SQL procedure successfully completed
    

    操作步骤

    我们使用如下命令

    注释:以上的命令,是在plsql客户端执行的,可以支持, 但是autotrace命令,plsql并没有很好的支持,所以我登录到了服务器所在的主机执行的,当然也可以通过sqlplus客户端操作。

    SQL> set autotrace traceonly explain ;
    

    这里写图片描述

    这里写图片描述

    这里写图片描述

    从结果中我们可以看到,虽然只是谓词的不同,但是oracle却选择了不同的执行计划,因为Oracle认为那样的计划代价最小。


    结论

    1. OLAP系统完全没有必要设置绑定变量那样只会带来负面的影响,比如导致SQL选择了错误的执行计划,让Oracle对每条SQL做hard parse ,确切知道谓词条件的值,这对执行计划的选择至关重要。 这是因为在OLAP系统中,SQL硬分析的代价是可以忽略的,系统的资源基本上是用于做大的SQL查询,和查询比较起来,SQL解析消耗的资源显然微不足道,所以得到一个最优的执行计划变得尤为重要
    2. 在OLAP系统上,让Oracle确切知道谓词条件的值,它直接决定了SQL执行计划的选择,这样做的方式就是不要绑定变量
    3. 在OLAP系统中,表索引的分析显得至关重要,因为它是Oracle为SQL做出正确的执行计划的信息来源和一句。

    bind peeking

    谈到变量绑定,我们不得不提一下从Oracle9i开始引入的一个新的特性,叫做bind peaking ,顾名思义,就是在SQL语句硬解析的时候,Oracle会看一下当前SQL谓词的值,以便于生成最佳的执行计划。

    需要强调的是,bind peaking 只发生在hard parse的时候,即SQL被第一次执行的时候,之后的变量将不会再做peeking.

    bind peeking 并不能最终解决不同谓词导致不同执行计划的问题,它只能让SQL第一次执行的时候,执行计划更加准确,并不能帮助OLAP系统解决绑定变量导致执行计划选择错误的问题,所以,OLAP依然不应该使用绑定变量。

    对于OLTP系统来讲,相同的SQL重复频率非常搞,如果优化器范湖解析SQL,势必极大地消耗资源。 另外OLTP系统用户请求的结果通常都是非常小,说基本上都会考虑使用索引。 bind peeking在第一次hard parse的时候获得了一个正确的执行计划,后面的SQL都按照这个计划来执行,可以极大地改善系统的性能,这是由OLTP系统的特性决定的。

    展开全文
  • Oracle-绑定变量binding variable解读

    千次阅读 2016-11-20 13:05:52
    绑定变量概述Oracle ,对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu,更重要的是会...

    绑定变量概述

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

    总结

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

    展开全文
  • oracle in 中绑定变量

    千次阅读 2012-06-29 14:59:00
    Oracle怎么对IN子查询使用绑定变量(2007-04-08 14:01:23) 转载▼ 分类: Oracle 在实际使用,经常会有带in的子查询,如where id in (1,2,3)这样的情况,但是如果很多这样的语句在数据库...
    Oracle怎么对IN子查询使用绑定变量(2007-04-08 14:01:23) 
     
    
    分类: Oracle

    在实际使用中,经常会有带in的子查询,如where id in (1,2,3)这样的情况,但是如果很多这样的语句在数据库中出现,将引起数据库的大量硬解析与共享池SQL碎片。所以,在实际应用中,可以采用其他方法,将这些in list给绑定起来。

    如果需要绑定in list,首先,需要创建两个类型(type)

    针对数据类型的

    CREATE OR REPLACE TYPE NUMTABLETYPE as table of number;

    针对字符串类型的(每个list的单元大小不要超过1000字节)

    create or replace type vartabletype as table of varchar2(1000);

    然后创建两个相关的函数

    数字列表函数
    create or replace function str2numList( p_string in varchar2 ) return numTableType
    as
       
    v_str long default p_string || ‘,‘;
       
    v_n number;
        v_data numTableType := numTableType();
    begin
        loop
        v_n := to_number(instr( v_str, ‘,‘ ));
       
    exit when (nvl(v_n,0) = 0);
        v_data.extend;
        v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
        v_str := substr( v_str, v_n+1 );
        end loop;
        return v_data;
    end;

    字符列表函数
    create or replace function str2varList( p_string in varchar2 ) return VarTableType
     
    as
     v_str long default p_string || ‘,‘;
     
    v_n varchar2(2000);
     v_data VarTableType := VarTableType();
     begin
        loop
           v_n :=instr( v_str, ‘,‘ );
       
    exit when (nvl(v_n,0) = 0);
        v_data.extend;
        v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
        v_str := substr( v_str, v_n+1 );
        end loop;
        return v_data;
    end;

    创建之后,我们就可以采用如下的方式来使用in list的绑定了。如可以采用如下的三种方案

    SELECT /*+ ordered use_nl(a,u) */ id, user_id, BITAND(promoted_type,4) busauth
        
    from table(STR2NUMLIST(:bind0)) a,
         bmw_users u
         where u.user_id = a.column_value;

    SELECT  /*+ leading(a) */ id, user_id, BITAND(promoted_type,4) busauth
        
    from bmw_users u where user_id in
        
    (select * from table(STR2NUMLIST(:bind0)) a);

    SELECT  /*+ index(bmw_users UK_BMW_USERS_USERID) */ id, user_id
        
    from bmw_users where user_id in
        
    (SELECT * FROM THE (SELECT CAST(STR2NUMLIST(:bind0) AS NUMTABLETYPE)
        
    FROM dual) WHERE rownum<1000); 在如上的方案中,以上语句中的hint提示,是为了稳定执行计划,防止Oraclein list的错误估计而导致走hash连接。一般建议采用第一种方法,比较简单可靠并且可以指定稳定的计划。但是要求数据库的版本比较高,在老版本中(8i),可能只能采用第三种方法。总的来说,12两种方法比3要少6个逻辑读左右。如:SQL> SELECT /*+ ordered use_nl(a,u) */ id, user_id
       from table(STR2NUMLIST(’1,2,3′)) a,
       bmw_users u
      4*  where u.user_id = a.column_value

    Execution Plan
    ———————————————————-
           SELECT STATEMENT Optimizer=CHOOSE (Cost=3279 Card=8168 Bytes =334888)
          NESTED LOOPS (Cost=3279 Card=8168 Bytes=334888)
            COLLECTION ITERATOR (PICKLER FETCH) OF ‘STR2NUMLIST’
            TABLE ACCESS (BY INDEX ROWID) OF ‘BMW_USERS’ (Cost=1 Card=1 Bytes=39)
              INDEX (UNIQUE SCAN) OF ‘UK_BMW_USERS_USERID’ (UNIQUE)

    Statistics
    ———————————————————-
              recursive calls
              db block gets
             10  consistent gets
              physical reads
              redo size
    ……
                                    
    SQL> SELECT  /*+ index(bmw_users UK_BMW_USERS_USERID) */ id, user_id
       from bmw_users where user_id in
    3*  (SELECT * FROM THE (SELECT CAST(STR2NUMLIST(’1,2,3′) AS NUMTABLETYPE) FROM dual) WHERE rownum<1000)

    Execution Plan
    ———————————————————-
           SELECT STATEMENT Optimizer=CHOOSE (Cost=430 Card=999 Bytes=51948)
          NESTED LOOPS (Cost=430 Card=999 Bytes=51948)
            VIEW OF ‘VW_NSO_1′ (Cost=11 Card=999 Bytes=12987)
              SORT (UNIQUE)
                COUNT (STOPKEY)
                  COLLECTION ITERATOR (PICKLER FETCH) OF ‘STR2NUMLIST’
                    TABLE ACCESS (FULL) OF ‘DUAL’ (Cost=2 Card=82)
            TABLE ACCESS (BY INDEX ROWID) OF ‘BMW_USERS’ (Cost=1 Card=1 Bytes=39)
              INDEX (UNIQUE SCAN) OF ‘UK_BMW_USERS_USERID’ (UNIQUE)

    Statistics
    ———————————————————-
              recursive calls
              db block gets
             16  consistent gets
              physical reads
              redo size

    展开全文
  • oracle PL/SQL 中变量绑定用法

    千次阅读 2006-12-05 19:11:00
    Oracle的共享池的设计、和Oracle推荐的 PL/SQL 写法,可以看出,变量绑定对性能有比较大的影响,那么,如何在PL/SQL 使用变量绑定呢?首先看看不使用变量绑定的用法:declare cursor cur_temp(id number) ...

    从Oracle的共享池的设计、和Oracle推荐的 PL/SQL 写法中,可以看出,变量绑定对性能有比较大的影响,那么,如何在PL/SQL 中使用变量绑定呢?

    首先看看不使用变量绑定的用法:

    declare

        cursor cur_temp(id number) is
                select * from table_a where a=id;

         c_temp cur_temp%rowtype;

    beign

      open cur_temp(1);

      loop

        fetch cur_temp into c_temp;

       exit when cur_temp%notfound;

      insert into b values (c_temp.a);

        end loop;

    close cur_temp;

    commit;

    end;

    上面是没有使用变量绑定的用法,包括游标和操作语句。

    再看下面使用变量绑定的用法:

    先要

    type cursorType is ref cursor;

    然后:

    declare

        cur_temp cursortype;

       c_temp table_a%rowtype;

    begin

      open cur_temp for 'select * from table_a where a=:1' using 91;

       loop

           fetch cur_temp into c_temp;

           exit when cur_temp%notfound;

          execute immediate 'insert into b values (:1)' using c_temp.a;

      end loop;

    close cur_temp;

    commit;

    end;

    上面是在PL/SQL 块中的写法,这个写法也同样适用于存储过程,触发器,函数,包等可以用PL/SQL 的地方。

    对于需要用 INTO 的地方,可以如下使用:

      i number(6);

    execute immediate 'select count(*) from table_a where a =:1' into i using 89;

    执行后,取出的值存放在了变量 i 中。

    在PL/SQL 中,变量绑定的常见用法基本如上所示,建议全部使用变量绑定。

    展开全文
  • oracle绑定变量

    2014-08-10 16:25:48
    一.绑定变量 bind variable: A variable in a SQL statement that must be replaced with a valid value, or the address of a value, in order for the ... 变量绑定是OLTP系统一个非常值得关注的技术。良好的
  • Oracle 绑定变量详解

    千次阅读 2018-06-19 11:50:03
    之前整理过一篇有关绑定变量的文章,... Oracle 绑定变量 http://www.cndba.cn/Dave/article/1275 一.绑定变量 bind variable: A variable in a SQL statement that must be replaced with a valid value,...
  • 我们利用lambda表达式定义了一个匿名函数,希望在函数定义的时候完成对变量名的绑定 2.解决方案 考虑以下的代码: In [1]: x = 10 In [2]: a = lambda y : x + y In [3]: x = 20 In [4]: b = lambda y : x + y In ...
  • Oracle 绑定变量窥探

    万次阅读 2011-11-01 09:19:44
    Bind Peeking是Oracle 9i引入的新特性,一直持续到Oracle 10g R2。它的作用就是在SQL语句硬分析的...一、绑定变量窥探  使用SQL首次运行时的值来生成执行计划。后续再次运行该SQL语句则使用首次执行计划来执行。
  • ORACLE中对in操作使用变量绑定的方法

    千次阅读 2008-05-09 10:34:00
    ORACLE中对in操作使用变量绑定的方法在ORACLE数据库编程使用变量绑定,可以重用共享池的查询,最小化硬解析的需求,提高数据库性能。 我们知道in操作符接受两种list, 一个是由一个个item组成的list, 另一个是由...
  • Oracle 绑定变量 详解

    2011-09-29 09:26:45
    之前整理过一篇有关绑定变量的文章,不太... Oracle 绑定变量  http://blog.csdn.net/xujinyang/article/details/6830430   一.绑定变量  bind variable: A variable in a SQL statement that must be repl
  • 下面是ASKTOM网站提供的关于oracle数据库未使用绑定变量情况的查看方式 1、创建一张表用于存放整理过的数据 create table test as select * from v$sqlarea; 2、...
  • 这篇是7788凑的:":="是赋值语句 如: l_name :='sky';...变量绑定 是指在sql语句的条件使用变量而不是常量。比如shared pool里有两条sql语句,select * from tab1 where col1=1;select * ...
  • 为什么要使用绑定变量:在JAVA的SQL 语句的编写方面,没有使用ORACLE 绑定变量,很大程度上降低了数据库的性能,表现在两个方面:1、SQL语句硬分析(Hard Parse)太多,严重消耗CPU资源,延长了SQL语句总的执行时间...
  • Oracle数据库绑定变量特性及应用

    千次阅读 2010-11-10 15:23:00
    http://doc.chinaunix.net/oracle/200712/157876.shtml
  • oracle 绑定变量 Oracle数据库有它的方式。 在会议上SQL演讲 ,我喜欢将人们与以下Oracle事实混淆: ……答案当然是: 使空字符串与NULL相同不是很可怕吗? 拜托,Oracle... 遵循前两个的唯一实际合理...
  • Oracle 绑定变量 详解 .

    2011-08-04 15:37:45
    之前整理过一篇有关绑定变量的文章,不太详细,重新补充一下。  Oracle 绑定变量   http://blog.csdn.net/tianlesoftware/archive/2009/10/17/4678335.aspx     一
  • Oracle 绑定变量详解 .

    2011-08-03 11:19:46
    此文转自http://blog.csdn.net/chenjie19891104/article/details/6637070 之前整理过一篇有关绑定变量的文章,不太详细,重新补充一下。  Oracle 绑定变量   http://blo
  • oracle绑定变量使用方法总结

    万次阅读 2016-11-11 17:36:17
    Oracle中,对于一个提交的sql语句,存在两种可选的解析过程,硬解析和软解析。 一个硬解析需要经解析,...绑定变量的实质就是使用变量来代替sql语句的常量。绑定变量能够使得每次提交的sql语句都完全一样。 1.
  • 之前对ORACLE中变量一直没个太清楚的认识,比如说使用:、&、&&、DEIFINE、VARIABLE……等等。今天正好闲下来,上网搜了搜相关的文章,汇总了一下,贴在这里,方便学习。   ==================================...
  • occi是oracle提供的一套对oci封装好的类库,前段时间我抽空看了看相关的文档,本来是想好好的研究研究的,不过因为一直有别的事情要做,所以就没有时间看下去了。论坛上用PRO*C的人多一些。用OCI的好像很少,而且...
  • Oracle 数据库的绑定变量特性及应用 关键词: 绑定变量(binding variable),共享池(shared buffer pool),SGA(system global area); 在开发一个数据库系统前,有谁对Oracle 系统了解很多,尤其是它的特性,好象很少吧;...
  • Oracle 数据库的绑定变量特性及应用 关键词: 绑定变量(binding variable),共享池(shared buffer pool), SGA(system global area); 在开发一个数据库系统前,有谁对Oracle 系统了解很多,尤其是它的特性,好象很少...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 26,829
精华内容 10,731
关键字:

oracle函数中变量绑定