Oracle中的变量使用：&、&&、DEIFINE、VARIABLE
概念：在oracle 中，对于一个提交的sql语句,存在两种可选的解析过程, 一种叫做硬解析,一种叫做软解析.
一个硬解析需要经解析,制定执行路径,优化访问计划等许多的步骤.硬解释不仅仅耗费大量的cpu，更重要的是会占据重要的们闩（latch）资源，
严重的影响系统的规模的扩大（即限制了系统的并发行），而且引起的问题不能通过增加内存条和cpu的数量来解决。之所以这样是因为门闩是为
了顺序访问以及修改一些内存区域而设置的，这些内存区域是不能被同时修改。当一个sql语句提交后，oracle会首先检查一下共享缓冲池（shared pool）
里有没有与之完全相同的语句，如果有的话只须执行软分析即可，否则就得进行硬分析。 而唯一使得oracle 能够重复利用执行计划的方法就是采用绑定变量。
绑定变量的实质就是用于替代sql语句中的常量的替代变量。绑定变量能够使得每次提交的sql语句都完全一样。
1、sqlplus中通过variable来绑定变量
OBJECT_NAME
--------------------------------------------------------------------------------
ORA$BASE
SQL> select object_name from t1 where object_id=200;
OBJECT_NAME
--------------------------------------------------------------------------------
ICOL$
I_USER1
SYS_IL0000000196C00005$$
SQL> variable i number;
SQL> exec :i :=100;
PL/SQL 过程已成功完成。
SQL> select object_name from t1 where object_id=:i;
OBJECT_NAME
--------------------------------------------------------------------------------
ORA$BASE
SQL> exec :i :=200;
PL/SQL 过程已成功完成。
SQL> select object_name from t1 where object_id=:i;
OBJECT_NAME
--------------------------------------------------------------------------------
ICOL$
I_USER1
SYS_IL0000000196C00005$$
SQL> select sql_text,parse_calls from v$sql where sql_text like 'select object_name from t1 where object_id=%';
SQL_TEXT
--------------------------------------------------------------------------------
PARSE_CALLS
-----------
select object_name from t1 where object_id=:i
1
select object_name from t1 where object_id=100
1
可以看到这条sql被调用了两次，这两次的使用就包括了一次soft parse
2、通过define定义
sqlplus中通过define定义的并不是变量，而只是字符常量，define定义之后，再通过&或&&引用的时候就不需要再输入了，oracle在执行的时候回自动用
定义的值进行替换，仅此而已，并不是绑定变量。
SQL> define a=100
SQL> define
DEFINE _DATE = "20-9月 -18" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "ORCL1" (CHAR)
DEFINE _USER = "TT" (CHAR)
DEFINE _PRIVILEGE = "" (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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1102000400" (CHAR)
DEFINE A = "100" (CHAR)
SQL> select object_name from t1 where object_id=&a;
原值 1: select object_name from t1 where object_id=&a
新值 1: select object_name from t1 where object_id=100
OBJECT_NAME
--------------------------------------------------------------------------------
ORA$BASE
&&和&一样的功能，不过&&替代过一次之后就不需要再输入了，可以多次替代。
注意：另外，如果define定义的是字符类型，在引用时需要加上单引号
SQL> select object_id from t1 where object_name=&c;
输入 c 的值: ORA$BASE
原值 1: select object_id from t1 where object_name=&c
新值 1: select object_id from t1 where object_name=ORA$BASE
select object_id from t1 where object_name=ORA$BASE
*
第 1 行出现错误:
ORA-00904: "ORA$BASE": 标识符无效
SQL> select object_id from t1 where object_name='&c';
输入 c 的值: ORA$BASE
原值 1: select object_id from t1 where object_name='&c'
新值 1: select object_id from t1 where object_name='ORA$BASE'
OBJECT_ID
----------
100
在执行中，oracle 自动进行了替换操作。
3、oracle在解析sql时会把plsql中定义的变量转为绑定变量
SQL> create table tt(id int,name varchar2(10));
表已创建。
SQL> alter session set sql_trace=true;
会话已更改。
SQL> declare
begin
for i in 1 .. 100 loop
insert into tt values(i,'test');
end loop;
commit;
end;
/
PL/SQL 过程已成功完成。
SQL> alter session set sql_trace=false;
会话已更改。
SQL> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));
TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_ora_15542.trc
cat /u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_ora_15542.trc
trace文件内容如下:
*** 2018-09-20 22:25:19.032
CLOSE #139802690152008:c=0,e=45,dep=0,type=0,tim=1537453519032416
=====================
PARSING IN CURSOR #139802690152008 len=34 dep=0 uid=90 oct=42 lid=90 tim=1537453519033909 hv=3317026814 ad='7f2659aafe00' sqlid='2w621wv2vbmzy'
alter session set sql_trace=false
END OF STMT
PARSE #139802690152008:c=0,e=1194,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1537453519033907
EXEC #139802690152008:c=0,e=117,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1537453519034287
SQL> select a.*,b.name from v$sesstat a , v$statname b
2 where a.statistic#=b.statistic#
3 and a.sid=(select distinct sid from v$mystat)
4 and b.name like '%parse%';
SID STATISTIC# VALUE NAME
---------- ---------- ---------- ------------------------------
117 264 0 ADG parselock X get attempts
117 265 0 ADG parselock X get successes
117 622 44 parse time cpu
117 623 328 parse time elapsed
117 624 214 parse count (total)
117 625 81 parse count (hard)
117 626 4 parse count (failures)
117 627 0 parse count (describe)
已选择8行。
以上可以看出硬解析是81
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 过程已成功完成。
SQL> select a.*,b.name
from v$sesstat a , v$statname b
where a.statistic#=b.statistic#
and a.sid=(select distinct sid from v$mystat)
and b.name like '%parse%';
SID STATISTIC# VALUE NAME
---------- ---------- ---------- ------------------------------
117 264 0 ADG parselock X get attempts
117 265 0 ADG parselock X get successes
117 622 49 parse time cpu
117 623 341 parse time elapsed
117 624 225 parse count (total)
117 625 88 parse count (hard)
117 626 8 parse count (failures)
117 627 0 parse count (describe)
已选择8行。
以上可以看出硬解析是88,只增长了7个，如果不是使用了绑定变量，硬解析数应该不止88。
4、动态sql中使用绑定变量
[oracle@rac1 ~]$ 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;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;
> EOF
SQL> alter system flush shared_pool;
系统已更改。
SQL> @sql_parse1.sql
9 /
PL/SQL 过程已成功完成。
SQL> set linesize 200
col hash_value format 9999999999
col sql_id format 99
col child_latch format 99
col version_count format 99
col sql_text format a40
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') 2
3718124844 6hfpagbftw59c 0 1 insert into tt values(70,'test') 2
4046592725 c5txty7sm46qp 0 1 insert into tt values(28,'test') 2
961289967 4n0n3dnwns7rg 0 1 insert into tt values(30,'test') 2
2124685404 g70mmhxza882w 0 1 insert into tt values(26,'test') 2
608576974 3nm07v4k4c9ff 0 1 insert into tt values(31,'test') 2
3770952793 2xcry8ghc8b2t 0 1 insert into tt values(1,'test') 2
3835047783 8dwuxnzk9cbv7 0 1 insert into tt values(78,'test') 2
807285090 at1mtkws1wcb2 0 1 insert into tt values(9,'test') 2
224278236 ax97csh6pwdqw 0 1 insert into tt values(54,'test') 2
2759080143 0xm5p1fk78f6g 0 1 insert into tt values(76,'test') 2
已选择100行。
每条语句都解析了两次，总共解析了200次。
我们换成绑定动态变量的方式
[oracle@rac1 ~]$ 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;
系统已更改。
SQL> @sql_parse2.sql
10 /
PL/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
可以很明显的看出硬解析只有一次，效率提升了许多。
阅读(3675) | 评论(0) | 转发(0) |
