sqlplus之使用绑定变量
扫描二维码
随时随地手机看文章
oracle将已解析、已编译的SQL连同其他内容存储在共享池(shared pool)中，这是系统全局区(System Global Area,SGA)中一个非常重要的共享内存结构.
绑定变量(bind variable)是查询中的一个占位符。
例如，要获取员工编号7369的相应记录，可以使用：
scott@ORCL>select * from emp where empno=7369; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20
或者可以将绑定变量:empno设置为7369，再执行查询:
scott@ORCL>variable empno number; scott@ORCL>exec :empno := 7369; PL/SQL 过程已成功完成。 scott@ORCL>select * from emp where empno=:empno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-12月-80 800 20
在典型的系统中，你可能只查询一次员工 7369，然后不再查询这个员工了。之后，你可能会查询员工666，然后是员工888，如此等等。如果在查询中使用直接量(常量),那么每个查询都将是一个全新的查询，在数据库看来以前从未见过，必须对查询进行解析、限定(命名解析)、安全性检查、优化等。你执行的每条不同的语句都要在执行时进行编译。
第二个查询使用了一个绑定变量:empno，变量值在查询执行时提供。这个查询只编译一次，随后会把查询计划存储在一个共享池(库缓存)中，以便以后获取和重用这个查询计划。
--------------------------------------------------------------------------------------------------------------------------------
第一个过程使用了带绑定变量的sql语句，如下：
scott@ORCL>create or replace procedure proc1 2 as 3 begin 4 for i in 1 .. 10000 5 loop 6 execute immediate 7 'insert into t values(:x)' using i; 8 end loop; 9 end; 10 / 过程已创建。
第二个过程则分别要为插入的每一行构造一条独特的sql语句，如下：
scott@ORCL>create or replace procedure proc2
2 as
3 begin
4 for i in 1 .. 10000
5 loop
6 execute immediate
7 'insert into t values('|| i ||')';
8 end loop;
9 end;
10 /
过程已创建。比较两者性能区别：
scott@ORCL>exec runstats_pkg.rs_start PL/SQL 过程已成功完成。 scott@ORCL>exec proc1 PL/SQL 过程已成功完成。 scott@ORCL>exec runstats_pkg.rs_middle PL/SQL 过程已成功完成。 scott@ORCL>exec proc2 PL/SQL 过程已成功完成。 scott@ORCL>exec runstats_pkg.rs_stop(1000) #打印差距大于1000以上的比较结果 Run1 ran in 25 cpu hsecs Run2 ran in 396 cpu hsecs run 1 ran in 6.31% of the time Name Run1 Run2 Diff LATCH.object queue header oper 2,569 280 -2,289 STAT...sql area evicted 0 7,506 7,506 STAT...session cursor cache hi 10,010 63 -9,947 STAT...calls to kcmgcs 51 10,048 9,997 STAT...enqueue requests 30 10,029 9,999 STAT...enqueue releases 28 10,029 10,001 STAT...parse count (hard) 4 10,011 10,007 STAT...parse count (total) 26 10,057 10,031 STAT...consistent gets from ca 51 10,126 10,075 STAT...consistent gets 96 10,318 10,222 STAT...consistent gets from ca 96 10,318 10,222 STAT...file io wait time 19,052 0 -19,052 LATCH.enqueue hash chains 853 20,669 19,816 LATCH.enqueues 759 20,626 19,867 STAT...db block gets 10,421 30,373 19,952 STAT...db block gets from cach 10,421 30,373 19,952 STAT...db block gets from cach 61 20,042 19,981 STAT...session logical reads 10,517 40,691 30,174 STAT...recursive calls 10,328 41,019 30,691 STAT...session uga memory max 123,512 72,952 -50,560 LATCH.kks stats 9 50,688 50,679 LATCH.cache buffers chains 57,017 113,844 56,827 STAT...session uga memory 65,488 130,976 65,488 STAT...session pga memory max 131,072 65,536 -65,536 STAT...session pga memory 65,536 131,072 65,536 LATCH.shared pool simulator 27 77,205 77,178 STAT...physical read total byt 81,920 0 -81,920 STAT...cell physical IO interc 81,920 0 -81,920 STAT...physical read bytes 81,920 0 -81,920 LATCH.row cache objects 809 182,273 181,464 LATCH.shared pool 20,393 455,102 434,709 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 86,743 925,525 838,782 9.37% PL/SQL 过程已成功完成。
结果清楚地显示，从墙上时钟来看，proc2(没有使用绑定变量)插入10000行记录的时间要比proc1(使用了绑定变量)要多出很多。实际上，proc2需要的时间是proc1的15倍多，这说明，在这种情况下，对于每个"无绑定变量"的insert，执行语句所需时间中有14/15仅用于解析！
可以看到，如果使用了绑定变量，则只有4次解析；没有使用绑定变量，却有不下10000次的硬解析(每次插入都会带来一次硬解析).
下载文档
