在oracle数据库的日常运维和性能优化中经常会碰到绑定变量，有未使用绑定变量数据库性能较差的，也有用了绑定变量而数据库性能反而恶化的。这背后的原因是什么？绑定变量能解决什么问题，使用什么场景，不适用什么场景，使用中有哪些注意事项，和绑定变量相关的有几个技术特性如绑定变量窥探（bind variable peeking），自适应共享游标（ACS），为什么要有这些特性，它们适用的哪种场景，有哪些局限性，这篇文章试着把这些问题说清楚。
1 绑定变量解决的问题
1.1 未使用绑定变量
绑定变量解决的是什么问题？请看一下一个简单的示例。下面这段plsql代码没有使用绑定变量，每一次执行从SH.CUSTOMERS中返回一行数据，总共执行10000次
DECLARE
name varchar2(40);
v_sql_str VARCHAR2(200);
begin
for i in 1..10000 LOOP
v_sql_str := 'SELECT CUST_FIRST_NAME FROM SH.CUSTOMERS WHERE CUST_ID =' ||to_char(i);
EXECUTE IMMEDIATE v_sql_str;
end loop;
END;
设置会话10046事件后，执行这段代码，用tkprof解析后总体分析结果如下
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1996 1.63 2.25 7 2142 0 0
Execute 2033 0.06 0.07 0 0 0 0
Fetch 128 0.00 0.00 0 339 0 81
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4157 1.70 2.33 7 2481 0 81
Misses in library cache during parse: 1981
Misses in library cache during execute: 11
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch: shared pool 3 0.00 0.00
1972 user SQL statements in session.
26 internal SQL statements in session.
1998 SQL statements in session.
sql语句执行了10000次，此次会话跟踪追踪到了不到2000次，在追踪的时间段内，解析消耗的数据库时间为2.25秒，CPU时间为1.63秒，执行消耗的数据库时间为0.07秒，CPU时间为0.06秒，解析执行了2142次查询，执行了1996次硬解析，Misses in library cache during parse值为1981，Misses in library cache during execute为11，大部分资源都消耗在解析上。而且，跟踪期间还发生了latch: shared pool等待事件。如果能避免重复解析，执行效率会有显著的提升，在这种情况下，会是几个数量级的提升。解决的办法就是使用绑定变量，上面代码改成下面的形式
1.2 使用绑定变量
DECLARE
v_sql_str VARCHAR2(200);
begin
for i in 1..10000 LOOP
v_sql_str := 'SELECT CUST_FIRST_NAME FROM SH.CUSTOMERS WHERE CUST_ID =:id';
EXECUTE IMMEDIATE v_sql_str USING i;
end loop;
END;
与前面的代码不同的是，这里的代码使用了绑定变量CUST_ID =:id，每次执行时使用using i给绑定变量赋值，同样执行10000次，设置会话跟踪后用tkprof解析,
SELECT CUST_FIRST_NAME
FROM
SH.CUSTOMERS WHERE CUST_ID =:id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 0.52 0.54 0 1 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 0.52 0.54 0 1 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
上面这段截取的是语句 SELECT CUST_FIRST_NAME FROM SH.CUSTOMERS WHERE CUST_ID =:id
的跟踪分析，Oracle对这条语句只进行了一次硬解析，Misses in library cache during parse的值为1，执行了10000次，解析消耗的CPU和数据库时间小于10毫秒，执行消耗的时间540毫秒，占用的CPU时间为520毫秒，解析占用的时间和CPU几乎可以忽略。解析执行的sql也被tkprof忽略了，下面看一下追踪时间段的整个分析结果
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 76 0.03 0.03 0 147 0 0
Execute 10593 0.72 0.73 0 286 0 0
Fetch 1283 0.01 0.02 0 2436 0 2407
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11952 0.78 0.79 0 2869 0 2407
Misses in library cache during parse: 45
Misses in library cache during execute: 43
4 user SQL statements in session.
57 internal SQL statements in session.
61 SQL statements in session.
在会话追踪的时间段内，解析的耗费的数据库时间为30ms，耗费的CPU时间也是30ms，因为是近似值，所以这两个值相同，执行占用CPU的时间为720ms，数据库时间为730ms，解析消耗的数据库和CPU时间几乎可以忽略不计。同不使用绑定变量的整体分析结果比较，不使用绑定变量时，整个解析消耗的时间为2.25s（2250ms），使用绑定变量后，整个解析消耗的时间几乎可以忽略。而且在不使用绑定变量时，在共享池闩锁上还发生了等待事件，因为硬解析要申请父游标和子游标的内存，这两个申请操作都要申请共享池闩锁。
1.3 为何硬解析的成本如此之高
对一条SQL语句执行硬解析，Oracle数据库需要从头开始执行创建SQL语句执行计划的完整过程。其流程始于语法解析，验证SQL结构合法性（如关键字顺序）并识别语句类型；随后进入语义解析阶段，校验对象存在性（如表、列）、用户权限，并展开视图或子查询等逻辑转换。接着，优化器基于统计信息进行逻辑优化（如谓词下推）与物理优化，通过成本模型评估数千种可能的访问路径（如索引扫描、全表扫描），最终选择成本最低的执行计划。完成优化后，系统将SQL文本、执行计划等元数据存入共享池的库缓存中，供后续复用。
这个过程消耗大量CPU资源（因复杂计算与递归查询数据字典），触发共享池内存争用（LRU机制淘汰旧游标）及闩锁竞争（如library cache latch）。
在高并发的OLTP环境，可能因频繁硬解析导致CPU过载与响应延迟，如果未使用绑定变量，虽然执行的SQL的逻辑相同，但是他们的因值却不同（如WHERE id=100与WHERE id=200），这样就会反复触发硬解析，加剧性能瓶颈，就像这篇文章中第一个实例所展示的那样。
2 绑定变量相关技术及参数
这里介绍两个和绑定变量经常一起提到的技术，绑定变量窥探（bind variable peeking）和自适应游标共享（ACS-Adaptive Cursor Share），这两个特性都是为了解决在不同的绑定变量下如何执行合适的执行计划的问题，这两个特性的开启是由隐含参数来控制的，下面的SQL语句可以查询相关的隐含参数
SQL> select i.KSPPINM,v.KSPPSTVL
from x$ksppi i inner join x$ksppcv v
on v.indx=i.indx
where i.KSPPINM in ('_optim_peek_user_binds','_optimizer_adaptive_cursor_sharing',
'_optimizer_extended_cursor_sharing','_optimizer_extended_cursor_sharing_rel');
KSPPINM KSPPSTVL
------------------------------------------------ --------------------------------
_optimizer_extended_cursor_sharing UDO
_optimizer_extended_cursor_sharing_rel SIMPLE
_optimizer_adaptive_cursor_sharing TRUE
_optim_peek_user_binds TRUE
和bind variable peeking、ACS相关的隐含参数就是上面4个，这4个参数可以在会话级和实例级设置，分别解释如下：
- _OPTIMIZER_EXTENDED_CURSOR_SHARING：生成的cursor是否具有bind sensitive属性，UDO表明会生成bind sensitive的cursor(默认值)，NONE表示不生成bind sensitive，由于变成bind sensitive的cursor才有可能变为bind aware，设置为NONE意味着不启用Adaptive cursor sharing特性
- _OPTIMIZER_EXTENDED_CURSOR_SHARING_REL：决定了哪些操作里cursor可以为bind sensitive属性，默认值是simple表示>,<,=,<>和like等操作里均会使cursor变为bind sensitive，如果设为None则代表cursor永远不会变成bind sensitive，由于变成bind sensitive才有可能变为bind aware所以设为None就意味着禁用ACS功能
- _OPTIMIZER_ADAPTIVE_CURSOR_SHARING：默认值为TRUE，启用Adaptive Cursor sharing特性，表示能将bind sensitive的cursor在一定条件下转为bind aware的cursor。为NONE则禁用掉ACS特性
- _optim_peek_user_binds 是否开启绑定变量窥探
上面的语句查出的绑定变量设置的值都是数据库的默认值，可以看到，绑定变量窥探和ACS默认都是开启的。
3 绑定变量带来的问题
还是用实际的例子来演示绑定变量存储带来的问题，由于默认情况下，绑定变量窥探是打开的，在运行示例代码前先关闭绑定bind variable peeking。
3.1 关闭绑定变量窥探
绑定变量窥探在实例级别默认是打开的，可以在会话级别关闭，
alter session set "_optim_peek_user_binds"=false;
3.2 从一条语句的执行计划来看绑定变量的局限
下面的SQL语句查询的sh.CUSTOMER_EU表在COUNTRY_ID列上数据分布时不均匀的，COUNTRY_ID列上也已搜集直方图统计信息，oracle优化器可以根据不同的列值选择不同的执行计划。
VARIABLE n NUMBER
EXECUTE :n := 52788;
SELECT * FROM sh.CUSTOMER_EUR WHERE COUNTRY_ID= :n;
SQL语句的执行计划是
SQL_ID dunuv5k933kqm, child number 0
-------------------------------------
SELECT * FROM sh.CUSTOMER_EUR WHERE COUNTRY_ID= :n
Plan hash value: 3148057754
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 91 |00:00:00.01 | 868 |
|* 1 | TABLE ACCESS FULL| CUSTOMER_EUR | 1 | 3820 | 91 |00:00:00.01 | 868 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COUNTRY_ID"=:N)
优化器评估的行数和表中实际的行数很大，显然不是根据实际情况评估的，那么，优化器的值是从何而来呢？用下面的SQL查询一下
SELECT count(*)/count(DISTINCT COUNTRY_ID ) FROM sh.CUSTOMER_EUR;
COUNT(*)/COUNT(DISTINCTCOUNTRY_ID)|
----------------------------------+
3820.375|
优化器对查询条件返回行数的评估同数据在列上均匀分布时每一个值返回的行数一致。优化器在生成执行计划时，由于不能获得绑定变量的值，因此选择数据均匀分布时每列的返回行数，不能利用直方图上列的统计信息，从而选择了错误的执行计划。
3.3 绑定变量带来的问题
从这个例子可以看到，在数据分布倾斜严重时，绑定变量生成的执行计划可能不是最优的，导致这个问题的原因是绑定变量的值是在语句执行前来获取的，在生成语句的执行计划时，Oracle优化器并不知道这个值，从而不能根据这个值来做实际的行数评估，解决这个问题的技术就是bind variable peeking和ACS，下面逐一介绍。
4 bind-peeking
既然绑定变量存在的问题是由优化器在生成执行计划时不能获得绑定变量实际的值引起的，解决这个问题的第一步就是让优化器在进行硬解析时获得这个值，这个技术Oracle就叫做bind variable peeking（绑定变量窥探），这个特性默认是打开的，要实验这个特性重新开一个会话就行，不过要在这个会话关闭ACS特性。实验前先刷新一下数据库的共享池（这个属于危险操作，生产环境可不能执行这个操作），
ALTER SYSTEM FLUSH SHARED_POOL;
--执行计划如下
Plan hash value: 968808656
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 91 |00:00:00.01 | 91 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER_EUR | 1 | 91 | 91 |00:00:00.01 | 91 |
|* 2 | INDEX RANGE SCAN | IDX_CE_C | 1 | 91 | 91 |00:00:00.01 | 9 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COUNTRY_ID"=:N)
使用bind variable peeking后优化器生成了正确的执行计划，解决了之前优化器行数评估不准确的问题。到目前为止，效果非常好。我们再给绑定变量赋一个值来看看。
EXECUTE :n := 52776;
SELECT * FROM sh.CUSTOMER_EUR WHERE COUNTRY_ID= :n;
Plan hash value: 968808656
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8173 |00:00:00.03 | 1897 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER_EUR | 1 | 91 | 8173 |00:00:00.03 | 1897 |
|* 2 | INDEX RANGE SCAN | IDX_CE_C | 1 | 91 | 8173 |00:00:00.01 | 564 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COUNTRY_ID"=:N)
问题又出现了，优化器评估的行数是91行，实际却是8173行，访问路径应该选择全表扫描，优化器却选择了索引范围扫描。导致这个问题的原因是bind variable peeking只在硬解析时生效，后面的执行会直接使用先前的执行计划。如果绑定变量不同的值返回的行数差异很大，使用的执行计划是硬解析时生成的，这个导致一些执行会选择次优化的执行计划，具体执行哪个执行计划，完全由硬解析时确定，这就好像是撞大运，如果硬解析时恰巧碰到了执行评率很低的变量值，会导致执行频率很高变量值选择了性能较差的执行计划，可能会造成灾难性的后果。不过，Oracle总有解决方案，这就是ACS（自适应游标共享），请看下一节。
5 自适应绑定游标共享
自适应绑定变量又称为bind-aware游标共享，它的目的是自动识别因执行计划（游标）重用导致的SQL语句性能低下的问题，也就是上一节里绑定变量窥探导致的问题。这里还是用前面的例子来说明。上一节的SQL刷新共享池后给绑定变量传入值52788的执行完毕后(bind variable peeking,ACS都是打开的），查询v$SQL视图，语句的信息如下：
--------------------------------------------------+-------------+------------+-----------------+-------------+------------+
SELECT SQL_TEXT,SQL_ID,CHILD_NUMBER,IS_BIND_SENSITIVE,IS_BIND_AWARE,IS_SHAREABLE FROM "V$SQL" WHERE SQL_ID='dunuv5k933kqm';
SQL_TEXT |SQL_ID |CHILD_NUMBER|IS_BIND_SENSITIVE|IS_BIND_AWARE|IS_SHAREABLE|
--------------------------------------------------+-------------+------------+-----------------+-------------+------------+
SELECT * FROM sh.CUSTOMER_EUR WHERE COUNTRY_ID= :n|dunuv5k933kqm| 0|Y |N |Y |
--------------------------------------------------+-------------+------------+-----------------+-------------+------------+
视图v$SQL里的几个列这里解释一下：
- is_bind_sensitive 不但指示这个执行计划的产生使用了bind variable peeking技术，也指示是否可能使用自适应游标共享技术。列值为Y表示可能会用到自适应游标共享;如果不需要考虑自适应游标共享，列值为N。
- is_bind_aware 游标是否使用自适应游标共享产生的， 如果是，值为Y, 否则为N。
- is_shareable 只是游标是否能被共享。能被共享，列值为 Y; 否则为N。如果列值为N, 游标不能被重用。
根据上面语句的查询结果，这个游标可以被共享，优化器生成执行计划时使用了bind variable peeking，这个游标可能会使用到自适应游标技术。
给绑定变量传入另一个值52776，执行后再看，语句的执行计划如下
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8173 |00:00:01.75 | 1904 | 776 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER_EUR | 1 | 91 | 8173 |00:00:01.75 | 1904 | 776 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IDX_CE_C | 1 | 91 | 8173 |00:00:00.02 | 564 | 0 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COUNTRY_ID"=:N)
可以看到，这次执行。Oracle选择的是错误的执行计划，优化器评估的行数严重偏离实际行数，查询一下v$sql视图
再次执行上面的SQL语句，执行计划如下
SQL_ID brz8d3b9vmnsw, child number 0
-------------------------------------
SELECT * FROM sh.CUSTOMER_EUR WHERE COUNTRY_ID= :n
Plan hash value: 3148057754
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8173 |00:00:00.04 | 1382 | 2 |
|* 1 | TABLE ACCESS FULL| CUSTOMER_EUR | 1 | 8173 | 8173 |00:00:00.04 | 1382 | 2 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COUNTRY_ID"=:N)
18 rows selected.
查询v$sql视图，结果如下
SQL_TEXT |SQL_ID |CHILD_NUMBER|IS_BIND_SENSITIVE|IS_BIND_AWARE|IS_SHAREABLE|
--------------------------------------------------+-------------+------------+-----------------+-------------+------------+
SELECT * FROM sh.CUSTOMER_EUR WHERE COUNTRY_ID= :n|dunuv5k933kqm| 0|Y |N |N |
SELECT * FROM sh.CUSTOMER_EUR WHERE COUNTRY_ID= :n|dunuv5k933kqm| 1|Y |Y |Y |
给绑定变量赋给不同的值，反复实验，发现Oracle会为每个执行计划保留一个可共享的游标（执行计划），保留共享状态的游标根据传入的绑定变量的值而定，每当传入的绑定变量的值对于它的执行计划来说限定性更强，对于全表扫描来说，条件表达式返回的行数更少，对于索引范围扫描来说，条件表达式返回的行数更多，Oracle会创建新的执行计划，将原来的执行计划置为不可共享状态。看下面的sql查询结果，SQL plan 哈希值相同的子游标有多个，但是只有一个的状态是可共享的。
SELECT SQL_TEXT,SQL_ID,CHILD_NUMBER,PLAN_HASH_VALUE,IS_BIND_SENSITIVE,IS_BIND_AWARE,IS_SHAREABLE FROM "V$SQL" WHERE SQL_ID='dunuv5k933kqm';
SQL_TEXT |SQL_ID |CHILD_NUMBER|PLAN_HASH_VALUE|IS_BIND_SENSITIVE|IS_BIND_AWARE|IS_SHAREABLE|
--------------------------------------------------+-------------+------------+---------------+-----------------+-------------+------------+
SELECT * FROM sh.CUSTOMER_EUR WHERE COUNTRY_ID= :n|dunuv5k933kqm| 0| 968808656|Y |N |N |
SELECT * FROM sh.CUSTOMER_EUR WHERE COUNTRY_ID= :n|dunuv5k933kqm| 1| 3148057754|Y |Y |N |
SELECT * FROM sh.CUSTOMER_EUR WHERE COUNTRY_ID= :n|dunuv5k933kqm| 2| 968808656|Y |Y |N |
SELECT * FROM sh.CUSTOMER_EUR WHERE COUNTRY_ID= :n|dunuv5k933kqm| 3| 3148057754|Y |Y |Y |
SELECT * FROM sh.CUSTOMER_EUR WHERE COUNTRY_ID= :n|dunuv5k933kqm| 4| 968808656|Y |Y |Y |
使用自适应游标共享的sql的硬解析的次数有明显的增加，这在复杂的sql语句可能情况更为严重。这个技术还有一个缺陷，就是在生成可以共享的自适应游标之前有一次语句执行的是错误的执行计划，这个高并发的生产环境可能会导致叫严重的后果。这种情况可以使用hint来解决，将语句写成下面的形式
SELECT /*+ BIND_AWARE */* FROM sh.CUSTOMER_EUR WHERE COUNTRY_ID= :n;
另外值得一提的是，Oracle的自适应游标共享的技术在某些版本还存在较严重的bug，有一些生产环境这个技术是手动关闭的。
6 总结
绑定变量是为解决高并发OLTP环境性sql语句重复硬解析的问题而产生的技术，适用于特定的场景，比如数据分布比较一致，语句执行本身比较快，查询条件为等值等，尤其适用于高并发的insert操作，在这些场景下，语句的硬解析时间往往比语句执行的时间还要长很多，高并发引起的闩锁冲突会导致严重的性能问题，绑定变量的使用有其必要性。
如果数据分布倾斜比较大，或者是非等值的范围查询，使用绑定变量会对Oracle优化器隐藏重要的信息，导致优化器选择次优化的执行计划，虽然Oracle也提供了绑定变量窥探、自适应游标共享等技术，仍然坑你导致意想之外的结果。尤其在OLAP的场景，硬解析的时间同语句执行的时间相比往往可以忽略，这种情况下应该避免使用绑定变量。
