- 数据库高效优化：架构、规范与SQL技巧
- 马立和 高振娇 韩锋
- 2116字
- 2020-08-27 05:47:11
6.2 绑定变量与解析
我们通过一个示例看一看带有绑定变量的SQL语句解析。
create table t1 as select * from dba_objects;
varv_name varchar2(30)
exec :v_name:='T1'
select * from t1 where object_name=:v_name;
SQL> select sql_id, address, hash_value ,plan_hash_value ,child_number
2 from v$sql
3 where sql_text like 'select * from t1 where object_name=:v_name';
SQL_ID ADDRESS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER
------------------------ ---------------- ---------- --------------- -----------
6g1g39543bkvc 000007FF3769DB00 1211485036 3617692013 0
SQL> select * from table(dbms_xplan.display_cursor('6g1g39543bkvc',0, 'peeked_binds'));
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 74 (100)| |
|* 1 | TABLE ACCESS FULL | T1 | 3 | 621 | 74 (0)| 00:00:01 |
---------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :V_NAME (VARCHAR2(30), CSID=873): 'T1'
从上面这个例子可见，对于一个带有绑定变量的语句，也是可以显示其执行计划的。并且，在执行计划中有单独的一个部分显示绑定变量的使用情况。这里大家可能会有一个疑问，就是在解析带有绑定变量的语句时，如何得到这个执行计划？这里就引入了一个重要的概念——绑定变量窥视。
1.绑定变量窥视
首先我们来明确一下，绑定变量窥视的概念。在数据库生成执行计划的时候，需要根据条件判断数据的访问规模，从而指出最优的访问路径。当使用的是带有绑定变量的SQL语句时，Oracle会在第一次解析SQL语句的时候，将绑定变量的输入值带到SQL语句中，从而根据其字面值来估算返回的记录数，从而得到执行计划。当再次执行相同的SQL语句时，就不用再考虑绑定变量的输入值了，直接沿用过去的执行计划即可。
下面我们通过一个示例演示一下绑定变量窥视。
create table t1 as select object_id as id,object_name from dba_objects where rownum<=10001;
update t1 set id=1 where rownum<=10000;
commit;
create index idx_t1 on t1(id);
//创建一个表，然后通过数据更新使id字段的数据分布不均匀，并在该字段上创建一个索引
execdbms_stats.gather_table_stats(user,'t1',cascade =>true,method_opt => 'for columns id size 254');
//收集一下统计信息。注意，这里要收集直方图，目的是让CBO知道id列上的数据分布不均匀
select max(id) from t1 where rownum<10;
MAX(ID)
----------
10312
varv_id number;
exec :v_id := 10312;
select * from t1 where id=:v_id;
selectsql_id, address, hash_value ,plan_hash_value ,child_number
fromv$sql
wheresql_text like 'select * from t1 where id=:v_id%';
SQL_ID ADDRESS HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER
----------------------- ---------------- ---------- --------------- ------------
7y7tt6xyhas1g 000007FF32B13C28 2097504303 50753647 0
select * from table(dbms_xplan.display_cursor('7y7tt6xyhas1g',0,'peeked_binds'));
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID
| T1 | 1 | 21 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :V_ID (NUMBER): 10312
/*
从上面的输出可见，这里使用了索引范围扫描的方式。对于这条语句来说（ID=10312）,这是一个不错的执行计划
*/
exec :v_id := 1
select * from t1 where id=:v_id;
select * from table(dbms_xplan.display_cursor('7y7tt6xyhas1g',0,'peeked_binds'));
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID
| T1 | 1 | 21 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :V_ID (NUMBER): 10312
/*
从上面结果可以看出，执行计划没有变化。我们知道，对于ID=1的记录是绝大多数，全表扫描对这个语句来说是一种更优的选择。从下面的绑定变量可知，生成这个执行计划的绑定变量还是第一次执行时的10312，也就是说绑定变量窥视只窥视一次
*/
/*
说明:
从上面结果可以看出，在为绑定变量传入第一个值为10312时，由于返回的记录条数较少，导致走索引扫描。当我们第二次传入绑定变量值1时，Oracle不再生成新的执行计划，而直接拿索引扫描的执行路径来用。但是，如果先传入1的绑定变量值，然后再传入10312的绑定变量值时，先传入1的绑定变量时将导致生成的执行计划走全表扫描。后面传入的13871的绑定变量的最佳执行路径应该是索引扫描，但是由于CBO并不知道这一点，而是直接用第一次生成的执行计划来，于是也走全表扫描
*/
为了解决上面的问题，在11g及以后的版本中，引入了自适应的绑定变量窥视。下面通过示例说明。
select * from t1 where id=:v_id
select * from t1 where id=:v_id
select * from t1 where id=:v_id
//重复上面的例子，多执行几次这个语句
select sql_text,sql_id,child_number,plan_hash_value from v$sql where sql_text like 'select * from t1 where%';
SQL_TEXT SQL_ID CHILD_NUMBER
------------------------------------------ ------------------------- -----------
select * from t1 where id=:v_id 7y7tt6xyhas1g 0
select * from t1 where id=:v_id 7y7tt6xyhas1g 1
//由此处可见，对于这一条语句生成了两个执行计划
select * from table(dbms_xplan.display_cursor('7y7tt6xyhas1g',0,'peeked_binds'));
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID
| T1 | 1 | 21 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :V_ID (NUMBER): 10312
//对于绑定变量传入的值为10312，此时走的索引范围扫描
select * from table(dbms_xplan.display_cursor('7y7tt6xyhas1g',1,'peeked_binds'));
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 13 (100)| |
|* 1 | TABLE ACCESS FULL | T1 | 10000 | 205K| 13 (0)| 00:00:01 |
---------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :V_ID (NUMBER): 1
//对于绑定变量传入的值为1，此时走的全表扫描
/*
说明:
在11g中引入了自适应的游标策略，根据不同的输入值，可以对应不同的执行计划，这大大提高了适应情况。但需要注意的是，前提条件是绑定变量对应的字段收集了直方图。这一点可以理解，如果不收集，是无法知道数据有倾斜的，也就不会有机会生成多个执行计划了
*/
除了上面这种情况，因为数据有倾斜导致一条SQL语句可能有不同执行计划的情况外，还有一种情况会导致这种现象。这就是绑定变量分级，下面针对这种情况加以说明。
2.绑定变量分级
所谓的绑定变量分级，是指Oracle数据库会根据绑定变量的长短将绑定变量分为不同的级别。不同级别的绑定变量会对应不同的子游标。下面通过一个示例说明这种现象。
create table t1 as select rownumid,object_name from dba_objects;
alter table t1 modify name varchar2(255);
varv_name varchar2(30)
exec :v_name:='ABC'
select * from t1 where name=:v_name;
select sql_id, hash_value ,child_number,executions
from v$sql
where sql_text like 'select * from t1 where name=:v_name';
SQL_ID HASH_VALUE CHILD_NUMBER EXECUTIONS
-------------------------- ---------- ------------ ----------
80au64833mru6 104456006 0 1
//我们执行了一条SQL语句，优化器生成了一个游标
varv_name varchar2(200)
exec :v_name:='ABC'
select * from t1 where name=:v_name;
selectsql_id, hash_value ,child_number,executions
fromv$sql
wheresql_text like 'select * from t1 where name=:v_name';
SQL_ID HASH_VALUE CHILD_NUMBER EXECUTIONS
-------------------------- ---------- ------------ ----------
80au64833mru6 104456006 0 1
80au64833mru6 104456006 1 1
/*
尽管这次执行的语句和前面的完全一样，但是数据库生成了两个不同的游标。我们可以通过下面的语句查看为什么生成了两个
*/
selects.child_number,m.position,m.max_length,
decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype) as datatype
fromv$sqls,v$sql_bind_metadata m
wheres.sql_id='80au64833mru6' and s.child_address=m.address
order by 1,2;
CHILD_NUMBER POSITION MAX_LENGTH DATATYPE
------------ ---------- ---------- ----------------------------------
0 1 128 VARCHAR2
1 1 2000 VARCHAR2
//从上面输出可以看出，两个游标是因为最大长度不同，导致生成了不同的游标
总结一下，绑定变量分级是Oracle根据文本型绑定变量的长度分成的若干级别。即使SQL语句相同，但是只要长度不同就仍然生成新的游标。系统是分为4个级别。
·第一个等级：长度在32字节（Byte）以内。
·第二个等级：长度在33~128字节之间。
·第三个等级：长度在129~2000字节之间。
·第四个等级：长度在2000字节以上。
除了上面这种显式使用绑定变量的情况，数据库也会考虑在某些情况下自动使用绑定变量替换原来的值。这种技术称为游标共享，下面详细说明。