【SQL 调优】绑定变量窥测

最新推荐文章于 2022-05-20 11:00:00 发布

cojm55771

最新推荐文章于 2022-05-20 11:00:00 发布

阅读量172

点赞数

文章标签：数据库

绑定变量窥测：在物理优化阶段，查询优化器会窥测绑定变量的值，将它作为文本来使用。这种方法的问题是它生成的执行计划会依赖第一次生成执行计划时所提供的值。
--建立索引
SQL> create index i_obj_id on t_var_peek(object_id);

索引已创建。

已用时间: 00: 00: 00.25
---执行计划选择了 range scan
SQL> select count(object_id) from t_var_peek where object_id < 10;
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1952566611

------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     2   (0)| 00:00:01|
|   1 | SORT AGGREGATE   |          |     1 |    13 |            |         |
|* 2 |   INDEX RANGE SCAN| I_OBJ_ID |     8 |   104 |     2   (0)| 00:00:01|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"<10)
Note
   - dynamic sampling used for this statement
----使用绑定变量
SQL> exec :x1 :=999;
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.01
SQL> select count(object_id) from t_var_peek where object_id < :x1;
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 1952566611
------------------------------------------------------------------------------

| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     3   (0)| 00:00:01 |
|   1 | SORT AGGREGATE   |          |     1 |    13 |            |          |
|* 2 |   INDEX RANGE SCAN| I_OBJ_ID | 3439 | 44707 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"Note
   -----
   - dynamic sampling used for this statement

----此时返回的行数和cost 大小是和 :X1=999 的值是一样的。说明第一次优化是使用值999来执行的。结果，查询优化器就选择了INDEX RANGE SCAN。由于游标是共享的，因此是这个选择影响了第二次使用9作为条件的查询语句。
SQL> exec :x1 :=9;
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.01
SQL> select count(object_id) from t_var_peek where object_id < :x1;
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 1952566611
------------------------------------------------------------------------------

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | INDEX RANGE SCAN| I_OBJ_ID | 3439 | 44707 | 3 (0)| 00:00:01 |

------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"Note
-----
   - dynamic sampling used for this statement
---在使用 <9 时请注意执行计划的rows 和 bytes cost的值！
SQL> select count(object_id) from t_var_peek where object_id < 9;
已用时间: 00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 1952566611
------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     2   (0)| 00:00:01 |
|   1 | SORT AGGREGATE   |          |     1 |    13 |            |          |
|* 2 |   INDEX RANGE SCAN| I_OBJ_ID |     7 |    91 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------
    Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"<9)
Note
-----
   - dynamic sampling used for this statement
--此时：x1 的值还是 9 但是执行计划的和前面 :X1 =999 和 :X1=999 的执行计划是一样的，明显的发生了变量窥测！
SQL> select count(object_id) from t_var_peek where object_id < :x1;
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 1952566611
------------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     3   (0)| 00:00:01 |
|   1 | SORT AGGREGATE   |          |     1 |    13 |            |          |
|* 2 |   INDEX RANGE SCAN| I_OBJ_ID | 3439 | 44707 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

     Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"Note
-----
   - dynamic sampling used for this statement
   只要游标还保存在库缓存中并且可以被共享，就可以被重用。不管与它相关的执行计划的效率如何，这种事情都会发生。
解决办法：
   为了解决这个问题，Oracle11g中引入了一个称为扩展的游标共享（extended cursor sharing，也称为适应性游标共享，adaptive cursor sharing）的新功能。它的目的是在重用一个已经存在的但是会导致执行效率低下的游标时能够自动进行识别。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22664653/viewspace-673026/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22664653/viewspace-673026/