在Oracle中,对于一个提交的sql语句,存在两种可选的解析过程,硬解析和软解析。
一个硬解析需要经解析,制定执行路径,优化访问计划等步骤。硬解析不仅仅会耗费大量的cpu,更重要的是会占据重要的闩(latch)资源。唯一使得oracle能够重复利用执行计划的方法就是采用绑定变量。绑定变量的实质就是使用变量来代替sql语句中的常量。绑定变量能够使得每次提交的sql语句都完全一样。
1. sqlplus中使用variable来定义
- SQL> select * from t where id=1;
- ID NAME
- ---------- --------------------------------
- 1 test
- SQL> select * from t where id=2;
- ID NAME
- ---------- --------------------------------
- 2 test2
- SQL> variable i number;
- SQL> exec :i :=1;
- PL/SQL procedure successfully completed.
- SQL> select * from t where id=:i;
- ID NAME
- ---------- --------------------------------
- 1 test
- SQL> exec :i :=2;
- PL/SQL procedure successfully completed.
- SQL> select * from t where id=:i;
- ID NAME
- ---------- --------------------------------
- 2 test2
- SQL> select sql_text,parse_calls from v$sql where sql_text like 'select * from t where id=%';
- SQL_TEXT
- --------------------------------------------------------------------------------
- PARSE_CALLS
- -----------
- select * from t where id=2
- 1
- select * from t where id=1
- 1
- select * from t where id=:i --可以看到这条sql被调用了两次,这两次的使用就包括了一次soft parse
2. (误区)sqlplus中通过define定义的并不是变量,而只是字符常量,define定义之后,再通过&或&&引用的时候就不需要再输入了,oracle在执行的时候回自动用定义的值进行替换,仅此而已,并不是绑定变量。
- SQL> define a=1
- SQL> define
- DEFINE _DATE = "30-OCT-16" (CHAR)
- DEFINE _CONNECT_IDENTIFIER = "ORCL" (CHAR)
- DEFINE _USER = "SYS" (CHAR)
- DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
- DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)
- DEFINE _EDITOR = "ed" (CHAR)
- DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
- DEFINE _O_RELEASE = "1102000400" (CHAR)
- DEFINE A = "1" (CHAR)
- SQL> select * from t where id=&a;
- old 1: select * from t where id=&a
- new 1: select * from t where id=1
- ID NAME
- ---------- --------------------------------
- 1 test
&&和&一样的功能,不过&&替代过一次之后就不需要再输入了,可以多次替代。
- SQL> select * from t where id=&b;
- Enter value for b: 2
- old 1: select * from t where id=&b
- new 1: select * from t where id=2
- ID NAME
- ---------- --------------------------------
- 2 test2
- SQL> select * from t where id=&b;
- Enter value for b: 2
- old 1: select * from t where id=&b
- new 1: select * from t where id=2
- ID NAME
- ---------- --------------------------------
- 2 test2
- SQL> select * from t where id=&&b;
- Enter value for b: 2
- old 1: select * from t where id=&&b
- new 1: select * from t where id=2
- ID NAME
- ---------- --------------------------------
- 2 test2
- SQL> select * from t where id=&&b;
- old 1: select * from t where id=&&b
- new 1: select * from t where id=2
- ID NAME
- ---------- --------------------------------
- 2 test2
另外,如果define定义的是字符类型,在引用时需要加上单引号
- SQL> select * from t where name=&c;
- old 1: select * from t where name=&c
- new 1: select * from t where name=test
- select * from t where name=test
- *
- ERROR at line 1:
- ORA-00904: "TEST": invalid identifier
- SQL> select * from t where name='&c';
- old 1: select * from t where name='&c'
- new 1: select * from t where name='test'
- ID NAME
- ---------- --------------------------------
- 1 test
- SQL> select sql_text from v$sql where sql_text like 'select * from t where name=%';
- SQL_TEXT
- --------------------------------------------------------------------------------
- select * from t where name='test'
3. oracle在解析sql时会把plsql中定义的变量转为绑定变量
- SQL> create table tt(id int,name varchar2(10));
- Table created.
- SQL> alter session set sql_trace=true;
- Session altered.
- SQL> declare
- 2 begin
- 3 for i in 1 .. 100 loop
- 4 insert into tt values(i,'test');
- 5 end loop;
- 6 commit;
- 7 end;
- 8 /
- PL/SQL procedure successfully completed.
- SQL> alter session set sql_trace=false;
- Session altered.
trace文件内容:
- *** 2016-10-30 12:11:22.815
- CLOSE #140170997623912:c=0,e=6,dep=0,type=0,tim=1477800682815427
- =====================
- PARSING IN CURSOR #140170997623912 len=92 dep=0 uid=0 oct=47 lid=0 tim=1477800682817922 hv=218581220 ad='8c89d9b0' sqlid='6pdgqjs6hfk74'
- declare
- begin
- for i in 1 .. 100 loop
- insert into tt values(i,'test');
- end loop;
- commit;
- end;
- END OF STMT
- PARSE #140170997623912:c=1999,e=2431,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1477800682817921
- =====================
- PARSING IN CURSOR #140170996439488 len=34 dep=1 uid=0 oct=2 lid=0 tim=1477800682818383 hv=1299226876 ad='86bc23a8' sqlid='9j06ydd6r187w'
- INSERT INTO TT VALUES(:B1 ,'test')
- END OF STMT
从硬解析的增长也可以看出:
- SQL> select a.*,b.name
- 2 from v$sesstat a , v$statname b
- 3 where a.statistic#=b.statistic#
- 4 and a.sid=(select distinct sid from v$mystat)
- 5 and b.name like '%parse%';
- SQL> col name format a30
- SQL> /
- SID STATISTIC# VALUE NAME
- ---------- ---------- ---------- ------------------------------
- 29 264 0 ADG parselock X get attempts
- 29 265 0 ADG parselock X get successes
- 29 622 4 parse time cpu
- 29 623 8 parse time elapsed
- 29 624 238 parse count (total)
- 29 625 155 parse count (hard)
- 29 626 0 parse count (failures)
- 29 627 0 parse count (describe)
- 8 rows selected.
执行前的硬解析数为155
- SQL> declare
- 2 begin
- 3 for i in 1 .. 100 loop
- 4 insert into tt values(i,'test');
- 5 end loop;
- 6 commit;
- 7 end;
- 8 /
- PL/SQL procedure successfully completed.
- SQL> select a.*,b.name
- 2 from v$sesstat a , v$statname b
- 3 where a.statistic#=b.statistic#
- 4 and a.sid=(select distinct sid from v$mystat)
- 5 and b.name like '%parse%';
- SID STATISTIC# VALUE NAME
- ---------- ---------- ---------- ------------------------------
- 29 264 0 ADG parselock X get attempts
- 29 265 0 ADG parselock X get successes
- 29 622 4 parse time cpu
- 29 623 8 parse time elapsed
- 29 624 242 parse count (total)
- 29 625 157 parse count (hard)
- 29 626 0 parse count (failures)
- 29 627 0 parse count (describe)
- 8 rows selected.
执行后的为157,只增长了两个,如果不是使用了绑定变量,硬解析数绝对不止两个
4. 存储过程中的参数会自动转化为绑定变量
- SQL> create or replace procedure proc_test(p_id int,p_name varchar2)
- 2 is
- 3 begin
- 4 insert into tt values(p_id,p_name);
- 5 commit;
- 6 end;
- 7 /
- Procedure created.
- SQL> alter session set sql_trace=true;
- Session altered.
- SQL> exec proc_test(200,'test');
- PL/SQL procedure successfully completed.
- SQL> alter session set sql_trace=false;
- Session altered.
trace文件内容:
- *** 2016-10-31 04:11:23.421
- CLOSE #140585231805712:c=0,e=6,dep=0,type=0,tim=1477858283421964
- =====================
- PARSING IN CURSOR #140585231805712 len=35 dep=0 uid=0 oct=47 lid=0 tim=1477858283423073 hv=526484776 ad='86b57878' sqlid='asc6yd8gq3198'
- BEGIN proc_test(200,'test'); END;
- END OF STMT
- PARSE #140585231805712:c=999,e=1047,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1477858283423072
- =====================
- PARSING IN CURSOR #140585233135112 len=32 dep=1 uid=0 oct=2 lid=0 tim=1477858283423304 hv=1422618771 ad='8697d9b8' sqlid='1yqc845acqw4m'
- INSERT INTO TT VALUES(:B2 ,:B1 )
- END OF STMT
- PARSE #140585233135112:c=0,e=100,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1477858283423304
- begin
- -- Call the procedure
- proc_test(p_id => :p_id,
- p_name => :p_name);
- end;
5. 动态sql中使用绑定变量
a. 直接使用游标中的值拼接
- [oracle@centos6 scripts]$ cat sql_parse1.sql
- declare
- cursor test_cur is select id,name from tt;
- begin
- for i in test_cur loop
- execute immediate 'insert into tt values('||i.id||','||chr(39)||i.name||chr(39)||')';
- end loop;
- commit;
- end;
- SQL> alter system flush shared_pool;
- System altered.
- SQL> @sql_parse1.sql
- PL/SQL procedure successfully completed.
- SQL> set linesize 200
- SQL> col hash_value format 9999999999
- SQL> col sql_id format 99
- SQL> col child_latch format 99
- SQL> col version_count format 99
- SQL> col sql_text format a40
- SQL> col parse_calls format 999
- SQL> select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like 'insert into tt%';
- HASH_VALUE SQL_ID CHILD_LATCH VERSION_COUNT SQL_TEXT PARSE_CALLS
- ----------- ------------- ----------- ------------- -------------------------------------------------- -----------
- 3161064081 196c4s2y6n0nj 0 1 insert into tt values(45,'test') 1
- 3718124844 6hfpagbftw59c 0 1 insert into tt values(70,'test') 1
- 4046592725 c5txty7sm46qp 0 1 insert into tt values(28,'test') 1
- 961289967 4n0n3dnwns7rg 0 1 insert into tt values(30,'test') 1
- 2124685404 g70mmhxza882w 0 1 insert into tt values(26,'test') 1
- 608576974 3nm07v4k4c9ff 0 1 insert into tt values(31,'test') 1
- 3770952793 2xcry8ghc8b2t 0 1 insert into tt values(1,'test') 1
b. 绑定变量写法
- [oracle@centos6 scripts]$ cat sql_parse2.sql
- declare
- cursor test_cur is select id,name from tt;
- begin
- for i in test_cur loop
- execute immediate 'insert into tt values(:a,:b)' using i.id,i.name;
- end loop;
- commit;
- end;
- /
- SQL> alter system flush shared_pool;
- System altered.
- SQL> @sql_parse2.sql
- PL/SQL procedure successfully completed.
- SQL> select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like 'insert into tt%';
- HASH_VALUE SQL_ID CHILD_LATCH VERSION_COUNT SQL_TEXT PARSE_CALLS
- ----------- ------------- ----------- ------------- -------------------------------------------------- -----------
- 2034333845 gbkazctwn2y4p 0 1 insert into tt values(:a,:b) 1