脚本宝典收集整理的这篇文章主要介绍了关于绑定变量窥探，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。
1.由于绑定变量窥探特性，对于后续生成的执行计划，不仅套用了首次生成的执行计划，而且执行计划中的Row,Bytes,Cost(%CPU)等都与首次生存执行计划得值相同。尽管可以使用绑定变量解决OLTP系统中大量重复SQL的反复解析的问题。但绑定变量可能会导致SQL语句选择非最佳的执行计划。尤其是对于存在数据倾斜的列，且生成了直方图更不宜于使用绑定变量。在oracle 11g 中，自适应特性从一定程度解决了绑定变量窥探所导致的问题。
2.10g和11g环境下绑定变量窥探测试，11g中说是采用了自适用游标，无论调整cursor_sharing，还是如下隐含参数，发现11g并没有改进，存在疑问。但是绑定变量窥探的这个问题进测试是存在的。
alter System set "_optimizer_extended_cursor_sharing_rel"=none scoPE=spfile sid='*';alter system set "_optimizer_extended_cursor_sharing"=none scope=spfile sid='*';alter system set "_optimizer_adaptive_cursor_sharing"=false scope=spfile sid='*';
这个参数是用来告诉Oracle在什么情况下可以共享游标，即SQL重用。 Cursor_sharing参数有3个值可以设置： 1)、EXACT：通常来说，exact值是Oracle推荐的，也是默认的，它要求SQL语句在完全相同时才会重用，否则会被重新执行硬解析操作。 2)、SIMILAR：similar是在Oracle认为某条SQL语句的谓词条件可能会影响到它的执行计划时，才会被重新分析，否则将重用SQL。 3)、FORCE：force是在任何情况下，无条件重用SQL。 备注：上面所说的SQL重用，仅仅是指谓词条件不同的SQL语句，实际上这样的SQL基本上都在执行同样的业务操作。 3、开启与关闭ACS默认情况下是开启的，为了关闭ACS需要修改下列参数alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=spfile sid='*';alter system set "_optimizer_extended_cursor_sharing"=none scope=spfile sid='*';alter system set "_optimizer_adaptive_cursor_sharing"=false scope=spfile sid='*';为了开启ACS需要确保下列参数有效alter system set "_optim_peek_user_binds" =true scope=spfile sid='*';alter system set "_optimizer_adaptive_cursor_sharing" =TRUE scope=spfile sid='*';alter system set "_optimizer_extended_cursor_sharing" =UDO scope=spfile sid='*';alter system set "_optimizer_extended_cursor_sharing_rel" =SIMPLE scope=spfile sid='*';
1、创建演示环境 SQL> select * From v$version where rownum<2; -->查看当前数据库版本 BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise EdITion Release 10.2.0.5.0 - 64bi conn SCOTT/SCOTT SQL> create table t(id,owner,object_id) as select rownum,owner,object_id from all_objects where rownum<=1000; SQL> alter table t add constraint t_pk Primary key(id); begin dbms_stats.gather_table_stats( ownname=>'SCOTT', tabname=>'T', estiMATE_percent=>100, method_opt=>'for all columns size 1'); end; / 2、未使用绑定变量情形下SQL语句的执行计划 查看值的分布情况 SQL> select count(id),count(distinct id),min(id),max(id) from t; COUNT(ID) COUNT(DISTINCTID) MIN(ID) MAX(ID) ---------- ----------------- ---------- ---------- 1000 1000 1 1000 SQL> select sum(object_id) from t where id<900; SUM(OBJECT_ID) -------------- 1513589 SQL> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID bz6h6fdsxgjka, child number 0 ------------------------------------- select sum(object_id) from t where id<900 Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"<900) 19 rows selected. SQL> SQL> set linesize 200 pagesize 2000 SQL> select sum(object_id) from t where id<10; SUM(OBJECT_ID) -------------- 3012 SQL> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 6y2280pyvaCFq, child number 0 ------------------------------------- select sum(object_id) from t where id<10 Plan hash value: 4270555908 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- PRedicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID"<10) 20 rows selected. 3、使用绑定变量情形下的执行计划 SQL> VARiable v_id number; SQL> exec :v_id:=900; PL/SQL procedure successfully completed. SQL> select sum(object_id) from t where id<:v_id; SUM(OBJECT_ID) -------------- 1513589 SQL> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 7qcp6urqh7d2j, child number 0 ------------------------------------- select sum(object_id) from t where id<:v_id Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"<:V_ID) 19 rows selected. SQL> exec :v_id:=10; PL/SQL procedure successfully completed. SQL> select sum(object_id) from t where id<:v_id; SUM(OBJECT_ID) -------------- 3012 SQL> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 7qcp6urqh7d2j, child number 0 ------------------------------------- select sum(object_id) from t where id<:v_id Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"<:V_ID) 19 rows selected. SQL> alter system flush shared_pool; System altered. SQL> print v_id; V_ID ---------- 10 SQL> select round(avg(object_id)) from t where id<:v_id; ROUND(AVG(OBJECT_ID)) --------------------- 335 SQL> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 0bx53mgt4QQnt, child number 0 ------------------------------------- select round(avg(object_id)) from t where id<:v_id Plan hash value: 4270555908 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID"<:V_ID) 20 rows selected. SQL> exec :v_id:=900; PL/SQL procedure successfully completed. SQL> select round(avg(object_id)) from t where id<:v_id; ROUND(AVG(OBJECT_ID)) --------------------- 1684 SQL> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 0bx53mgt4qqnt, child number 0 ------------------------------------- select round(avg(object_id)) from t where id<:v_id Plan hash value: 4270555908 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID"<:V_ID) 20 rows selected. SQL> drop table t purge; Table dropped.
1、创建演示环境 SQL> select * from v$version where rownum<2; -->查看当前数据库版本 BANNER ---------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production conn SCOTT/SCOTT SQL> create table t(id,owner,object_id) as -->创建测试表t select rownum,owner,object_id from all_objects where rownum<=1000; SQL> alter table t add constraint t_pk primary key(id); -->为表t添加主键 begin -->收集统计信息,此处未生成直方图信息 dbms_stats.gather_table_stats( ownname=>'SCOTT', tabname=>'T', estimate_percent=>100, method_opt=>'for all columns size 1'); end; / 22:43:14 SCOTT@zytrac1>select count(id),count(distinct id),min(id),max(id) from t; COUNT(ID) COUNT(DISTINCTID) MIN(ID) MAX(ID) ---------- ----------------- ---------- ---------- 1000 1000 1 1000 2、未使用绑定变量情形下SQL语句的执行计划 22:47:17 SCOTT@zytrac1>select sum(object_id) from t where id<900; SUM(OBJECT_ID) -------------- 451307 Elapsed: 00:00:00.00 22:47:27 SCOTT@zytrac1>select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID bz6h6fdsxgjka, child number 0 ------------------------------------- select sum(object_id) from t where id<900 Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"<900) 19 rows selected. Elapsed: 00:00:00.05 22:47:32 SCOTT@zytrac1>select sum(object_id) from t where id<10; SUM(OBJECT_ID) -------------- 261 Elapsed: 00:00:00.00 22:48:20 SCOTT@zytrac1>select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 6y2280pyvacfq, child number 0 ------------------------------------- select sum(object_id) from t where id<10 Plan hash value: 4270555908 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID"<10) 20 rows selected. Elapsed: 00:00:00.03 22:48:30 SCOTT@zytrac1>variable v_id number; 22:48:45 SCOTT@zytrac1>exec :v_id:=900; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 22:48:49 SCOTT@zytrac1>select sum(object_id) from t where id<:v_id; SUM(OBJECT_ID) -------------- 451307 Elapsed: 00:00:00.00 22:48:54 SCOTT@zytrac1>select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 7qcp6urqh7d2j, child number 0 ------------------------------------- select sum(object_id) from t where id<:v_id Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"<:V_ID) 19 rows selected. Elapsed: 00:00:00.04 22:49:01 SCOTT@zytrac1>exec :v_id:=10; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 22:49:09 SCOTT@zytrac1>select sum(object_id) from t where id<:v_id; SUM(OBJECT_ID) -------------- 261 Elapsed: 00:00:00.00 22:49:13 SCOTT@zytrac1>select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 7qcp6urqh7d2j, child number 0 ------------------------------------- select sum(object_id) from t where id<:v_id Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| T | 900 | 7200 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"<:V_ID) 19 rows selected. Elapsed: 00:00:00.04 22:49:19 SCOTT@zytrac1>alter system flush shared_pool; System altered. Elapsed: 00:00:00.24 22:49:36 SCOTT@zytrac1>print v_id; V_ID ---------- 10 22:49:41 SCOTT@zytrac1>select round(avg(object_id)) from t where id<:v_id; ROUND(AVG(OBJECT_ID)) --------------------- 29 Elapsed: 00:00:00.02 22:49:46 SCOTT@zytrac1>select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 0bx53mgt4qqnt, child number 0 ------------------------------------- select round(avg(object_id)) from t where id<:v_id Plan hash value: 4270555908 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID"<:V_ID) 20 rows selected. Elapsed: 00:00:00.55 22:49:54 SCOTT@zytrac1>exec :v_id:=900; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 22:50:09 SCOTT@zytrac1>select round(avg(object_id)) from t where id<:v_id; ROUND(AVG(OBJECT_ID)) --------------------- 502 Elapsed: 00:00:00.00 22:50:14 SCOTT@zytrac1> select * from table(dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 0bx53mgt4qqnt, child number 0 ------------------------------------- select round(avg(object_id)) from t where id<:v_id Plan hash value: 4270555908 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 9 | 72 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_PK | 9 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID"<:V_ID) 20 rows selected. Elapsed: 00:00:00.01 22:50:19 SCOTT@zytrac1> select sql_id,child_number, is_bind_sensitive, is_bind_aware, is_shareable from v$sql where sql_id in ('0bx53mgt4qqnt') order by child_number; SQL_ID CHILD_NUMBER IS IS IS -------------------------- ------------ -- -- -- 7qcp6urqh7d2j 0 N N Y
在Oracle 11g 以后在绑定变量这块有所以改变，会生成一个范围值的执行计划。然后每次传变量进去就对比范围，选择最优的执行计划。与这个功能相关的参数保存在v$sql视图中：is_bind_sensitive,is_bind_aware,is_shareable。这几个字段，在Oracle 10g的v$sql 视图里是没有的。 IS_BIND_SENSITIVE：指示游标是否为对绑定敏感，值为YES | NO。符合以下情况的查询称为对绑定敏感的查询：计算谓词选择性时优化程序为其扫视绑定变量值，并且绑定变量值的更改可能导致不同计划。 IS_BIND_AWARE：指示游标是否为能标识绑定的游标，值为YES | NO。游标高速缓存中已标记为使用能识别绑定的游标共享的游标称为能标识绑定的游标。
参考链接：https://blog.csdn.net/luis_ora/article/details/82109986https://bLOG.csdn.net/leshami/article/details/6923627https://www.cnblogs.COM/xibuhaohao/p/11325211.htMLhttps://www.cnblogs.com/Richardzhu/Archive/2013/01/21/2869837.html
以上是脚本宝典为你收集整理的关于绑定变量窥探全部内容，希望文章能够帮你解决关于绑定变量窥探所遇到的问题。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。