【DB笔试面试582】在Oracle中，什么是绑定变量窥探（上）？
【DB笔试面试582】在Oracle中，什么是绑定变量窥探（上）？
♣
题目部分
在Oracle中，什么是绑定变量窥探（上）？
♣
答案部分
目标SQL若不使用绑定变量，则当具体输入值一旦发生了变化，目标SQL的SQL文本就会随之发生变化，这样Oracle就能很容易地计算出对应Selectivity和Cardinality的值，进而据此来选择执行计划。但对于使用了绑定变量的目标SQL而言，情况就完全不一样了，因为现在无论对应绑定变量的具体输入值是什么，目标SQL的SQL文本都是一模一样的。对于使用了绑定变量的目标SQL而言，Oracle可以选择如下两种方法来决定其执行计划：
l 使用绑定变量窥探（Bind Peeking）。
l 如果不使用绑定变量窥探，那么对于那些可选择率可能会随着具体输入值的不同而不同的谓词条件使用默认的可选择率（例如5%）
绑定变量窥探（Bind Peeking）是在Oracle 9i中引入的，是否启用绑定变量窥探受隐含参数“_OPTIM_PEEK_USER_BINDS”的控制，该参数的默认值是TRUE，表示在Oracle 9i及其后续的版本中，默认启用绑定变量窥探。
SYS@orclasm > set pagesize 9999
SYS@orclasm > set line 9999
SYS@orclasm > col NAME format a40
SYS@orclasm > col KSPPDESC format a50
SYS@orclasm > col KSPPSTVL format a20
SYS@orclasm > SELECT a.INDX,
2 a.KSPPINM NAME,
3 a.KSPPDESC,
4 b.KSPPSTVL
5 FROM x$ksppi a,
6 x$ksppcv b
7 WHERE a.INDX = b.INDX
8 and lower(a.KSPPINM) like lower('%¶meter%');
Enter value for parameter: _OPTIM_PEEK_USER_BINDS
old 8: and lower(a.KSPPINM) like lower('%¶meter%')
new 8: and lower(a.KSPPINM) like lower('%_OPTIM_PEEK_USER_BINDS%')
INDX NAME KSPPDESC KSPPSTVL
---------- ---------------------------------------- -------------------------------------------------- --------------------
2050 _optim_peek_user_binds enable peeking of user binds TRUE
绑定变量窥探的优缺点如下所示：
① 优点：当绑定变量窥探被启用后，每当Oracle以硬解析的方式解析使用了绑定变量的目标SQL时，Oracle都会实际窥探（Peeking）一下对应绑定变量的具体输入值，并以这些具体输入值为标准，来决定这些使用了绑定变量的目标SQL的WHERE条件的Selectivity和Cardinality的值，并据此来选择该SQL的执行计划。需要注意的是，这里这个“窥探（Peeking）”的动作只有在硬解析的时候才会执行，当使用了绑定变量的目标SQL再次执行时（此时对应的是软解析/软软解析），即便此时对应绑定变量的具体输入值和之前硬解析时对应的值不同，Oracle也会沿用之前硬解析时所产生的解析树和执行计划，而不再重复执行上述“窥探”的动作。因为有了绑定变量窥探，所以，Oracle在计算目标SQL的WHERE条件的Selectivity和Cardinality的值时，就可以避免使用默认的可选择率，这样就有更大的可能性能得到该SQL准确的执行计划。
② 缺点：对于那些执行计划可能会随着对应绑定变量具体输入值的不同而变化的目标SQL而言一旦启用了绑定变量窥探，其执行计划就会被固定下来，至于这个固定下来的执行计划到底是什么，则完全倚赖于该SQL在硬解析时传入的对应绑定变量的具体值。这意味着一旦启用了绑定变量窥探，目标SQL在后续执行时就只会沿用之前硬解析所产生的解析树和执行计划，即使当时的执行计划和解析树并不适合于新传入的值。
关于绑定变量窥探需要注意以下几点：
（1）在Oracle llg中引入自适应游标共享后，绑定变量窥探这种不管后续传入的绑定变量的具体输入值是什么而一直沿用之前硬解析时所产生的解析树和执行计划的缺点才有所缓解。
（2）在不改变SQL语句文本的情况下，让SQL语句重新进行硬解析的方法有：①对SQL语句涉及到的对象执行DDL操作（例如COMMENT语句）。②执行DBMS_SHARED_POOL.PURGE来删除共享池中的游标。③在重新收集统计信息时指定NO_INVALIDATE=>FALSE选项。
绑定变量窥探这种不管后续传入的绑定变量的具体输入值是什么而一直沿用之前硬解析时所产生的解析树和执行计划的特性一直饱受诟病（这种状况一直到Oracle llg中引入自适应游标共享后才有所缓解），因为绑定变量窥探可能使CBO在某些情况下（对应绑定变量的某些具体输入值）所选择的执行计划并不是目标SQL在当前情形下的最优执行计划，而且它可能会带来目标SQL执行计划的突然改变，进而直接影响应用系统的性能。
绑定变量窥探的副作用在于，一旦启用（默认情况下绑定变量窥探就己经被启用），使用了绑定变量的目标SQL就只会沿用之前硬解析时所产生的解析树和执行计划，即使这种沿用完全不适合当前的情形，即根据第一次传入的值然后固化执行计划。
下面给出绑定变量窥探的示例：
数据库版本为11.2.0.3，做如下的准备工作：
CREATE TABLE T_BP_20170609_LHR AS SELECT * FROM DBA_OBJECTS;
CREATE INDEX IDX_OBJID_LHR ON T_BP_20170609_LHR(OBJECT_ID);
SELECT COUNT(1) FROM T_BP_20170609_LHR;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_BP_20170609_LHR',ESTIMATE_PERCENT => 100,CASCADE => TRUE,METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',NO_INVALIDATE => FALSE);--不收集直方图
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102; --3 5q51c7s4z0dp9
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000; --59539 gpbkt45132r8x
LHR@orclasm > COL SQL_TEXT FORMAT A100
LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT FROM V$sqlarea A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(1) FROM T_BP_20170609_LHR T%';
SQL_TEXT SQL_ID VERSION_COUNT
---------------------------------------------------------------------------------------------------- ------------- -------------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102 5q51c7s4z0dp9 1
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000 gpbkt45132r8x 1
--下面查看其执行计划：
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5q51c7s4z0dp9',0,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------
SQL_ID 5q51c7s4z0dp9, child number 0
-------------------------------------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102
Plan hash value: 1089369592
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| IDX_OBJID_LHR | 3 | 15 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" ("T_BP_20170609_LHR"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_ID">=100 AND "T"."OBJECT_ID"<=102)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
45 rows selected.
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gpbkt45132r8x',0,'advanced'));
PLAN_TABLE_OUTPUT
---------------------------------------------
SQL_ID gpbkt45132r8x, child number 0
-------------------------------------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100AND 60000
Plan hash value: 1768048749
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 50 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_OBJID_LHR | 28807 | 140K| 50 (2)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T_BP_20170609_LHR"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("T"."OBJECT_ID"<=60000 AND "T"."OBJECT_ID">=100))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
--可见，SQL1选择的是索引范围扫描，SQL2选择的是索引快速全扫描，下面使用绑定变量的形式，定义两个绑定变量X和Y，并赋值100和102：
LHR@orclasm > VAR X NUMBER;
LHR@orclasm > VAR Y NUMBER;
LHR@orclasm > EXEC :X :=100;
PL/SQL procedure successfully completed.
LHR@orclasm > EXEC :Y :=102;
PL/SQL procedure successfully completed.
LHR@orclasm > SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y;
COUNT(1)
----------
3
LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT FROM V$sqlarea A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(1) FROM T_BP_20170609_LHR T%';
SQL_TEXT SQL_ID VERSION_COUNT
---------------------------------------------------------------------------------------------------- ------------- -------------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102 5q51c7s4z0dp9 1
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y gya9jjznchps5 1
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000 gpbkt45132r8x 1
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gya9jjznchps5',0,'advanced'));
PLAN_TABLE_OUTPUT
-------------------------------------------------
SQL_ID gya9jjznchps5, child number 0
-------------------------------------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y
Plan hash value: 196260839
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_OBJID_LHR | 3 | 15 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" ("T_BP_20170609_LHR"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 100
2 - :Y (NUMBER): 102
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:X<=:Y)
3 - access("T"."OBJECT_ID">=:X AND "T"."OBJECT_ID"<=:Y)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
从上述显示内容可以看出，Oracle此时选择的执行计划是走对索引IDX_OBJID_LHR的索引范围扫描，而且Oracle评估出来执行这个索引范围扫描所返回结果集的Cardinality的值为3。注意到“Peeked Binds”部分的内容为“1 - :X (NUMBER): 100”和“2 - :Y (NUMBER): 102”，这说明Oracle在硬解析上述SQL的过程中确实使用了绑定变量窺探，且做“窥探”这个动作时看到的绑定变量X和Y的具体输入值分别为100和102。
现在保持X的值不变，将Y的值修改为60000，如下所示：
LHR@orclasm > EXEC :Y :=60000;
PL/SQL procedure successfully completed.
LHR@orclasm > SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y;
COUNT(1)
----------
59539
LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT,A.EXECUTIONS FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(1) FROM T_BP_20170609_LHR T%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
---------------------------------------------------------------------------------------------------- ------------- ------------- ----------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102 5q51c7s4z0dp9 1 1
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y gya9jjznchps5 1 2
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000 gpbkt45132r8x 1 1
从查询结果可以看到上述SQL对应的列VERSION_COUNT的值为1，列EXECUTIONS的值为2，这说明Oracle在第二次执行该SQL时用的是软解析。此时SQL的执行计划为：
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gya9jjznchps5',0,'advanced'));
PLAN_TABLE_OUTPUT
-------------------------------------------------
SQL_ID gya9jjznchps5, child number 0
-------------------------------------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X
AND :Y
Plan hash value: 196260839
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_OBJID_LHR | 3 | 15 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" ("T_BP_20170609_LHR"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 100
2 - :Y (NUMBER): 102
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:X<=:Y)
3 - access("T"."OBJECT_ID">=:X AND "T"."OBJECT_ID"<=:Y)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
从上述显示内容可以看出，Oracle此时依然选择的执行计划是走对索引IDX_OBJID_LHR的索引范围扫描，而且“Peeked Binds”部分的内容依然为“1 - :X (NUMBER): 100”和“2 - :Y (NUMBER): 102”，这说明Oracle在第2次执行使用绑定变量的SQL时不再重复做“窥探”动作。
此时若想Oracle进行“窥探”动作，则只能让以前的共享游标失效，重复执行一次硬解析即可。让Oracle再次执行目标SQL时使用硬解析的方法有很多，其中很常见的一种方法是对目标SQL中所涉及的表执行DDL操作。因为一旦对某个表执行了DDL操作，库缓存中所有在SQL文本中包含了这个表的Shared Cursor都会被Oracle标记为失效（INVALID），这意味着这些Shared Cursor中存储的解析树和执行计划将不再能被重用，所以当Oracle再次执行与这个表相关的SQL时就会使用硬解析。
所以，现在只需要对表T_BP_20170609_LHR执行一个DDL操作，就可以让Oracle再次执行上述SQL时使用硬解析了。DDL操作有很多种，通常选择添加注释的COMMENT语句，因为使用COMMENT添加注释也是DDL操作，但同时它的杀伤力和对生产环境的影响又微乎其微。
LHR@orclasm > COMMENT ON TABLE T_BP_20170609_LHR IS 'TEST BIND PEEKING';
Comment created.
LHR@orclasm > SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y;
COUNT(1)
----------
59539
LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT,A.EXECUTIONS FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(1) FROM T_BP_20170609_LHR T%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
---------------------------------------------------------------------------------------------------- ------------- ------------- ----------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102 5q51c7s4z0dp9 1 1
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y gya9jjznchps5 1 1
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000 gpbkt45132r8x 1 1
从查询结果可以看到上述SQL对应的列VERSION_COUNT的值为1，列EXECUTIONS的值由之前的2变为了现在的1，这说明Oracle在第三次执行上述SQL时用的是硬解析（EXECUTIONS的值为1，是因为Oracle在这里重新生成了一对Parent Cursor和Child Cursor，原先EXECUTIONS的值为2所对应的Shared Cursor己经被Oracle标记为INVALID，相当于被废弃了）。
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gya9jjznchps5',0,'advanced'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID gya9jjznchps5, child number 0
-------------------------------------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y
Plan hash value: 2066501558
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 50 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX FAST FULL SCAN| IDX_OBJID_LHR | 28807 | 140K| 50 (2)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T_BP_20170609_LHR"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 100
2 - :Y (NUMBER): 60000
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:X<=:Y)
3 - filter(("T"."OBJECT_ID"<=:Y AND "T"."OBJECT_ID">=:X))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
53 rows selected.
LHR@orclasm >
从上述显示内容可以看出，Oracle此时选择的执行计划己经从之前的走对索引IDX_OBJID_LHR的索引范围扫描变为索引快速全扫描，而且Oracle评估出来执行这个索引快速全扫描所返回结果集的Cardinality的值为28807。注意到“Peeked Binds”部分的内容为“1 - :X (NUMBER): 100”和“2 - :Y (NUMBER): 60000”，这说明Oracle在执行上述SQL的过程中确实又一次使用了绑定变量窥探，且做“窥探”这个动作时看到的绑定变量和X和Y的具体输入值分别为100和60000。
在这里，Oracle评估出来对索引IDX_OBJID_LHR执行索引范围扫描所返回结果集的Cardinality的值为3，对执行索引快速全扫描所返回结果集的Cardinality的值为28807。下面来解释一下这两个值是如何计算出来的：
参考下一篇。
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
腾讯云开发者
