索引是我们进行优化的一种重要方式。实际工作中，一个简单的索引，可能就会大大提升提高关键业务作业效率，最终提升用户满意度。在CBO时代，DBA和开发人员经常为索引为什么不出现在执行计划中而困惑。
问题提出
使用绑定变量已经是现在成熟项目开发团队开发规范的一种重要组成部分。对大部分系统场景，特别是OLTP系统，使用绑定变量带来的好处是不可忽视的。使用绑定变量可以有效减少系统硬解析发生，缓解系统实例资源的消耗和争用。但是，一些场合下，绑定变量也是会给我们带来一些困扰。其中，绑定变量窥视peeking是经常会出现的一个问题，会影响到执行计划的正确生成。
实验环境准备
实验选择Oracle 11gR2 For Linux环境。
SQL> create table t as select * from dba_objects;
Table created
SQL> create index idx_t_owner on t(owner);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
标准SQL实验
我们首先使用硬解析手段获取标准的执行计划。硬解析手段虽然要消耗较多的资源，但是生成的执行计划通常是唯一且正确的。
//查询结果省略
SQL> select * from t where wner='SYS';
已选择58483行。
已用时间: 00: 00: 01.75
SQL> select * from t where wner='SCOTT';
已选择6行。
已用时间: 00: 00: 00.00
我们从Shared_pool中直接取出执行计划。
SQL> col sql_text for a40;
SQL> select sql_text, sql_id, version_count from v$sqlarea where sql_text like 'select * from t%';
SQL_TEXT SQL_ID VERSION_COUNT
---------------------------------------- ------------- -------------
select * from t where wner='SYS' 6gzd8z5vm5k0t 1
select * from t where wner='SCOTT' 5ytkakbws70wb 1
v$sqlarea中表示的是父游标的记录信息，相同sql结构的语句都会对应到相同的记录上，使用sql_id进行区分。获取到父游标sql_id之后，就可以获取到当时的执行计划。
SQL> select * from table(dbms_xplan.display_cursor('5ytkakbws70wb',null,null));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5ytkakbws70wb, child number 0
-------------------------------------
select * from t where wner='SCOTT'
Plan hash value: 1516787156
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 13 | 1261 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IDX_T_OWNER | 13 | | 1 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
19 rows selected
SQL> select * from table(dbms_xplan.display_cursor('6gzd8z5vm5k0t',null,null));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6gzd8z5vm5k0t, child number 0
-------------------------------------
select * from t where wner='SYS'
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 280 (100)| |
|* 1 | TABLE ACCESS FULL| T | 57833 | 5478K| 280 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
18 rows selected
此时，我们看到了由于统计量数据分布的差异，使得相同结构的SQL语句，由于选取值分布的差异，执行计划有差异。Owner=’SYS’由于数据量较大，所以选择进行全表扫描的成本cost较低，CBO选择生成全表扫描的执行计划。而scott的数据量较少，选择索引路径相对高效些。
绑定变量路径
下面实验绑定变量路径方式。
SQL> var x varchar2(10);
SQL> exec :x := 'SYS';
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.03
SQL> select * from t where wner=:x;
已选择58483行。
已用时间: 00: 00: 01.87
SQL> exec :x := 'SCOTT';
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.00
SQL> select * from t where wner=:x;
已选择6行。
已用时间: 00: 00: 00.00
我们从shared pool中获取到父游标信息。
SQL> select sql_text, sql_id, version_count, EXECUTIONS from v$sqlarea where sql_text like 'select * from t%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
---------------------------------------- ------------- ------------- ----------
select * from t where wner=:x 25hhj4rsxz9w1 1 2
SQL>
SQL> select count(*) from v$sql where sql_id='25hhj4rsxz9w1';
COUNT(*)
----------
1
//绑定变量信息
SQL> select bind_data from v$sql where sql_id='25hhj4rsxz9w1';
BIND_DATA
--------------------------------------------------------------------------------
BEDA0B2001004DB6ACF7000101F00120036903535953
注意，虽然执行了两次，虽然执行两次使用的绑定变量值不同，但是由于SQL语句结构相同，所以父游标相同。观察version_count=1，证明始终只有一个执行计划。两次执行共享的是一个执行计划。下面提取出执行计划全文。
SQL> select * from table(dbms_xplan.display_cursor('25hhj4rsxz9w1',0,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 25hhj4rsxz9w1, child number 0
-------------------------------------
select * from t where wner=:x
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 280 (100)| |
|* 1 | TABLE ACCESS FULL| T | 57833 | 5478K| 280 (1)| 00:00:04 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
Peeked Binds (identified by position):
--------------------------------------
1 - :X (VARCHAR2(30), CSID=873): 'SYS'
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"=:X)
（篇幅原因，有删节…）
54 rows selected
从执行计划中，我们可以看到两次共享的执行计划路径和采用的方案。其中标红的peeked binds部分，可以看出Oracle在生成这个执行计划时，是使用’SYS’作为参照值进行生成的。因为’SYS’对应的执行路径是全表扫描，所以在第二次执行的时候，即使绑定变量X取值为’SCOTT’，由于公用执行计划的原因，依然使用全表扫描的方式。这就是出现了所谓的binds peeking问题。
Binds peeking是Oracle在生成执行计划时候所采用的一种技术。当Oracle优化器解析语句时，如果这个语句上是携带者绑定变量的，就会使用到这种技术。CBO是依据统计量生成执行计划的，此时需要知道变量取值来确定计划路径。这时候Oracle就会“偷偷”看一下送过来SQL中使用的绑定变量取值（‘SYS’），依据这个值生成执行计划。第二次相同的语句来的时候，就是用第一次peeking生成的执行计划即可。
这样，就出现了本篇要说明的问题，就是peeking有时候会影响到SQL执行的效率。如果出现了本例中的情况，Oracle在peeking的时候，恰好peeking到了一个全表扫描或者错误的路径方案，之后所有的路径都共享这个执行计划，必然之后会带来性能上的突然变化。
绑定变量使用的越来越广泛，那么我们如何避免由于使用绑定变量而带来的binds peeking问题呢？笔者个人觉得从如下几个方面考虑：
ü 列数据分布的详细研究。通常，我们都希望数据列取值尽可能分布均匀，不出现过于偏移的数据分布状况。Peeking不是Oracle的罪过，数据分布不均匀是引起peeking问题的根源。所以，要下力气研究分析数据分布规律，尽量避免分布偏移情况，或者设计对应的解决方案；
ü 进入Oracle10g之后，Oracle引入了多次peeking的方案。也就是对一个绑定变量执行计划，不是一次peeking就结束。而是根据计算成本情况，不定时的多次取binds值，调整执行计划。这样，综合获取到适当执行计划的几率有所提升；
ü 最后是一个“以暴制暴”的方法，既然peeking引起了诸多问题。那么就通过禁用隐含参数“_optim_peek_user_binds”的方法，禁止使用peeking。随之带来的就是执行计划生成依据的缺失。这种情况下，Oracle更倾向于走索引路径和嵌套循环路径来生成执行计划。此时要想控制执行计划就只能通过outline或者hint来实现；
绑定变量在OLTP系统中，带来好处多多。但是随之而来的binds peeking问题，也是我们进行开发和日常运维不能忽视的问题现象。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-693827/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-693827/
947
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
