绑定变量
绑定变量会从两个方面来影响应用程序.第一,从开发角度来说,使用绑定变量会使用程序变得简单或复杂
(更准确的来说是使用绑定变量会减少或增加代码量).在这种情况下,使用绑定变量的影响依赖于执行sql
语句的应用程序接口来定的,例如,如果你的程序是使用plsql编码那么使用绑定变量会变得容易,但是
如果你的应用程序是使用java的jdbc那么不使用绑定变量编码会容易些.第二从性能角度来看,使用绑定
变量有优点也有缺点.
使用绑定变量的优点
绑定变量的优点是允许在库缓存中共享游标这样可以避免硬解析以及硬解析所花的开锁.下面的例子说明了
使用绑定变量来共享游标的情况
SQL> create table t (n number(12),v varchar2(4000)); Table created SQL> variable n number SQL> variable v varchar2(32) SQL> execute :n:=1;:v:='Helicon'; PL/SQL procedure successfully completed n --------- 1 v --------- Helicon SQL> insert into t(n,v) values(:n,:v); 1 row inserted n --------- 1 v --------- Helicon SQL> execute :n:=2;:v:='Trantor'; PL/SQL procedure successfully completed n --------- 2 v --------- Trantor SQL> insert into t(n,v) values(:n,:v); 1 row inserted n --------- 2 v --------- Trantor SQL> execute :n:=3;:v:='Kalgan'; PL/SQL procedure successfully completed n --------- 3 v --------- Kalgan SQL> insert into t(n,v) values(:n,:v); 1 row inserted n --------- 3 v --------- Kalgan SQL> commit; Commit complete SQL> select sql_id,sql_text,child_number,executions 2 from v$sql where sql_text like'insert into t(n,v) values(:n,:v)%'; SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS ------------- ----------------------------------- ------------ ---------- dunwv1qwg6nrv insert into t(n,v) values(:n,:v) 0 3
然而有些情况下尽管使用了绑定变量还是会生成一些子游标.下面的例子同样使用
insert into t(n,v) values(:n,:v)语句只是将变量v的长度改为2000
SQL> variable v varchar2(2000) SQL> execut :n:=4;:v:='Terminus'; PL/SQL 过程已成功完成。 SQL> insert into t(n,v) values(:n,:v); 已创建 1 行。 SQL> commit; 提交完成。 SQL> variable v varchar2(129) SQL> execut :n:=5;:v:='JY'; PL/SQL 过程已成功完成。 SQL> insert into t(n,v) values(:n,:v); 已创建 1 行。 SQL> commit; 提交完成。 SQL> select sql_id,sql_text,child_number,executions 2 from v$sql where sql_text like'insert into t(n,v) values(:n,:v)%'; SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS ------------- ---------------------------------------------- ------------ ---------- dunwv1qwg6nrv insert into t(n,v) values(:n,:v) 0 3 dunwv1qwg6nrv insert into t(n,v) values(:n,:v) 1 2
一个新的子游标生成了(1)因为在前面三个insert语句与第四个与第五个insert语句的执行环境发生了改变.错误的匹配
可以通过查询v$sql_shared_cursor来查看原因,原因就是因为绑定变量
SQL> SELECT child_number, bind_mismatch 2 FROM v$sql_shared_cursor 3 WHERE sql_id = 'dunwv1qwg6nrv'; CHILD_NUMBER BIND_MISMATCH ------------ ------------- 0 N 1 N
当数据库引擎应用绑定变量等级时会发什么了.使用绑定变量等级这个功能的目的是为了最小化子游标的个数.
根据绑定变量的大小分成四组.绑定变量长度小于等于32字节的为第一组.绑定变量长度在33和128字节之间的
为第二组. 绑定变量的长度在129和2000字节之间的为第三组,绑定变量长度大于2000字节的为第四组.
绑定变量的数字类型的变量的长度最大长度为22字节.下面的查询v$sql_bind_metadata视图显示了绑定变量
最大长度的一组.注意尽管使用的绑定变量的长度是129但是用到的长度还是2000
SQL> SELECT s.child_number, m.position, m.max_length,
2 decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype) AS datatype
3 FROM v$sql s, v$sql_bind_metadata m
4 WHERE s.sql_id = 'dunwv1qwg6nrv'
5 AND s.child_address = m.address
6 ORDER BY 1, 2;
CHILD_NUMBER POSITION MAX_LENGTH DATATYPE
------------ ---------- ---------- ----------------------------------------
0 1 22 NUMBER
0 2 32 VARCHAR2
1 1 22 NUMBER
1 2 2000 VARCHAR2
如果说每一次都有一个新的子游标被创建,那么一个执行计划也会生成.一个新的执行计划是否等于
另一个子游标所使用的执行计划还是取决于绑定变量的值.
缺点
在where子句中使用绑定变量的缺点是查询优化器的关键的信息被隐藏了.事实上,对于查询优化器来说,
使用文本字面值会比绑定变量更好,使用文本值,它能够改善其评估.当优化器检查条件值是否超出可用值的
范围(小于最小或大于最大值)和当优化器使用直方图时尤其如此.下面举例说明这种情况.创建一个表t,它有
1000行数据,在id列上,它的值从1到1000
SQL> create table t(id number(12),name varchar2(12));
表已创建。
SQL> create index idx_t on t(id);
索引已创建。
SQL> begin
2 for i in 1 .. 1000 loop
3 insert into t values(i,''||i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> SELECT count(id), count(DISTINCT id), min(id), max(id) FROM t;
COUNT(ID) COUNT(DISTINCTID) MIN(ID) MAX(ID)
---------- ----------------- ---------- ----------
1000 1000 1 1000
当一个用户要从表t中查询id小于等于990的所有记录时,查询优化器(由于对象统计信息)知道这个表的99%
的记录被选择了,因此查询优化器会使用全表扫描.同时也要注意查询所返回的行数据所对应的评估基数(
执行计划中的行数)
SQL> select count(name) from t where id< =990;
COUNT(NAME)
-----------
990
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
|* 2 | TABLE ACCESS FULL| T | 990 | 20790 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<=990)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
7 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
340 bytes sent via SQL*Net to client
376 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
当另一个用户从表t中查询id小于10的所有记录时,查询优化器会知道只有1%的数据被选择.因此优化器会使用
索引扫描.
SQL> select count(name) from t where id<10;
COUNT(NAME)
-----------
9
执行计划
----------------------------------------------------------
Plan hash value: 1883549804
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 189 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T | 9 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"<10)
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
339 bytes sent via SQL*Net to client
376 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
无论何时处理绑定变量,查询优化器会忽略绑定变量的值.因此想要很好的评估谓词的基数变得不可能.
为了避免这个问题,从oracle9i开始引入了绑定变量窥视功能
注意:绑定变量窥视功能在oracle9i中不支持jdbc驱动
绑定变量窥视功能很简单.当在进行物理优化阶段时,查询优化器将会窥视绑定变量的值并使用它们当作文本值.
使用这种做法的问题是生成的执行计划依赖于第一次执行所提供的值.下面将举例说明这个问题,第一次执行时
使用id< =990作为查询条件.然后查询优化器会选择执行全表扫描.正是这种选择,自从这个游标被共享后(sql_id
和子游标相同),那么它将会影响使用id<10作为查询条件的第二个查询.
SQL> variable id number
SQL> execute :id:=990;
PL/SQL 过程已成功完成。
SQL> set autotrace on;
SQL> select count(name) from t where id< =:id;
COUNT(NAME)
-----------
990
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
|* 2 | TABLE ACCESS FULL| T | 50 | 1050 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<=TO_NUMBER(:ID))
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
278 recursive calls
0 db block gets
52 consistent gets
9 physical reads
0 redo size
340 bytes sent via SQL*Net to client
376 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> variable id number
SQL> execute :id:=10;
PL/SQL 过程已成功完成。
SQL> set autotrace on
SQL> select count(name) from t where id< =:id;
COUNT(NAME)
-----------
10
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
|* 2 | TABLE ACCESS FULL| T | 50 | 1050 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"<=TO_NUMBER(:ID))
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
339 bytes sent via SQL*Net to client
376 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
当然,如果第一次执行条件id< =10的查询,查询优化器会选择索引扫描,注意为了避免与之前的查询的游标 共享,可以重启数据库或清空共享池,我是重启数据库
SQL> variable id number;
SQL> execute :id:=10;
PL/SQL 过程已成功完成。
SQL> set autotrace on;
SQL> select count(name) from t where id=:id;
COUNT(NAME)
———–
1
执行计划
———————————————————-
Plan hash value: 1883549804
——————————————————————————–
——
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 21 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 10 | 210 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T | 4 | | 1 (0)| 00:00:01 |
————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
3 – access(“ID”=TO_NUMBER(:ID))
Note
—–
– dynamic sampling used for this statement (level=2)
统计信息
———————————————————-
278 recursive calls
0 db block gets
49 consistent gets
7 physical reads
0 redo size
339 bytes sent via SQL*Net to client
376 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> execute :id:=990;
PL/SQL 过程已成功完成。
SQL> set autotrace on;
SQL> select count(name) from t where id< =:id;
COUNT(NAME)
-----------
990
执行计划
----------------------------------------------------------
Plan hash value: 1883549804
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 10 | 210 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T | 4 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=TO_NUMBER(:ID))
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
278 recursive calls
0 db block gets
49 consistent gets
7 physical reads
0 redo size
339 bytes sent via SQL*Net to client
376 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
必须要理解的是游标保存在库缓存中共享多久它就能被重用多久,这种情况是不会顾及执行计划的效率的
为了解决这个问题,在oracle11g中有一个新功能叫做扩展游标共享(也叫自适应游标共享).它的目的就是
识别哪些重复使用一个已经存的可用的游标而导致效率低下的执行.为了理解这个功能是怎样工作的.
我们先来查看一个v$sql视图在oracle11g中的变化:
is_bind_sensitive:如果不仅仅绑定变量窥视会被用来生成执行计划而且执行计划还依赖于窥视的值
那么这个列的值就为Y;否则为N
is_bind_aware:这个列指示游标是否正在使用扩展游标共享,如果是,这个列的值为Y;如果不是这个列的值为N.
如果这个列的值为N.那么这个游标就被标记为过期的它将不再被使用.
is_shareable:这个列指示游标是否能被共享.如果能被共享这个列的值就为Y.否则为N.如果为N,
这个游标就被标记为过期的它将不再被使用.
在前面的例子中,游标能共享但是对绑定变量不敏感也不能不扩展游标
SQL> SELECT child_number, is_bind_sensitive, is_bind_aware, is_shareable
2 FROM v$sql WHERE sql_id = ‘7y1vyk0wnb2ut’ ORDER BY child_number;
CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE
———— —————– ————- ————
0 N N Y
当使用不同的变量值让游标执行多次时会出现意想不到的情况.在使用id=10和id=990执行多次,对v$sql的查询
会有不同.注意,v$sql视图中child_number为0的游标是不能共享但两个新的子游标使用扩展游标共享
SQL> SELECT child_number, is_bind_sensitive, is_bind_aware, is_shareable 2 FROM v$sql 3 WHERE sql_id = '7y1vyk0wnb2ut' 4 ORDER BY child_number; CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE ------------ ----------------- ------------- ------------ 0 N N N 1 N Y Y 2 N Y Y
你可能希望查看这个游标相关的执行计划,你可以看到一个新的子游标的执行计划是执行全表扫描,而另外的
执行计划是执行索引扫描
----------------------------------------------------------
Plan hash value: 1883549804
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 10 | 210 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T | 4 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
----------------------------------------------------------
Plan hash value: 1883549804
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
|* 2 | TABLE ACCESS FULL| T | 990 | 1050 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
----------------------------------------------------------
Plan hash value: 1883549804
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 10 | 210 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T | 4 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
为了进一步分析生成两个子游标的原因,有一些新的动态性能视图可以使用
v$sql_cs_statistics, v$sql_cs_selectivity,v$sql_cs_histogram,第一步来显示每个子游标的相关
执行统计信息是否使用了窥视.在下面的输出中,可以确定子游标为1的处理的行数要比子游标为2的多很多.
因此,在这种情况,查询优化器选择全表扫描而另一个使用索引扫描
SQL> SELECT child_number, peeked, executions, rows_processed, buffer_gets 2 FROM v$sql_cs_statistics 3 WHERE sql_id = '7y1vyk0wnb2ut' 4 ORDER BY child_number; CHILD_NUMBER PEEKED EXECUTIONS ROWS_PROCESSED BUFFER_GETS ------------ ------ ---------- -------------- ----------- 0 Y 1 10 3 1 Y 1 990 19 2 Y 1 10 3
v$sql_cs_selectivity视图显示了每一个子游标每一个谓词相关的基数选择范围.事实上,数据库引擎不会
对每一个绑定变量值创建一个新的子游标而是让一组值有相同的选择范围,那么这一组值有相同的执行计划.
SQL> SELECT child_number, predicate, low, high 2 FROM v$sql_cs_selectivity 3 WHERE sql_id = '7y1vyk0wnb2ut' 4 ORDER BY child_number; CHILD_NUMBER PREDICATE LOW HIGH ------------ --------- ---------- ---------- 1总之,如果为了要增加查询优化器生成高效的执行计划的概率你不应该使用绑定变量,绑定变量窥视可能会有帮助,
但不幸地是,有时会生成一个低效的执行计划.唯一的例外是只有在oracle11G中使用了自适应游标共享才解决这个问题任何功能只有当它的优点大于它的缺点时才选择使用.在有些情况下很容易判断,例如,在没有where子句的情况下,
sql语句没有理由不使用绑定变量(如,insert语句).另一方面当查询优化器使用直方图提供重要信息时要不惜代价的
避免使用绑定变量否则使用绑定变量窥视有很高的风险.在其它的情况是否使用绑定变量主要考虑以下两点:sql语句处理少量数据:每当少量数据被处理时,sql语句的解析时间可能接近或超过sql语句的执行时间.在这种情况下
使用绑定变量通常是可取的办法.尤其是对哪些经常执行的sql语句来说更是如此.通常这样的语句使用在数据录入系统
(也就是通常所说的OLTP系统).sql语句处理大量数据:每当大量数据被处理时,sql语句的解析时间通常比sql语句的执行时间低几个数量级.在这种
情况下,使用绑定变量对整个sql语句的响应时间不会有什么影响,但它可能会增加查询优化器生成低效执行计划的概率.
因此,通常这时是不使用绑定变量.一般来说,象批量job,报表,或数据仓库(OLAP)是不使用绑定变量的