绑定变量及其优缺点

曾经终败给现在 2022-05-15 11:27 255阅读 0赞

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

**一、绑定变量**

提到绑定变量，就不得不了解硬解析与软解析。硬解析简言之即一条SQL语句没有被运行过，处于首次运行，则需要对其进行语法分析，语  
义识别，跟据统计信息生成最佳的执行计划，然后对其执行。而软解析呢，则是由于在library cache已经存在与该SQL语句一致的SQL语句文本  
、运行环境，即有相同的父游标与子游标，采用拿来主义，直接执行即可。软解析同样经历语法分析，语义识别，且生成hash value ，接下来  
在library cache搜索相同的hash value ，如存在在实施软解析。有关更多的硬解析与软解析以及父游标，子游标请作如下参考：  
  
有关硬解析与软解析，请参考：[Oracle 硬解析与软解析  
][Oracle _] 有关父游标、子游标，请参考：[父游标、子游标与共享游标  
][Link 1]  
绑定变量  
首先其实质是变量，有些类似于我们经常使用的替代变量，替代变量使用&占位符，只不过绑定变量使用:  
替代变量使用时为 &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 硬解析与软解析][Oracle _]  
[父游标、子游标与共享游标][Link 1]  
[ 启用用户进程跟踪  
][Link 2] [ PL/SQL --> 动态SQL  
][PL_SQL --_ _SQL] [ PL/SQL --> 动态SQL的常见错误][PL_SQL --_ _SQL 1]

[Oracle _]: http://blog.csdn.net/robinson_0612/archive/2011/02/19/6195483.aspx
[Link 1]: http://blog.csdn.net/robinson_0612/article/details/6893477
[Link 2]: http://blog.csdn.net/robinson_0612/archive/2010/10/07/5925564.aspx
[PL_SQL --_ _SQL]: http://blog.csdn.net/robinson_0612/archive/2011/01/05/6118010.aspx
[PL_SQL --_ _SQL 1]: http://blog.csdn.net/robinson_0612/archive/2011/01/05/6118049.aspx