绑定变量优缺点、使用、绑定变量窥探
绑定变量是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;
- 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
- 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
- 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
- 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
-
-
- 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);
-
- 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
-
- NAME VALUE
-
- parse count (total) 394
- parse count (hard) 63
- parse count (failures) 1
-
-
- /**************************************************/
- /* Author: Robinson Cheng */
- /* Blog: http://blog.csdn.net/robinson_0612 */
- /* MSN: robinson_0612@hotmail.com */
- /* QQ: 645746311 */
- /**************************************************/
-
- SQL> begin
- 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
-
- 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)
- 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#
-
- 1084 938
-
- SQL> exec dbms_monitor.session_trace_enable(session_id=>1084,serial_num=>938);
-
- 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);
-
- 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
-
-
- 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
- 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> @get_parse
-
- 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;
- 4 end loop;
- 5 commit;
- 6 end;
- 7 /
-
- PL/SQL procedure successfully completed.
-
- SQL> @get_parse
-
- NAME VALUE
-
- parse count (total) 537
- parse count (hard) 122
- parse count (failures) 1
-
- SQL> set serveroutput on;
- SQL> get get_sal.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只会匹配已经在共享池中相同的对象名。
2、绑定变量窥探
Bind Peeking是Oracle 9i中引入的新特性，一直持续到Oracle 10g R2。它的作用就是在SQL语句硬分析的时候，查看一下当前SQL谓词的值，以便生成最佳的执行计划。而在oracle 9i之前的版本中，Oracle 只根据统计信息来做出执行计划。
使用SQL首次运行时的值来生成执行计划。后续再次运行该SQL语句则使用首次执行计划来执行。
影响的版本：Oracle 9i, Oracle 10g
对于绑定变量列中的特殊值或非均匀分布列上的绑定变量会造成非高效的执行计划被选择并执行。
要注意的是，Bind Peeking只发生在硬分析的时候，即SQL被第一次执行的时候，之后的变量将不会在做peeking。我们可以看出，Bind peeking并不能最终解决不同谓词导致选择不同执行计划的问题，它只能让SQL第一次执行的时候，执行计划选择更加准确，并不能帮助OLAP系统解决绑定变量导致执行计划选择错误的问题。这也是OLAP不应该使用绑定变量的一个原因。
更确切地说，绑定变量窥探是在SQL解析的物理阶段，查询优化器将会窥探绑定变量的值并将其作为字面量来使用。即ORACLE首次解析SQL时会将变量的真实值代入产生执行计划，后续对所有使用该绑定变量SQL语句都采用首次生存的执行计划。如此这般？那性能究竟如何？
结果是并非最佳的执行计划的使用。此问题在Oracle 11g中得以解决。
