ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 绑定变量之绑定变量窥探(Bind Peeking)

绑定变量之绑定变量窥探(Bind Peeking)

原创 Oracle 作者：13811135929 时间：2017-05-03 09:52:09 0 删除 编辑

绑定变量是Oracle解决硬解析的首要利器，能解决OLTP系统中共享池的过度耗用以提高系统性能。对于使用了绑定变量的SQL而言，具体有哪些因素决定其执行计划？
     1.绑定变量窥探
     2.若未启用绑定变量窥探，则对于哪些可选择率会随着具体输入值的不同而不同的谓词条件会使用默认的选择率。
     具体数据库是否启用绑定变量窥探(Bind Peeking)由隐含参数_OPTIM_PEEK_USER_BINDS决定，在9i版本之后该参数值默认为TRUE，代表绑定窥探已经启用了。
     
     启用绑定变量窥探，Oracle会在第一次硬解析时根据WHERE条件计算出SELECTIVITY以及CARDINALITY，最大限度的得到SQL的准确执行计划。但其最大问题在于，不管以后绑定变量传入何值，它会一直沿用之前硬解析时产生的解析树和执行计划(针对于11g之前的版本，11g引入了ACS该问题得到了改善)。该问题可能会导致CBO在某些情况下所选择的执行计划并不是最优执行计划，从而极大的影响了系统的性能。
    操作环境：

点击(此处)折叠或打开

  1. SELECT * FROM v$version;
  2. BANNER
  3. ------------------------------------------------------------------------------------------------------------------------------------------------------
  4. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
  5. PL/SQL Release 10.2.0.1.0 - Production
  6. CORE 10.2.0.1.0 Production
  7. TNS for Linux: Version 10.2.0.1.0 - Production
  8. NLSRTL Version 10.2.0.1.0 - Production

准备测试表：

点击(此处)折叠或打开

  1. create table t as select * from dba_objects;
  2. create index ind_obj_type on t(OBJECT_TYPE);

  3.
  4. update t set OBJECT_TYPE='VIEW' WHERE rownum<50598;
  5. select OBJECT_TYPE,count(*) FROM t group by OBJECT_TYPE;
  6. OBJECT_TYPE                                               COUNT(*)
  7. --------------------------------------------------------- ----------
  8. INDEX PARTITION                                           2
  9. TABLE PARTITION                                           2
  10. VIEW                                                      50597

  12. exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T',estimate_percent=>100,cascade=>TRUE,method_opt=>'FOR ALL COLUMNS');

    可以看到在OBJECT_TYPE列存在着严重的倾斜，由于该列收集了直方图信息，因此在不使用绑定变量的情况下OBJECT_TYPE='INDEX PARTITION'和INDEX PARTITION='VIEW'可能会走不同的执行计划。具体实验如下：

点击(此处)折叠或打开

  1. select count(*) from t where object_type='INDEX PARTITION';
  2.   COUNT(*)
  3. ----------
  4.          2

  6. select count(*) from t where object_type='VIEW';
  7.   COUNT(*)
  8. ----------
  9.      50597

  11. select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'select count(*) from t%';
  12. SQL_TEXT                                                     SQL_ID                                  VERSION_COUNT
  13. ------------------------------------------------------------ --------------------------------------- -------------
  14. select count(*) from t where object_type='VIEW'              1615uttr9arqc                           1
  15. select count(*) from t where object_type='INDEX PARTITION'   7hyfwxx1r7pkw                           1


  18. select * from table(dbms_xplan.display_cursor('7hyfwxx1r7pkw',0,'advanced'));
  19. PLAN_TABLE_OUTPUT
  20. ------------------------------------------------------------------------------------------------------------------------------------------------------
  21. SQL_ID 7hyfwxx1r7pkw, child number 0
  22. -------------------------------------
  23. select count(*) from t where object_type='INDEX PARTITION'
  24. Plan hash value: 3381337503
  25. -----------------------------------------------------------------------------------------------
  26. | Id | Operation                     | Name            | Rows | Bytes | Cost (%CPU) | Time     |
  27. -----------------------------------------------------------------------------------------------
  28. | 0  | SELECT STATEMENT              |                 |       |      |1 (100)      |          |
  29. | 1  | SORT AGGREGATE                |                 | 1     | 6    |             |          |
  30. |* 2 | INDEX RANGE SCAN              | IND_OBJ_TYPE    | 2     | 12   | 1 (0)       | 00:00:01 |
  31. -----------------------------------------------------------------------------------------------

  33. select * from table(dbms_xplan.display_cursor('1615uttr9arqc',0,'advanced'));
  34. PLAN_TABLE_OUTPUT
  35. ------------------------------------------------------------------------------------------------------------------------------------------------------
  36. SQL_ID 1615uttr9arqc, child number 0
  37. -------------------------------------
  38. select count(*) from t where object_type='VIEW'
  39. Plan hash value: 1419492071
  40. -----------------------------------------------------------------------------------------------
  41. | Id| Operation                    | Name              | Rows | Bytes | Cost (%CPU)| Time     |
  42. -----------------------------------------------------------------------------------------------
  43. | 0 | SELECT STATEMENT             |                   |      |       | 27 (100)   |          |
  44. | 1 | SORT AGGREGATE               |                   | 1    | 6     |            |          |
  45. |* 2| INDEX FAST FULL SCAN         | IND_OBJ_TYPE      | 50597| 296K  | 27 (4)     | 00:00:01 |
  46. -----------------------------------------------------------------------------------------------

可以看到在不使用绑定变量的情况下，object_type='INDEX PARTITION'走的INDEX RANGE SCAN索引范围扫描，object_type='VIEW'走的INDEX FAST FULL SCAN索引快速全扫描，执行计划与实际数据的分布情况相符。
   现在我们将前面的两条语句改造成使用绑定变量的等价形式。
     

点击(此处)折叠或打开

  1. var v varchar2(30);
  2. exec :v:='INDEX PARTITION';
  3. select count(*) from t where object_type=:v;
  4.   COUNT(*)
  5. ----------
  6.          2

  8. select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t%';
  9. SQL_TEXT                                                     SQL_ID                                  VERSION_COUNT          EXECUTIONS
  10. ------------------------------------------------------------ --------------------------------------- -------------          -----------
  11. select count(*) from t where object_type=:v                  2mbcpzxk94xyg                            1                     1

  13. select * from table(dbms_xplan.display_cursor('2mbcpzxk94xyg',0,'advanced'));
  14. PLAN_TABLE_OUTPUT
  15. ------------------------------------------------------------------------------------------------------------------------------------------------------
  16. SQL_ID 2mbcpzxk94xyg, child number 0
  17. -------------------------------------
  18. select count(*) from t where object_type=:v
  19. Plan hash value: 3381337503
  20. -----------------------------------------------------------------------------------------------
  21. | Id | Operation                   | Name            | Rows | Bytes | Cost (%CPU)| Time       |
  22. -----------------------------------------------------------------------------------------------
  23. | 0 | SELECT STATEMENT             |                 |      |       | 1 (100)    |            |
  24. | 1 | SORT AGGREGATE               |                 | 1    | 6     |            |            |
  25. |* 2 | INDEX RANGE SCAN            | IND_OBJ_TYPE    | 2    | 12    | 1 (0)      | 00:00:01   |
  26. -----------------------------------------------------------------------------------------------
  27. Peeked Binds (identified by position):
  28. --------------------------------------
  29.    1 - :V (VARCHAR2(30), CSID=31): 'INDEX PARTITION'

  31. exec :v :='VIEW';
  32. select count(*) from t where object_type=:v;
  33.   COUNT(*)
  34. ----------
  35.      50597

  37. select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t%';
  38. SQL_TEXT                                                     SQL_ID                                  VERSION_COUNT          EXECUTIONS
  39. ------------------------------------------------------------ --------------------------------------- -------------          -----------select count(*) from t where object_type=:v                  2mbcpzxk94xyg                            1                     2
  41. select * from table(dbms_xplan.display_cursor('2mbcpzxk94xyg',0,'advanced'));
  42. PLAN_TABLE_OUTPUT
  43. ------------------------------------------------------------------------------------------------------------------------------------------------------
  44. SQL_ID 2mbcpzxk94xyg, child number 0
  45. -------------------------------------
  46. select count(*) from t where object_type=:v
  47. Plan hash value: 3381337503
  48. ----------------------------------------------------------------------------------------------
  49. | Id | Operation                  | Name              | Rows | Bytes | Cost (%CPU)| Time     |
  50. ----------------------------------------------------------------------------------------------
  51. | 0 | SELECT STATEMENT            |                   |      |       | 1 (100)    |          |
  52. | 1 | SORT AGGREGATE              |                   | 1    | 6     |            |          |
  53. |* 2 | INDEX RANGE SCAN           | IND_OBJ_TYPE      | 2    | 12    | 1 (0)      | 00:00:01 |
  54. ----------------------------------------------------------------------------------------------
  55. Peeked Binds (identified by position):
  56. --------------------------------------
  57.    1 - :V (VARCHAR2(30), CSID=31): 'INDEX PARTITION'
   从上述显示内容可以看到，select count(*) from t where object_type=‘VIEW’本应该走索引快速全扫描，但由于使用了绑定变量，这条SQL的解析为软解析，此执行计划会沿用第一次硬解析的结果：走的是索引范围扫描，并且Peeked Binds部分的内容依然为'INDEX PARTITION'。
  下面在做同样的实验，只不过把顺序调整一下，先执行exec :v:='VIEW',然后再执行exec :v:='INDEX PARTITION'， 看看具体效果。
  为避免重用之前的解析树以及执行计划，首先刷新一遍共享池(此步骤尽量不要在生产环境尝试)
  alter system flush shared_pool;

点击(此处)折叠或打开

  1. var v varchar2(30);
  2. exec :v:='VIEW';
  3. select count(*) from t where object_type=:v;
  4.   COUNT(*)
  5. ----------
  6.      50597

  8. select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t%';
  9. SQL_TEXT                                                     SQL_ID                                  VERSION_COUNT EXECUTIONS
  10. ------------------------------------------------------------ --------------------------------------- ------------- ----------
  11. select count(*) from t where object_type=:v                  2mbcpzxk94xyg                           1             1

  13. select * from table(dbms_xplan.display_cursor('2mbcpzxk94xyg',0,'advanced'));
  14. PLAN_TABLE_OUTPUT
  15. ---------------------------------------------------------------------------------------------------------------------------------
  16. SQL_ID 2mbcpzxk94xyg, child number 0
  17. -------------------------------------
  18. select count(*) from t where object_type=:v
  19. Plan hash value: 1419492071
  20. ----------------------------------------------------------------------------------------
  21. | Id | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
  22. ----------------------------------------------------------------------------------------
  23. | 0 | SELECT STATEMENT        |                |       |       | 27 (100)   |          |
  24. | 1 | SORT AGGREGATE          |                | 1     | 6     |            |          |
  25. |* 2 | INDEX FAST FULL SCAN   | IND_OBJ_TYPE   | 50597 | 296K  | 27 (4)     | 00:00:01 |
  26. ----------------------------------------------------------------------------------------
  27. Peeked Binds (identified by position):
  28. --------------------------------------
  29.    1 - :V (VARCHAR2(30), CSID=31): 'VIEW'

  31. exec :v:='INDEX PARTITION';
  32. select count(*) from t where object_type=:v;
  33. select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t%';
  34. SQL_TEXT                                                     SQL_ID                                  VERSION_COUNT EXECUTIONS
  35. ------------------------------------------------------------ --------------------------------------- ------------- ----------
  36. select count(*) from t where object_type=:v                  2mbcpzxk94xyg                           1              2

  38. select * from table(dbms_xplan.display_cursor('2mbcpzxk94xyg',0,'advanced'));
  39. PLAN_TABLE_OUTPUT
  40. ------------------------------------------------------------------------------------------------------------------------------
  41. SQL_ID 2mbcpzxk94xyg, child number 0
  42. -------------------------------------
  43. select count(*) from t where object_type=:v
  44. Plan hash value: 1419492071
  45. -----------------------------------------------------------------------------------------
  46. | Id | Operation             | Name           | Rows   | Bytes  | Cost (%CPU)| Time     |
  47. -----------------------------------------------------------------------------------------
  48. | 0 | SELECT STATEMENT       |                |        |        | 27 (100)   |          |
  49. | 1 | SORT AGGREGATE         |                | 1      | 6      |            |          |
  50. |* 2 | INDEX FAST FULL SCAN  | IND_OBJ_TYPE   | 16867  | 98K    | 27 (4)     | 00:00:01 |
  51. -------------------------------------------------------------------------------------------
    从上述显示内容可以看到，select count(*) from t where object_type=‘INDEX PARTITION’走索引范围扫描才是最好的执行计划，但由于使用了绑定变量，这条SQL的解析为软解析，此执行计划会沿用第一次硬解析的结果：走的是索引快速全扫描。
   通过以上的两个实验验证了一个问题：当SQL语句中有绑定变量，不管以后绑定变量传入何值，它会一直沿用之前硬解析时产生的解析树和执行计划.
   有没有办法让上述使用绑定变量改写后的SQL语句再次执行时，选择走索引快速全扫描呢?有，让Oracle再次执行硬解析就可以，通常做法为对SQL涉及的表执行DDL操作，因为一旦某表执行了DDL操作，库缓存中所有SQL文本中包含了这个表的Cursor都会被Oracle标记为失效，Oracle再次执行与该表相关的SQL就会使用硬解析。最简单的DDL操作就是添加注释的COMMENT语句：
  comment on table t is 'Bind Peeking Test';
  再次执行

点击(此处)折叠或打开

  1. exec :v:='INDEX PARTITION';
  2. select count(*) from t where object_type=:v;
  3. select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t%';
  4. SQL_TEXT                                                      SQL_ID                                  VERSION_COUNT EXECUTIONS
  5. ------------------------------------------------------------ --------------------------------------- ------------- ----------
  6. select count(*) from t where object_type=:v                  2mbcpzxk94xyg                            1              1

  8. select * from table(dbms_xplan.display_cursor('2mbcpzxk94xyg',0,'advanced'));
  9. PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  2mbcpzxk94xyg, child number 0
    -------------------------------------
    select count(*) from t where object_type=:v
    Plan hash value: 3381337503

    ----------------------------------------------------------------------------------
    | Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |              |       |       |     1 (100)|          |
    |   1 |  SORT AGGREGATE   |              |     1 |     6 |            |          |
    |*  2 |   INDEX RANGE SCAN| IND_OBJ_TYPE |     2 |    12 |     1   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------
    Peeked Binds (identified by position):
    --------------------------------------
       1 - :V (VARCHAR2(30), CSID=31): 'INDEX PARTITION'
看到了什么？当对SQL涉及的表进行DDL操作后，会重新进行硬解析，重新生成解析树以及执行计划。







           


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

0
0
上一篇： 绑定变量之基本概念
下一篇： 绑定变量之自适应游标共享(Adaptive Cursor Sharing)
请登录后发表评论 登录
全部评论
13811135929

注册时间：2014-08-25

  • 博文量
    28
  • 访问量
    70034

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593