Oracle 绑定变量

在Oracle中,对于一个提交的sql语句,存在两种可选的解析过程,硬解析和软解析。

一个硬解析需要经解析,制定执行路径,优化访问计划等步骤。硬解析不仅仅会耗费大量的cpu,更重要的是会占据重要的闩(latch)资源。唯一使得oracle能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是使用变量来代替sql语句中的常量。绑定变量能够使得每次提交的sql语句都完全一样。


1. sqlplus中使用variable来定义

  1. SQL> select * from t where id=1;  
  2.   
  3.         ID NAME  
  4. ---------- --------------------------------  
  5.          1 test  
  6.   
  7. SQL> select * from t where id=2;  
  8.   
  9.         ID NAME  
  10. ---------- --------------------------------  
  11.          2 test2  
  12.   
  13. SQL> variable i number;  
  14. SQL> exec :i :=1;  
  15.   
  16. PL/SQL procedure successfully completed.  
  17.   
  18. SQL> select * from t where id=:i;  
  19.   
  20.         ID NAME  
  21. ---------- --------------------------------  
  22.          1 test  
  23.   
  24. SQL> exec :i :=2;  
  25.   
  26. PL/SQL procedure successfully completed.  
  27.   
  28. SQL> select * from t where id=:i;  
  29.   
  30.         ID NAME  
  31. ---------- --------------------------------  
  32.          2 test2  
  33. SQL> select sql_text,parse_calls from v$sql where sql_text like 'select * from t where id=%';  
  34.   
  35. SQL_TEXT  
  36. --------------------------------------------------------------------------------  
  37. PARSE_CALLS  
  38. -----------  
  39. select * from t where id=2  
  40.           1  
  41.   
  42. select * from t where id=1  
  43.           1  
  44.   
  45. select * from t where id=:i  --可以看到这条sql被调用了两次,这两次的使用就包括了一次soft parse  

2.  (误区)sqlplus中通过define定义的并不是变量,而只是字符常量,define定义之后,再通过&或&&引用的时候就不需要再输入了,oracle在执行的时候回自动用定义的值进行替换,仅此而已,并不是绑定变量。

  1. SQL> define a=1  
  2. SQL> define  
  3. DEFINE _DATE           = "30-OCT-16" (CHAR)  
  4. DEFINE _CONNECT_IDENTIFIER = "ORCL" (CHAR)  
  5. DEFINE _USER           = "SYS" (CHAR)  
  6. DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)  
  7. DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)  
  8. DEFINE _EDITOR         = "ed" (CHAR)  
  9. DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production  
  10. With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)  
  11. DEFINE _O_RELEASE      = "1102000400" (CHAR)  
  12. DEFINE A               = "1" (CHAR)  
  13. SQL> select * from t where id=&a;  
  14. old   1: select * from t where id=&a  
  15. new   1: select * from t where id=1  
  16.   
  17.         ID NAME  
  18. ---------- --------------------------------  
  19.          1 test  

&&和&一样的功能,不过&&替代过一次之后就不需要再输入了,可以多次替代。

  1. SQL> select * from t where id=&b;  
  2. Enter value for b: 2  
  3. old   1: select * from t where id=&b  
  4. new   1: select * from t where id=2  
  5.   
  6.         ID NAME  
  7. ---------- --------------------------------  
  8.          2 test2  
  9.   
  10. SQL> select * from t where id=&b;   
  11. Enter value for b: 2  
  12. old   1: select * from t where id=&b  
  13. new   1: select * from t where id=2  
  14.   
  15.         ID NAME  
  16. ---------- --------------------------------  
  17.          2 test2  
  18.   
  19. SQL> select * from t where id=&&b;  
  20. Enter value for b: 2  
  21. old   1: select * from t where id=&&b  
  22. new   1: select * from t where id=2  
  23.   
  24.         ID NAME  
  25. ---------- --------------------------------  
  26.          2 test2  
  27.   
  28. SQL> select * from t where id=&&b;  
  29. old   1: select * from t where id=&&b  
  30. new   1: select * from t where id=2  
  31.   
  32.         ID NAME  
  33. ---------- --------------------------------  
  34.          2 test2  

另外,如果define定义的是字符类型,在引用时需要加上单引号

  1. SQL> select * from t where name=&c;  
  2. old   1: select * from t where name=&c  
  3. new   1: select * from t where name=test  
  4. select * from t where name=test  
  5.                            *  
  6. ERROR at line 1:  
  7. ORA-00904: "TEST": invalid identifier  
  8.   
  9.   
  10. SQL> select * from t where name='&c';  
  11. old   1: select * from t where name='&c'  
  12. new   1: select * from t where name='test'  
  13.   
  14.         ID NAME  
  15. ---------- --------------------------------  
  16.          1 test  
可以看到,在执行sql的时候oracle自动进行了替换

  1. SQL> select sql_text from v$sql where sql_text like 'select * from t where name=%';  
  2.   
  3. SQL_TEXT  
  4. --------------------------------------------------------------------------------  
  5. select * from t where name='test'  

3. oracle在解析sql时会把plsql中定义的变量转为绑定变量

  1. SQL> create table tt(id int,name varchar2(10));  
  2.   
  3. Table created.  
  4.   
  5. SQL> alter session set sql_trace=true;  
  6.   
  7. Session altered.  
  8.   
  9. SQL> declare  
  10.   2  begin  
  11.   3  for i in 1 .. 100 loop  
  12.   4  insert into tt values(i,'test');  
  13.   5  end loop;  
  14.   6  commit;  
  15.   7  end;  
  16.   8  /  
  17.   
  18. PL/SQL procedure successfully completed.  
  19.   
  20. SQL> alter session set sql_trace=false;  
  21.   
  22. Session altered.  

trace文件内容:

  1. *** 2016-10-30 12:11:22.815  
  2. CLOSE #140170997623912:c=0,e=6,dep=0,type=0,tim=1477800682815427  
  3. =====================  
  4. PARSING IN CURSOR #140170997623912 len=92 dep=0 uid=0 oct=47 lid=0 tim=1477800682817922 hv=218581220 ad='8c89d9b0' sqlid='6pdgqjs6hfk74'  
  5. declare  
  6. begin  
  7. for i in 1 .. 100 loop  
  8. insert into tt values(i,'test');  
  9. end loop;  
  10. commit;  
  11. end;  
  12. END OF STMT  
  13. PARSE #140170997623912:c=1999,e=2431,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1477800682817921  
  14. =====================  
  15. PARSING IN CURSOR #140170996439488 len=34 dep=1 uid=0 oct=2 lid=0 tim=1477800682818383 hv=1299226876 ad='86bc23a8' sqlid='9j06ydd6r187w'  
  16. INSERT INTO TT VALUES(:B1 ,'test')  
  17. END OF STMT  

从硬解析的增长也可以看出:

  1. SQL> select a.*,b.name   
  2.   
  3.   2   from v$sesstat a , v$statname b  
  4.   3   where a.statistic#=b.statistic#  
  5.   4   and a.sid=(select distinct sid from v$mystat)  
  6.   5   and b.name like '%parse%';  
  7.   
  8. SQL> col name format a30  
  9. SQL> /  
  10.   
  11.        SID STATISTIC#      VALUE NAME  
  12. ---------- ---------- ---------- ------------------------------  
  13.         29        264          0 ADG parselock X get attempts  
  14.         29        265          0 ADG parselock X get successes  
  15.         29        622          4 parse time cpu  
  16.         29        623          8 parse time elapsed  
  17.         29        624        238 parse count (total)  
  18.         29        625        155 parse count (hard)  
  19.         29        626          0 parse count (failures)  
  20.         29        627          0 parse count (describe)  
  21.   
  22. rows selected.  

执行前的硬解析数为155

  1. SQL> declare  
  2.   2  begin  
  3.   3  for i in 1 .. 100 loop  
  4.   4  insert into tt values(i,'test');  
  5.   5  end loop;  
  6.   6  commit;  
  7.   7  end;  
  8.   8  /  
  9.   
  10. PL/SQL procedure successfully completed.  
  11.   
  12. SQL> select a.*,b.name  
  13.   2   from v$sesstat a , v$statname b  
  14.   3   where a.statistic#=b.statistic#  
  15.   4   and a.sid=(select distinct sid from v$mystat)  
  16.   5   and b.name like '%parse%';  
  17.   
  18.        SID STATISTIC#      VALUE NAME  
  19. ---------- ---------- ---------- ------------------------------  
  20.         29        264          0 ADG parselock X get attempts  
  21.         29        265          0 ADG parselock X get successes  
  22.         29        622          4 parse time cpu  
  23.         29        623          8 parse time elapsed  
  24.         29        624        242 parse count (total)  
  25.         29        625        157 parse count (hard)  
  26.         29        626          0 parse count (failures)  
  27.         29        627          0 parse count (describe)  
  28.   
  29. rows selected.  

执行后的为157,只增长了两个,如果不是使用了绑定变量,硬解析数绝对不止两个

4. 存储过程中的参数会自动转化为绑定变量

  1. SQL> create or replace procedure proc_test(p_id int,p_name varchar2)  
  2.   2  is  
  3.   3  begin  
  4.   4  insert into tt values(p_id,p_name);  
  5.   5  commit;  
  6.   6  end;  
  7.   7  /  
  8.   
  9. Procedure created.  
  10.   
  11. SQL> alter session set sql_trace=true;  
  12.   
  13. Session altered.  
  14.   
  15. SQL> exec proc_test(200,'test');  
  16.   
  17. PL/SQL procedure successfully completed.  
  18.   
  19. SQL> alter session set sql_trace=false;  
  20.   
  21. Session altered.  

trace文件内容:

  1. *** 2016-10-31 04:11:23.421  
  2. CLOSE #140585231805712:c=0,e=6,dep=0,type=0,tim=1477858283421964  
  3. =====================  
  4. PARSING IN CURSOR #140585231805712 len=35 dep=0 uid=0 oct=47 lid=0 tim=1477858283423073 hv=526484776 ad='86b57878' sqlid='asc6yd8gq3198'  
  5. BEGIN proc_test(200,'test'); END;  
  6. END OF STMT  
  7. PARSE #140585231805712:c=999,e=1047,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1477858283423072  
  8. =====================  
  9. PARSING IN CURSOR #140585233135112 len=32 dep=1 uid=0 oct=2 lid=0 tim=1477858283423304 hv=1422618771 ad='8697d9b8' sqlid='1yqc845acqw4m'  
  10. INSERT INTO TT VALUES(:B2 ,:B1 )  
  11. END OF STMT  
  12. PARSE #140585233135112:c=0,e=100,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1477858283423304  
其实从存储过程的调用过程也可以看出是使用了绑定变量

  1. begin  
  2. -- Call the procedure  
  3. proc_test(p_id => :p_id,  
  4. p_name => :p_name);  
  5. end;  

5. 动态sql中使用绑定变量

a. 直接使用游标中的值拼接

  1. [oracle@centos6 scripts]$ cat sql_parse1.sql  
  2. declare  
  3. cursor test_cur is select id,name from tt;  
  4. begin  
  5. for i in test_cur loop  
  6. execute immediate 'insert into tt values('||i.id||','||chr(39)||i.name||chr(39)||')';  
  7. end loop;  
  8. commit;  
  9. end;  
这样直接使用游标中的值拼接是属于非绑定变量,为硬解析

  1. SQL> alter system flush shared_pool;  
  2.   
  3. System altered.  
  4.   
  5. SQL> @sql_parse1.sql  
  6.   
  7. PL/SQL procedure successfully completed.  
  8.   
  9. SQL> set linesize 200  
  10. SQL> col hash_value format 9999999999  
  11. SQL> col sql_id format 99  
  12. SQL> col child_latch format 99  
  13. SQL> col version_count format 99  
  14. SQL> col sql_text format a40  
  15. SQL> col parse_calls format 999  
  16. SQL> select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like 'insert into tt%';  
  17.  HASH_VALUE SQL_ID        CHILD_LATCH VERSION_COUNT          SQL_TEXT                                           PARSE_CALLS  
  18. ----------- ------------- ----------- ------------- -------------------------------------------------- -----------  
  19.  3161064081 196c4s2y6n0nj           0             1 insert into tt values(45,'test')                             1  
  20.  3718124844 6hfpagbftw59c           0             1 insert into tt values(70,'test')                             1  
  21.  4046592725 c5txty7sm46qp           0             1 insert into tt values(28,'test')                             1  
  22.   961289967 4n0n3dnwns7rg           0             1 insert into tt values(30,'test')                             1  
  23.  2124685404 g70mmhxza882w           0             1 insert into tt values(26,'test')                             1  
  24.   608576974 3nm07v4k4c9ff           0             1 insert into tt values(31,'test')                             1  
  25.  3770952793 2xcry8ghc8b2t           0             1 insert into tt values(1,'test')                              1  

b. 绑定变量写法

  1. [oracle@centos6 scripts]$ cat sql_parse2.sql  
  2. declare  
  3. cursor test_cur is select id,name from tt;  
  4. begin  
  5. for i in test_cur loop  
  6. execute immediate 'insert into tt values(:a,:b)' using i.id,i.name;  
  7. end loop;  
  8. commit;  
  9. end;  
  10. /  
  11.   
  12. SQL> alter system flush shared_pool;  
  13.   
  14. System altered.  
  15.   
  16. SQL> @sql_parse2.sql  
  17.   
  18. PL/SQL procedure successfully completed.  
  19.   
  20. SQL> select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like 'insert into tt%';  
  21.   
  22.  HASH_VALUE SQL_ID        CHILD_LATCH VERSION_COUNT SQL_TEXT                                           PARSE_CALLS  
  23. ----------- ------------- ----------- ------------- -------------------------------------------------- -----------  
  24.  2034333845 gbkazctwn2y4p           0             1 insert into tt values(:a,:b)                                 1  


阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页