Oracle绑定变量详解(原创)

绑定变量概述
oracle对SQL语句进行了概括和抽象，将SQL语句提炼为两部分，一部分是 SQL语句的静态部分，也就是SQL语句本身的关键词、所涉及的表名称以及表的列等。另一部分就是SQL语句的动态部分，也就是SQL语句中的值（即表里 的数据）。很明显的，整个数据库中所包含的对象数量是有限的，而其中所包含的数据则是无限的。而正是这无限的数据导致了SQL语句的千变万化，也就是说在 数据库运行的过程中，发生的所有SQL语句中，静态部分可以认为数量是有限的，而动态部分则是无限的。而实际上，动态部分对解析的影响相比静态部分对解析 的影响来说是微乎其微，也就是说通常情况下，对于相同的静态部分的SQL 语句来说，不同的动态部分所产生的解析结果（执行计划）基本都是一样的。这也就为oracle提高解析SQL语句的效率提供了方向。
oracle中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.
一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要闩（latch）资源，严重的影响系统规模扩大（即限制了系统的并发行）， 而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为闩是为了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。

当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。
当oracle在shared pool中查找相同的SQL语句的过程中，如果SQL语句使用了绑定变量（bind variable），那么就是比较SQL语句的静态部分，前面我们已经知道，静态部分是有限的，很容易就能够缓存在内存里，从而找到相同的SQL语句的概 率很高。如果没有使用绑定变量，则就是比较SQL语句的静态部分和动态部分，而动态部分的变化是无限的，因此这样的SQL语句很难被缓存在shared pool里。毕竟内存是有限的，不可能把所有的动态部分都缓存在shared pool里，即便能够缓存，管理这样一个无限大的shared pool也是不可能完成的任务。不使用绑定变量导致的直接结果就是，找到相同的SQL语句的概率很低，导致必须完整的解析SQL语句，也就导致消耗更多的资源。
事实上绑定变量只是起到占位的作用，同名的绑定变量并不意味着它们是相同的，在传递时要考虑的是传递的值与绑定变量出现顺序的对位，而不是绑定变量的名称。
注意：绑定变量不能当作嵌入的字符串来使用，只能当作语句中的变量来用。不能用绑定变量来代替表名、过程名、字段名等.
绑定变量的使用环境
由于在OLTP中，SQL语句大多是比较简单或者操作的结果集都很小。当一个表上创建了索引，那么这种极小结果集的操作使用索引最合适，并且几乎所有的SQL的执行计划的索引都会被选择，因为这种情况下，索引可能只需要扫描几个数据块就可以定位到数据，而全表扫描将会相当耗资源。 因此，这种情况下，即使每个用户的谓词条件不一样，执行计划也是一样的，就是都用索引来访问数据，基本不会出现全表扫描的情况。 在这种执行计划几乎唯一的情况下，使用绑定变量来代替谓词常量是合适的。
注意：如果系统中某些数据有倾斜的情况，对于这些数据的操作也不是于使用绑定变量，因为他会影响执行计划
在OLAP系统中，SQL的操作就复杂很多，OLAP数据库上大多数时候运行的一些报表SQL，这些SQL经常会用到聚合查询（如：group by），而且结果集也是非常庞大，在这种情况下，索引并不是必然的选择，甚至有时候全表扫描的性能会更优于索引，即使相同的SQL，如果谓词不同，执行计划都可能不同。 让Oracle对每条SQL做硬分析，确切的知道谓词条件的值，这对执行计划的选择至关重要，这样做的原因是为了得到一个最优的执行计划。 在OLAP系统中， 系统的资源基本上是用于做大的SQL查询 ， 和查询比起来SQL解析消耗的资源显得微不足道 ，SQL硬分析的代价是可以忽略的。 因此让Oracle确切地知道谓词的数值至关重要，它直接决定了SQL执行计划的选择，所以在 OLAP系统完全没有必要绑定变量，那样很可能带来负面影响，比如导致SQL选择错误的执行，这个代价有时是灾难性的。
Bind Peeking是Oracle 9i中引入的新特性，它的作用就是在SQL语句硬分析的时候，查看一下当前SQL谓词的值，以便生成最佳的执行计划。而在oracle 9i之前的版本中，Oracle只根据统计信息来做出执行计划。要注意的是，Bind Peeking只发生在硬分析的时候，即SQL被第一次执行的时候，之后的变量将不会在做peeking。我们可以看出，Bind peeking并不能最终解决不同谓词导致选择不同执行计划的问题，它只能让SQL第一次执行的时候，执行计划选择更加准确，并不能帮助OLAP系统解决绑定变量导致执行计划选择错误的问题。这也是OLAP不应该使用绑定变量的一个原因。
实验示例
SQL> create table bind_a(id number,name varchar2(20));
Table created.
SQL> begin
  2   for i in 1..10000 loop
  3   if i <= 9900 then
  4     insert into bind_a values(i,'A');
  5   else
  6     insert into bind_a values(i,'DBA');
  7   end if;
  8   commit;
  9   end loop;
 10* end;
 /
SQL> create index BIND_A_IDX ON BIND_A(NAME);                       
Index created.
SQL> select TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,NUM_BUCKETS,HISTOGRAM from DBA_TAB_COL_STATISTICS where table_name='BIND_A';
TABLE_NAME   COLUMN_N NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------ -------- ------------ ----------- --------------------
BIND_A       NAME                2           2 FREQUENCY
BIND_A       ID              10000          75 HEIGHT BALANCED
SQL> var t varchar2(10);
SQL> exec :t:='DBA';
SQL> select count(*) from bind_a where name= :t
  COUNT(*)
----------
       100
SQL>select sql_text,child_number,executions ,buffer_gets from v$sql where sql_text='select count(*) from bind_a where name= :t'
SQL_TEXT                                                    CHILD_NUMBER EXECUTIONS BUFFER_GETS
--------------------------------------------------------- ------------ ---------- -----------
select count(*) from bind_a where name= :t                0         1                 3         
CHILD_NUMBER为0,执行了1次
SQL> select count(*) from bind_a where name= :t;
  COUNT(*)
----------
       100
SQL> select sql_text,child_number,executions ,buffer_gets from v$sql where sql_text='select count(*) from bind_a where name= :t'
  2  ;

SQL_TEXT                                                  CHILD_NUMBER EXECUTIONS BUFFER_GETS
--------------------------------------------------------- ------------ ---------- -----------
select count(*) from bind_a where name= :t                           0          2           6
CHILD_NUMBER为0,执行了2次
SQL> explain plan for select count(*) from bind_a where name= :t;
Explained.
SQL> select * from table(dbms_xplan.display)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1309236542
------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |            |     1 |     3 |            |          |
|*  2 |   INDEX FAST FULL SCAN| BIND_A_IDX |  5000 | 15000 |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
   2 - filter("NAME"=:T)
14 rows selected.
这里的由于DBA的值较少，故采用了 INDEX FAST FULL SCAN ，将name的值改为A
SQL> exec :t := 'A';
PL/SQL procedure successfully completed.
SQL> select count(*) from bind_a where name= :t;
  COUNT(*)
----------
      9900
SQL> select sql_text,child_number,executions ,buffer_gets from v$sql where sql_text='select count(*) from bind_a where name= :t';
SQL_TEXT                                                  CHILD_NUMBER EXECUTIONS BUFFER_GETS
--------------------------------------------------------- ------------ ---------- -----------
select count(*) from bind_a where name= :t                           0          3          25
--这个cursor执行3次，说明换了值之后，还是使用的这个cursor，child_number依旧为0。查看执行计划
SQL> explain plan for select count(*) from bind_a where name= :t;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1309236542
------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |     3 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |            |     1 |     3 |            |          |
|*  2 |   INDEX FAST FULL SCAN| BIND_A_IDX |  5000 | 15000 |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
   2 - filter("NAME"=:T)
14 rows selected.
可以看到，由于bind peeking只发生在硬解析时，在已经生产了执行计划的情况下，bind peeking并不会帮助sql产生最优的执行计划，所以在10g中，如果数据存在严重倾斜或者在OLAP系统中不建议采用绑定变量。

为了解决上述问题，Oracle11g中引入了adaptive cursor sharing( 适应性游标共享) 、Bind-Sensitive Cursors、 Bind-Aware Cursors、和Cursor Merging 的新功能。详细的信息可以参考笔者的如下博客

http://czmmiao.iteye.com/blog/1905855

11g中虽然对绑定变量的使用进行了优化，感觉更加智能，但依据笔者的经验，在OLAP系统下，由于系统语句重复率低，解析占用的资源相对少的特点，使用绑定变量的意义不大。同时为了避免CBO时不时的“犯傻"，建议在OLAP系统下少用，甚至不用绑定变量。而11g的这个特点可以说进一步加强了OLTP系统下绑定变量的使用性能。

 

参考至：《让Oracle跑得更快》谭怀远著
               http://blog.csdn.net/tianlesoftware/article/details/6591222
               http://www.dbanotes.net/database/top_bind_variables.html
               http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#PFGRF95174
               http://blog.csdn.net/tianlesoftware/article/details/6324243
               http://blog.csdn.net/tianlesoftware/article/details/5856430
本文原创，转载请注明出处、作者
如有错误，欢迎指正
邮箱:[e-mail]

评论

1 楼 xly931 2016-04-25  

请站在东南方向，受我一拜