ORACLE绑定变量BIND PEEKING

最新推荐文章于 2023-07-22 12:25:31 发布
最新推荐文章于 2023-07-22 12:25:31 发布
阅读量2.3k 收藏
点赞数 1
分类专栏： Oracle Tuning 文章标签： oracle bind peeking
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yidian815/article/details/17954133
版权

ORACLE 在9i之后引入了bind peeking，通过bind peeking，oracle可以在硬解析的时候窥探绑定变量的值，并根据当前绑定变量的值生成执行计划。在oracle 9i之前的版本中，oracle仅仅通过统计信息来生成执行计划。

下面看一下不同版本oracle下绑定变量对执行计划的影响

SQL> alter system flush shared_pool;

系统已更改。

SQL> alter system set optimizer_features_enable='8.1.7';

系统已更改。

SQL> var v number;
SQL> exec :v := 1;

PL/SQL 过程已成功完成。

SQL> select count(*) from acs_test_tab where record_type = :v;

  COUNT(*)
----------
	 1

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	3rg5r8sghcvb3, child number 0
-------------------------------------
select count(*) from acs_test_tab where record_type = :v

Plan hash value: 2956728990

--------------------------------------------------------------------------------
| Id  | Operation	  | Name		       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |			       |       |       |     3 |
|   1 |  SORT AGGREGATE   |			       |     1 |     4 |       |
|*  2 |   INDEX RANGE SCAN| ACS_TEST_TAB_RECORD_TYPE_I |     2 |     8 |     3 |
--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RECORD_TYPE"=:V)


已选择47行。

SQL> alter system flush shared_pool;

系统已更改。

SQL> alter system set optimizer_features_enable='11.2.0.3.1';

系统已更改。

SQL> var v number;
SQL> exec :v := 1;

PL/SQL 过程已成功完成。

SQL> select count(*) from acs_test_tab where record_type = :v;

  COUNT(*)
----------
	 1

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	3rg5r8sghcvb3, child number 0
-------------------------------------
select count(*) from acs_test_tab where record_type = :v

Plan hash value: 2956728990

------------------------------------------------------------------------------------------------
| Id  | Operation	  | Name		       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |			       |       |       |     3 (100)|	       |
|   1 |  SORT AGGREGATE   |			       |     1 |     4 |	    |	       |
|*  2 |   INDEX RANGE SCAN| ACS_TEST_TAB_RECORD_TYPE_I |     1 |     4 |     3	 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


Peeked Binds (identified by position):
--------------------------------------

   1 - :V (NUMBER): 1  --绑定变量窥探

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RECORD_TYPE"=:V)


已选择49行。

SQL> alter system flush shared_pool;

系统已更改。

SQL> exec :v := 2;

PL/SQL 过程已成功完成。

SQL> select count(*) from acs_test_tab where record_type = :v;

  COUNT(*)
----------
     50000

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	3rg5r8sghcvb3, child number 0
-------------------------------------
select count(*) from acs_test_tab where record_type = :v

Plan hash value: 2957754476

----------------------------------------------------------------------------------------------------
| Id  | Operation	      | Name			   | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | 			   |	   |	   |   136 (100)|	   |
|   1 |  SORT AGGREGATE       | 			   |	 1 |	 4 |		|	   |
|*  2 |   INDEX FAST FULL SCAN| ACS_TEST_TAB_RECORD_TYPE_I | 48031 |   187K|   136   (1)| 00:00:02 |
----------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :V (NUMBER): 2  --绑定变量窥探，绑定变量会影响最初硬解析的执行计划

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("RECORD_TYPE"=:V)


已选择49行。

使用绑定变量窥测的好处是：可以帮助优化器在第一次硬解析时选择最优的执行计划。但是同时这也是其弊端：在第一次硬解析后，后面发生的所有解析都会使用第一次硬解析生成的执行计划，如果数据的分布是均匀的，问题不大，如果数据分布式倾斜的，那么第一次硬解析生成的执行计划未必是最优的，甚至可能是非常糟糕的。例如： 

SQL> show parameter optimizer_feat

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable	     string	 11.2.0.3.1
SQL> alter system flush shared_pool;

系统已更改。

SQL> var v number;
SQL> exec :v := 2;

PL/SQL 过程已成功完成。

SQL> select count(*) from acs_test_tab where record_type = :v;

  COUNT(*)
----------
     50000

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	3rg5r8sghcvb3, child number 0
-------------------------------------
select count(*) from acs_test_tab where record_type = :v

Plan hash value: 2957754476

----------------------------------------------------------------------------------------------------
| Id  | Operation	      | Name			   | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | 			   |	   |	   |   136 (100)|	   |
|   1 |  SORT AGGREGATE       | 			   |	 1 |	 4 |		|	   |
|*  2 |   INDEX FAST FULL SCAN| ACS_TEST_TAB_RECORD_TYPE_I | 48031 |   187K|   136   (1)| 00:00:02 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("RECORD_TYPE"=:V)


已选择19行。

SQL> exec :v := 1

PL/SQL 过程已成功完成。

SQL> select count(*) from acs_test_tab where record_type = :v;

  COUNT(*)
----------
	 1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	3rg5r8sghcvb3, child number 0
-------------------------------------
select count(*) from acs_test_tab where record_type = :v

Plan hash value: 2957754476

----------------------------------------------------------------------------------------------------
| Id  | Operation	      | Name			   | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | 			   |	   |	   |   136 (100)|	   |
|   1 |  SORT AGGREGATE       | 			   |	 1 |	 4 |		|	   |
|*  2 |   INDEX FAST FULL SCAN| ACS_TEST_TAB_RECORD_TYPE_I | 48031 |   187K|   136   (1)| 00:00:02 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("RECORD_TYPE"=:V)


已选择19行。

SQL> select count(*) from acs_test_tab where record_type = 1;

  COUNT(*)
----------
	 1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	1pxm87f6yd0bp, child number 0
-------------------------------------
select count(*) from acs_test_tab where record_type = 1

Plan hash value: 2956728990

------------------------------------------------------------------------------------------------
| Id  | Operation	  | Name		       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |			       |       |       |     3 (100)|	       |
|   1 |  SORT AGGREGATE   |			       |     1 |     4 |	    |	       |
|*  2 |   INDEX RANGE SCAN| ACS_TEST_TAB_RECORD_TYPE_I |     1 |     4 |     3	 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RECORD_TYPE"=1)


已选择19行。

对于变量v的取值为1的执行计划和采用常量1的执行计划性能差距还是比较大的。


总结：oracle在9i后引入变量窥测技术，该技术对于数据分布均匀的数据是非常合适的，但是对于分布倾斜的数据或者在OLAP系统中是不建议使用的。


ezbit
关注
专栏目录
Oracle数据倾斜问题,Oracle数据倾斜导致的问题-有绑定变量
weixin_34957608的博客
04-03 1336
Oracle数据倾斜导致的问题-有绑定变量参考整理---<<恩墨年货-SQL与性能优化>>场景二：在上一节实验可以知道，没有绑定变量时，数据倾斜问题在特定场景下可以用直方图解决，那么在有绑定变量情况下，数据倾斜问题单凭直方图可以解决吗？显然是不能的，Oracle绑定变量技术解决了SQL语句硬解析过多的问题，降低了资源的争用。但是绑定变量在引入cursor sharing，增...
绑定变量及其相关特性分析
blockheading的博客
02-22 529
--绑定变量分析 一、理论分析 1.优势 绑定变量在性能方面的优势是他们允许共享库缓存中的父游标，这样就避免了硬解析以及相关的额外开销。 2.劣势 在某些条件下会对查询优化器隐藏重要的信息。使用字面值时，查询优化器总能够做出最接近的估算。 二、实验 1.绑定变量共享游标实验 2.绑定变量扫视（bind variable peeking）导致的劣势实验。 3.自适应游标共享（ada
oracle 的绑定变量
12-14
一．绑定变量 　　变量绑定是OLTP系统中一个非常值得关注的技术。良好的变量绑定会使OLTP系统数据库中的SQL 执行速度飞快，内存效率极高；不使用绑定变量可能会使OLTP 数据库不堪重负，资源被SQL解析严重耗尽，系统运行缓慢。 　　当一个用户与数据库建立连接后，会向数据库发出操作请求，即向数据库送过去SQL语句。Oracle 在接收到这些SQL后，会先对这个SQL做一个hash 函数运算，得到一个Hash值，然后到共享池中寻找是否有和这个hash 值匹配的SQL存在 。注意这里说的SQL既要求语句上的字符级别的完全相同，又要求涉及的对象也必须完全相同。如果找到了，Oracle将直接使用
Oracle 绑定变量
热门推荐
David Dai -- Focus on Oracle
10-16 1万+
oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行）， 而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为门闩是为了顺序访问以及修
oracle 绑定变量（bind variable）
xch的专栏
01-31 1525
 绑定变量 在oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行）， 而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为门闩是
Event 10053 执行计划 绑定变量 Bind peeking
David Dai -- Focus on Oracle
04-29 1万+
 相关的准备知识，请参考我的Blog：Oracle 跟踪事件 set eventhttp://blog.csdn.net/tianlesoftware/archive/2009/12/13/4977827.aspxOracle SQL的硬解析和软解析http://blog.csdn.net/tianlesoftware/archive/2010/04/08/54
oracle绑定变量使用方法总结
demonson的专栏
03-19 2908
在Oracle中，对于一个提交的sql语句，存在两种可选的解析过程，硬解析和软解析。一个硬解析需要经解析，制定执行路径，优化访问计划等步骤。硬解析不仅仅会耗费大量的cpu，更重要的是会占据重要的闩(latch)资源。唯一使得oracle能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是使用变量来代替sql语句中的常量。绑定变量能够使得每次提交的sql语句都完全一样。1. sqlplus...
浅谈oracle 数据库的绑定变量特性及应用
02-26
这时可以通过绑定变量提示（bind peeking）或重写SQL语句来解决。 - **避免过度使用绑定变量**：虽然绑定变量通常能提升性能，但过度使用可能导致解析缓存污染，反而影响性能。 4. **Oracle的绑定变量与SQL性能...
Oracle数据库绑定变量特性及应用
07-30
然而，值得注意的是，虽然绑定变量通常带来性能提升，但在某些特定情况下，如绑定变量幻象（Bind Variable Peeking）可能导致不理想的执行计划。这种情况发生在Oracle根据第一次提供的变量值选择执行计划，但后续的...
绑定变量导致执行计划不走索引
希望走上人生巅峰早日脱光的菜鸟
02-10 960
客户反应有个查询慢，看了一下没有走索引，以为很简单，结果发现和以前遇到的问题完全不一样，原来是bind peeking问题，最后只好建议客户用hint，以下是过程。 explain plan for select pcode as ID,        tjDate as riqi,        busiarea as dishiID,        a.agentno as q
Oracle绑定变量
CSDN1csdn1的博客
07-22 1083
变量绑定会使OLTP系统数据库中的SQL 执行速度飞快，内存效率极高；不使用绑定变量可能会使OLTP 数据库不堪重负，资源被SQL解析严重耗尽，系统运行缓慢。对OLTP和OLAP不了解的可以查看这篇文章：OLTP vs OLAP。首先理解一下为什么需要绑定变量？
ORACLE 绑定变量
sunyuzhou的专栏
12-05 390
绑定变量的意义： 在oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的们闩（latch）资源，严重的影响系统的规模的扩大（即限制了系统的并发行），而且引起的问题不能通过增加内存条和cpu的数量来解决。而软解析只做软分析即可，效率上有很大
oracle绑定变量
枫叶的专栏
08-10 340
一．绑定变量 bind variable： A variable in a SQL statement that must be replaced with a valid value, or the address of a value, in order for the statement to successfully execute. 变量绑定是OLTP系统中一个非常值得关注的技术。良好的
在JAVA 源程序中编写SQL语句时使用ORACLE 绑定变量
Stone的专栏
10-19 6651
在JAVA 源程序中编写SQL语句时使用ORACLE 绑定变量( bind variable )在JAVA中的SQL 语句的编写方面，没有使用ORACLE 绑定变量，很大程度上降低了数据库的性能，表现在两个方面：1、SQL语句硬分析(Hard Parse)太多，严重消耗CPU资源，延长了SQL语句总的执行时间SQL语句的执行过程分几个步骤：语法检查、分析、执行、返回结果。其中分析又分为硬分析(Ha
oracle 绑定变量
wuhen666
09-11 674
绑定变量的本质是将硬解析变成软解析，减少sql解析时的资源消耗及latch争用。 绑定变量将一个变量代替常量，使oracle在对sql做hash运算时得到同一个值。 e.g select * from user_tables where table_name='wuhen'; 使用绑定变量： select * from user_tables where table_name=:i
bind peeking
事后诸葛亮的程序人生（微信：zq9017197）
06-21 1675
光有统计信息是不够的，变量的值不同，可能采用的执行计划也不同，所以Oracle引入了bind peeking这个技术。但需要强调的是，即使是bind peeking，也只是发生在硬分析的时候。 SQL> drop table t purge; 表已删除。 SQL> create table t as select 1 id,a.* from all_objects a; 表已创建。 SQ
Oracle 中bind peeking的处理
cuihuang1302的博客
01-18 133
对于Oracle10g之前的版本，使用绑定变量的缺点就是容易产出bind peeking，但出现这种情况时，最好把使用绑定变量的SQL语句修改为硬解析的方式。 对于问题系统的临时解决方案，参考老白的处理办法： alte...
oracle 查看绑定变量
最新发布
08-12
1. 使用Oracle的动态视图V$SQL_BIND_CAPTURE：这个视图可以显示SQL语句中绑定变量的名称、类型和值。通过查询V$SQL_BIND_CAPTURE视图，可以获取绑定变量的详细信息，包括它们在SQL语句中的位置和具体的值。 2. 使用...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值