使用绑定变量的一点总结！

warehouse · 发表于 2008-4-4 00:33

欢迎补充、指正！
在oltp系统中提倡使用绑定变量，使用绑定变量可以减少hard parse，避免因解析sql而过渡消耗cpu时间以及引起latch争用等一系列问题。那么到底如何使用绑定变量？可能是困扰很多人的一个问题，下面列举了一些使用绑定变量的例子
1.sqlplus中如何使用绑定变量，可以通过variable来定义
SQL> select * from tt where id=1;

ID NAME
---------- ----------------------------------------
1 test

SQL> select * from tt where id=2;

ID NAME
---------- ----------------------------------------
2 test

SQL> variable i number;
SQL> exec :i :=1;

PL/SQL 过程已成功完成。

SQL> select *from tt where id=:i;

ID NAME
---------- ----------------------------------------
1 test

SQL> exec :i :=2;

PL/SQL 过程已成功完成。

SQL> select *from tt where id=:i;

ID NAME
---------- ----------------------------------------
2 test

SQL> print i;

I
----------
2

SQL> select sql_text,parse_calls from v$sql where sql_text like 'select * from t
t where id=%';

SQL_TEXT PARSE_CALLS
------------------------------------------------------------ -----------
select * from tt where id=2 1
select * from tt where id=1 1
select * from tt where id=:i 2
SQL>
从上面试验发现绑定变量i的使用使查询id=1和id=2的sqlselect *from tt where id=:i得以重复
使用，从而避免了hard parse，这里的PARSE_CALLS＝2包括了一次soft parse

[ 本帖最后由 warehouse 于 2008-4-4 00:38 编辑 ]

warehouse · 发表于 2008-4-4 00:33

2.前两天看到有人在pub上问在sqlplus中通过define和variable定义的变量的区别。其实define定义的我

理解不是变量而是字符常量，通过define定义之后，在通过&或者&&引用的时候不需要输入了，仅此而已。

oracle在执行的时候自动用值进行了替换；而variable定义的是绑定变量,上面已经提到。
C:>sqlplus xys/manager
SQL*Plus: Release 11.1.0.6.0 - Production on 星期二 4月 1 14:03:00 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> define
DEFINE _DATE = "01-4月 -08" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)
DEFINE _USER = "XYS" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.
6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (
CHAR)
DEFINE _O_RELEASE = "1101000600" (CHAR)
SQL> select *from tt;
ID NAME
---------- ----------
1 a
2 a
3 "abc"
SQL> define a
SP2-0135: 符号 a 未定义
SQL> define a=1
SQL> define
DEFINE _DATE = "01-4月 -08" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)
DEFINE _USER = "XYS" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.
6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (
CHAR)
DEFINE _O_RELEASE = "1101000600" (CHAR)
DEFINE A = "1" (CHAR)
--通过上面显示define定义的应该是字符（串）常量。
SQL> select * from tt where id=&a;
原值 1: select * from tt where id=&a
新值 1: select * from tt where id=1
ID NAME
---------- ----------
1 a
SQL> select * from tt where id=&&a;
原值 1: select * from tt where id=&&a
新值 1: select * from tt where id=1
ID NAME
---------- ----------
1 a
SQL> define b='a';
SQL> define
DEFINE _DATE = "01-4月 -08" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "db11" (CHAR)
DEFINE _USER = "XYS" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1101000600" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.1.0.
6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (
CHAR)
DEFINE _O_RELEASE = "1101000600" (CHAR)
DEFINE A = "1" (CHAR)
DEFINE B = "a" (CHAR)

--如果是字符类型那么在引用时别忘了加上单引号，另外通过define定义之后在引用时不需要输入了。
SQL> select * from tt where name=&&b;
原值 1: select * from tt where name=&&b
新值 1: select * from tt where name=a
select * from tt where name=a
*
第 1 行出现错误:
ORA-00904: "A": 标识符无效

SQL> select * from tt where name='&&b';
原值 1: select * from tt where name='&&b'
新值 1: select * from tt where name='a'
ID NAME
---------- ----------
1 a
2 a
SQL> select * from tt where name='&b';
原值 1: select * from tt where name='&b'
新值 1: select * from tt where name='a'
ID NAME
---------- ----------
1 a
2 a
--执行sql时进行了替换
SQL> select sql_text from v$sql where sql_text like 'select * from tt where name
=%';
SQL_TEXT
--------------------------------------------------------------------------------
select * from tt where name=1
select * from tt where name='a'
SQL>

[ 本帖最后由 warehouse 于 2008-4-4 18:51 编辑 ]

warehouse · 发表于 2008-4-4 00:34

3.oracle在解析sql时会把plsql中定义的变量转为为绑定变量
SQL> create table tt(id int , name varchar2(10));

表已创建。

SQL> alter session set sql_trace=true;

会话已更改。

SQL> declare
2 begin
3 for i in 1..100 loop
4 insert into tt values(i,'test');
5 end loop;
6 commit;
7 end;
8 /

PL/SQL 过程已成功完成。

SQL> alter session set sql_trace=false;
--trace file:
=====================
PARSING IN CURSOR #3 len=90 dep=0 uid=31 oct=47 lid=31 tim=7109565004 hv=962259239

ad='668ec528'
declare
begin
for i in 1..100 loop
insert into tt values(i,'test');
end loop;
commit;
end;
END OF STMT
PARSE #3:c=15625,e=5678,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=1,tim=7109564996
=====================
PARSING IN CURSOR #5 len=34 dep=1 uid=31 oct=2 lid=31 tim=7109565520 hv=1299226876

ad='66869934'
INSERT INTO TT VALUES(:B1 ,'test')
END OF STMT
PARSE #5:c=0,e=226,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=7109565513
=====================
另外从hard parse的数据量上其实也可以大致猜测oracle会把plsql中定义的变量转换为绑定变量处理
SQL> connect /as sysdba
已连接。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 67110244 bytes
Database Buffers 96468992 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> connect xys/manager
已连接。
SQL> drop table tt;

表已删除。

SQL> create table tt(id int , name varchar2(10));

表已创建。
SQL> col name format a30
SQL> select a.*,b.name
2 from v$sesstat a , v$statname b
3 where a.statistic#=b.statistic#
4 and a.sid=(select distinct sid from v$mystat)
5 and b.name like '%parse%';

SID STATISTIC# VALUE NAME
---------- ---------- ---------- ------------------------------
159 328 39 parse time cpu
159 329 74 parse time elapsed
159 330 339 parse count (total)
159 331 165 parse count (hard)
159 332 0 parse count (failures)

SQL> declare
2 begin
3 for i in 1..100 loop
4 insert into tt values(i,'test');
5 end loop;
6 commit;
7 end;
8 /

PL/SQL 过程已成功完成。

SQL> select a.*,b.name
2 from v$sesstat a , v$statname b
3 where a.statistic#=b.statistic#
4 and a.sid=(select distinct sid from v$mystat)
5 and b.name like '%parse%'
6 /

SID STATISTIC# VALUE NAME
---------- ---------- ---------- ------------------------------
159 328 39 parse time cpu
159 329 74 parse time elapsed
159 330 345 parse count (total)
159 331 167 parse count (hard)
159 332 0 parse count (failures)

SQL>
这里发现hard parse只增加了2，如果没有使用绑定变量的话，相信hard parse会更多

[ 本帖最后由 warehouse 于 2008-4-4 00:36 编辑 ]

warehouse · 发表于 2008-4-4 00:34

4.过程中的参数会自动转化为绑定变量
SQL> edit
已写入 file afiedt.buf

1 create or replace procedure proc_test(p_id int, p_name varchar2)
2 is
3 begin
4 insert into tt values(p_id , p_name);
5 commit;
6* end;
SQL> /

过程已创建。

SQL> alter session set sql_trace=true;

会话已更改。

SQL> exec proc_test(200,'test');

PL/SQL 过程已成功完成。

SQL> alter session set sql_trace=false;

会话已更改。
--trace file：
alter session set sql_trace=true
END OF STMT
EXEC #3:c=0,e=749,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=7393908487
=====================
PARSING IN CURSOR #1 len=35 dep=0 uid=31 oct=47 lid=31 tim=7403000735 hv=526484776

ad='6687b0b8'
BEGIN proc_test(200,'test'); END;
END OF STMT
PARSE #1:c=0,e=2584,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=7403000727
=====================
PARSING IN CURSOR #6 len=33 dep=1 uid=31 oct=2 lid=31 tim=7403001293 hv=2874748229

ad='668e9cd8'
INSERT INTO TT VALUES(:B2 , :B1 )
END OF STMT
PARSE #6:c=0,e=246,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=7403001286
=====================
另外也可以直观的观察：
SQL> exec proc_test(200,'test');

PL/SQL 过程已成功完成。

SQL> select sql_text from v$sql where sql_text like '%proc_test%';

SQL_TEXT
--------------------------------------------------------------------------------
BEGIN proc_test(200,'test'); END;

SQL>
在sqlplus里执行过程不能观察出来
下面在plsql developer执行一次过程之后再来看执行的情况
SQL> select sql_text from v$sql where sql_text like '%proc_test%';

SQL_TEXT
--------------------------------------------------------------------------------
begin -- Call the procedure proc_test(p_id =>

_id, p_name => :

p_name); end;

SQL>
很显然oracle在执行过程时把参数转化为绑定变量了，其实从plsql developer中执行过程时的语法就能

看出来：
begin
-- Call the procedure
proc_test(p_id =>

_id,
p_name =>

_name);
end;
在输入参数列表框上面的执行语法就是这样的。

[ 本帖最后由 warehouse 于 2008-4-4 00:37 编辑 ]

warehouse · 发表于 2008-4-4 00:40

本帖最后由 warehouse 于 2011-12-9 19:24 编辑

5.在动态sql中使用绑定变量，动态sql中使用绑定变量非常明显也容易理解，下面给出2个简单的例子
SQL> set serveroutput on
SQL> declare
  2  v_string varchar2(100);
  3  v_id tt.id%type ;
  4  v_name tt.name%type ;
  5  begin
  6  v_string:='select * from tt where id=:v_id';
  7  execute immediate v_string into v_id , v_name using &a;
  8  dbms_output.put_line(v_id||' '||v_name) ;
  9  end;
10  /
输入 a 的值:  1
原值 7: execute immediate v_string into v_id , v_name using &a;
新值 7: execute immediate v_string into v_id , v_name using 1;
1 test

PL/SQL 过程已成功完成。

SQL> declare
  2  v_string varchar2(100);
  3  v_id tt.id%type;
  4  v_name tt.name%type ;
  5  begin
  6  v_string:='insert into tt values(:id,:name)';
  7  execute immediate v_string using &id,&name ;
  8  end;
  9  /
输入 id 的值:  1000
输入 name 的值:  'test'
原值 7: execute immediate v_string using &id,&name ;
新值 7: execute immediate v_string using 1000,'test' ;

PL/SQL 过程已成功完成。

SQL> select * from tt where id=1000;

      ID NAME
---------- ----------
   1000 test

SQL>

6.java,.net等开发语言中如何使用绑定变量有熟悉的弟兄可以补充！
7.当然使用绑定变量也有缺点，那就是当列上数据分布不均衡时容易错过更高效的执行计划：
SQL> desc tt
名称                                     是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER                                           VARCHAR2(30)
OBJECT_NAME                                     VARCHAR2(128)
SUBOBJECT_NAME                                  VARCHAR2(30)
OBJECT_ID                                        NUMBER
DATA_OBJECT_ID                                  NUMBER
OBJECT_TYPE                                     VARCHAR2(19)
CREATED                                           DATE
LAST_DDL_TIME                                     DATE
TIMESTAMP                                        VARCHAR2(19)
STATUS                                           VARCHAR2(7)
TEMPORARY                                        VARCHAR2(1)
GENERATED                                        VARCHAR2(1)
SECONDARY                                        VARCHAR2(1)
SQL> select count(*) from tt;
  COUNT(*)
----------
   46080
SQL> update tt set object_id=999 where object_id>5;
已更新46064行。
SQL> commit;
提交完成。
SQL> create index idx_tt on tt(object_id) tablespace users;
索引已创建。
SQL> set autotrace traceonly
SQL> variable i number
SQL> exec :i := 2;
PL/SQL 过程已成功完成。
SQL> set linesize 200
SQL> select * from tt where object_id=:i;

执行计划
----------------------------------------------------------
Plan hash value: 6977672
--------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |       | 548 | 96996 | 22 (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID| TT    | 548 | 96996 | 22 (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN       | IDX_TT | 219 |    | 19 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:I))
Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      7  consistent gets
      0  physical reads
      0  redo size
   1472  bytes sent via SQL*Net to client
      385  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      4  rows processed
SQL> exec :i := 999;
PL/SQL 过程已成功完成。
SQL> select * from tt where object_id=:i;
已选择46064行。

执行计划
----------------------------------------------------------
Plan hash value: 6977672
--------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |       | 548 | 96996 | 22 (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID| TT    | 548 | 96996 | 22 (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN       | IDX_TT | 219 |    | 19 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:I))
Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   6774  consistent gets
      98  physical reads
      0  redo size
4572424  bytes sent via SQL*Net to client
   34155  bytes received via SQL*Net from client
   3072  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   46064  rows processed
--很显然上面查询使用绑定变量时object_id=999使用了索引，不是理想的执行计划，下面没有使用绑定变量产生了理解的执行计划
SQL> select * from tt where object_id=2;

执行计划
----------------------------------------------------------
Plan hash value: 6977672
--------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |       |    4 | 708 |    2 (0)| 00:00:01 |
| 1 |  TABLE ACCESS BY INDEX ROWID| TT    |    4 | 708 |    2 (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN       | IDX_TT |    4 |    |    1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2)
Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
      9  recursive calls
      0  db block gets
      89  consistent gets
      0  physical reads
      0  redo size
   1472  bytes sent via SQL*Net to client
      385  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      4  rows processed
SQL> select * from tt where object_id=999;
已选择46064行。

执行计划
----------------------------------------------------------
Plan hash value: 264906180
--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 54823 |  9476K| 253 (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| TT | 54823 |  9476K| 253 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=999)
Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
      9  recursive calls
      0  db block gets
   4248  consistent gets
      0  physical reads
      0  redo size
1920772  bytes sent via SQL*Net to client
   34155  bytes received via SQL*Net from client
   3072  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   46064  rows processed
SQL>

[ 本帖最后由 warehouse 于 2008-4-4 16:31 编辑 ]

五“宅”一生 · 发表于 2008-4-4 01:30

好文章。收藏！

cc59 · 发表于 2008-4-4 02:26

java:PreparedStatement
.net: 也有类似的吧.

zuohao_lu · 发表于 2008-4-4 09:13

支持.
收藏.

SingleLove · 发表于 2008-4-4 11:11

不错，拜读中！

paulyibinyi · 发表于 2008-4-4 11:54

不错很全面