绑定变量之基本概念

 在oracle数据库对于一条SQL，可能存在硬解析、软解析以及软软解析。
  硬解析(Hard Parse)是指Oracle在执行目标SQL时，在库缓存找不到可以重用的解析树和执行计划，而不得不从头开始解析SQL并生成相应的Parent Cursor和Child Cursor的过程。硬解析不仅仅耗费CPU等硬件资源，更重要的是其会导致闩(Shared Pool Latch以及Library Cache Latch)等争用。大量的硬解析会严重影响系统的性能及其扩展性，尤其对于OLTP系统而言。
  那么怎样才能降低OLTP应用系统硬解析的数量呢？-----绑定变量。绑定变量的实质就是用一种特殊类型的变量后者称之为占位符替代SQL语句中的动态部分，从而保证每次提交的语句都一样。Oracle数据库中绑定变量的语法规则为：":variable_name"。下面通过具体实验来说明绑定变量的具体用法。
 1.简单SELECT语句使用绑定变量
 执行不含任何绑定变量的SQL语句如下：

点击(此处)折叠或打开

1. select /*no_bind*/ * from emp where empno=7902;
   
2. ...省略输出内容
3. 
   
4. //查看执行计划
   
5. SELECT * FROM table(dbms_xplan.display_cursor(null,null,'advanced'));
6. PLAN_TABLE_OUTPUT
   
7. ----------------------------------------------------------------------------------------------------
   
8. SQL_ID 8qng5nrymtdc1, child number 0
   
9. -------------------------------------
   
10. select /*no_bind*/ * from emp where empno=7902
11. Plan hash value: 2949544139
12. --------------------------------------------------------------------------------------
    
13. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    
14. --------------------------------------------------------------------------------------
    
15. | 0 | SELECT STATEMENT | | | | 1 (100)| |
    
16. | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
    
17. |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
    
18. --------------------------------------------------------------------------------------
    
19. Predicate Information (identified by operation id):
    
20. ---------------------------------------------------
21.    2 - access("EMPNO"=7902)
    

1. select /*no_bind*/ * from emp where empno=7876;
   
2. 1. ...省略输出内容
   2. 
      
   3. //查看执行计划
3. select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
4. PLAN_TABLE_OUTPUT
   
5. ----------------------------------------------------------------------------------------------------
   
6. SQL_ID 0x1jqmhdwjg7p, child number 0
   
7. -------------------------------------
   
8. select /*no_bind*/ * from emp where empno=7876
9. Plan hash value: 2949544139
10. --------------------------------------------------------------------------------------
    
11. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    
12. --------------------------------------------------------------------------------------
    
13. | 0 | SELECT STATEMENT | | | | 1 (100)| |
    
14. | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
    
15. |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
    
16. --------------------------------------------------------------------------------------
17. Predicate Information (identified by operation id):
    
18. ---------------------------------------------------
19.    2 - access("EMPNO"=7876)

 查询v$sqlarea视图：

点击(此处)折叠或打开

1. SELECT t.SQL_TEXT,t.SQL_ID,t.VERSION_COUNT,t.EXECUTIONS FROM v$sqlarea t WHERE t.SQL_TEXT LIKE '%no_bind%' AND t.SQL_TEXT NOT LIKE '%v$sql%';
   
2. SQL_TEXT                                                     SQL_ID        VERSION_COUNT EXECUTIONS
   
3. ------------------------------------------------------------ ------------- ------------- ----------
   
4. select /*no_bind*/ * from emp where empno=7902               8qng5nrymtdc1  1            1
   
5. select /*no_bind*/ * from emp where empno=7876               0x1jqmhdwjg7p  1            1

两条语句唯一的区别在于WHERE条件不同，且可以看到两条语句的 执行计划都是索引范围扫描。仅仅由于SQL文本的细微差别，导致数据库不得不进行两次硬解析，占用了系统的宝贵资源。
我们尝试用绑定变量重复上面的工作，看一下硬解析的次数以及效果。

点击(此处)折叠或打开

1. var x number;
   
2. exec :x := 7902;
   
3. select /*bind*/ * from emp where empno=:x;
   
4. ...省略输出内容
   
5. exec :x :=7876;
   
6. select /*bind*/ * from emp where empno=:x;
   
7. ...省略输出内容
   
8. 
   
9. SELECT t.SQL_TEXT,t.SQL_ID,t.VERSION_COUNT,t.EXECUTIONS FROM v$sqlarea t WHERE t.SQL_TEXT LIKE '%bind%' AND t.SQL_TEXT NOT LIKE '%v$sql%';
   
10. SQL_TEXT                                                     SQL_ID        VERSION_COUNT EXECUTIONS
    
11. ------------------------------------------------------------ ------------- ------------- ----------
    
12. select /*bind*/ * from emp where empno=:x                    5cnzfrvqdqccg  1            2
    
13. 
    
14. SELECT t.SQL_TEXT,t.SQL_ID,t.CHILD_NUMBER,t.EXECUTIONS FROM v$sql t WHERE t.SQL_TEXT LIKE '%bind%' AND t.SQL_TEXT NOT LIKE '%v$sql%';
    
15. SQL_TEXT                                                     SQL_ID        CHILD_NUMBER EXECUTIONS
    
16. ------------------------------------------------------------ ------------- ------------ ----------
    
17. select /*bind*/ * from emp where empno=:x                    5cnzfrvqdqccg 0            2

VERSION_COUNT=1,说明两条语句只进行了一次硬解析（从v$sql输出结果也印证了这一点），并执行了EXECUTIONS=2次。相比前面未使用绑定变量的情况，使用绑定变量减少了硬解析的次数。

2.存储过程使用绑定变量
  分别创建两个PROCEDURE，两个存储过程的作用一样都是向表插入10万条数据，唯一不同的是 其中PROC1未使用变量，PROC2使用绑定变量。

点击(此处)折叠或打开

1. CREATE TABLE T (A INT);
   
2. //未使用绑定变量
3. CREATE OR REPLACE PROCEDURE PROC1
   
4. AS
   
5. BEGIN
   
6.   FOR i IN 1 .. 100000
   
7.   LOOP
   
8.   EXECUTE IMMEDIATE 'INSERT INTO T VALUES('||i|| ')' ;
   
9.   END LOOP ;
   
10. END ;
    
11. //使用绑定变量
12. CREATE OR REPLACE PROCEDURE PROC2
    
13. AS
    
14. BEGIN
    
15.   FOR i IN 1 .. 100000
    
16.   LOOP
    
17.   EXECUTE IMMEDIATE 'INSERT INTO T VALUES(:X)' USING i;
    
18.   END LOOP ;
    
19. END ;

下面具体看一下硬解析次数以及执行时间:
proc1的执行情况如下：

点击(此处)折叠或打开

1. select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
2. NAME                                                              VALUE
   
3. ---------------------------------------------------------------- ----------
   
4. parse count (total)                                               505
   
5. parse count (hard)                                                312
   
6. parse count (failures)                                            4
   
7. parse count (describe)                                            0
   
8. 
   
9. exec proc1;
   
10. Elapsed: 00:01:23.09
    
11. 
    
12. select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
13. NAME                                                              VALUE
    
14. ---------------------------------------------------------------- ----------
    
15. parse count (total)                                               100515
    
16. parse count (hard)                                                100313
    
17. parse count (failures)                                            4
    
18. parse count (describe)                                            0

执行前后硬解析次数增加了10万次左右( 100313-312=100001),每一次 insert  都进行一次硬解析。同时注意到执行时间为1分钟23秒。
proc2的执行情况：

点击(此处)折叠或打开

1. select name,value from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
2. NAME                                                             VALUE
   
3. ---------------------------------------------------------------- ----------
   
4. parse count (total)                                              100570
   
5. parse count (hard)                                               100345
   
6. parse count (failures)                                           4
   
7. parse count (describe)                                           0
8. Elapsed: 00:00:00.01
   
9. 
   
10. SQL> exec proc2;
11. PL/SQL procedure successfully completed.
12. Elapsed: 00:00:11.02
    
13. 
    
14. select name,value from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
15. NAME                                                             VALUE
    
16. ---------------------------------------------------------------- ----------
    
17. parse count (total)                                              100573
    
18. parse count (hard)                                               100347
    
19. parse count (failures)                                           4
    
20. parse count (describe)                                           0

执行前后 硬解析次数仅增加2次(100347-100345)，执行时间仅用了11秒。
看到PROC1的执行时间几乎是PROC2执行时间的8倍。

3.JAVA语言使用绑定变量的情况
java PrepareStatement对象，可以将sql语句做预编译操作，被封装的sql语句可以包含动态参数，减少编译的次数，提高数据库性能.PrepareStatement的具体使用方法如下：

点击(此处)折叠或打开

1. for (int i =1 ;i<=1000;i++ ) {
   
2. v_sql ="select object_name from objects where object_id= :x ";
   
3. stmt=conn.prepareStatement(v_sql);
   
4. stmt.setString(1,Integer.toString(i));
   
5. rset = stmt.executeQuery();
   
6. stmt.close();
   
7. }

【总结】
TOM曾说过：Oracle将已解析、已编译的SQL连同其他内容存储在共享池(shared pool)中，这个是系统全局区(System Golbal Area,SGA)中一个非常重要的共享内存结构。如果你确实想让Oracle缓慢运行，甚至几近停顿，只要根本不使用绑定变量就可以办到，足以见绑定变量的重要性。






  
 
 

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

转载于:http://blog.itpub.net/29827284/viewspace-2138106/