绑定变量及其优缺点

3121321321 · 发表于 2014-7-9 09:48:17

绑定变量是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.iyunv.com/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只会匹配已经在共享池中相同的对象名。

账号		自动登录	找回密码
密码			立即注册

Centos6.5×64安装配置openmeetings3.0.3详

大疆运维招人啦，

C++ :try 语句块和异常处理

C++的多态

Red Hat RHCE 8 (EX294) Cert Guide

Java/C++ 区别：看完这一篇，就够用！

别再用过时库了！这 13 个顶级 C++ 库才是

[经验分享] 绑定变量及其优缺点

相关帖子

扫码加入运维网微信交流群