关于oracle绑定变量的测试

使用绑定变量可以减少硬解析，所以在客户端执行SQL语句时一般会写成绑定变量的形式，以增加SQL执行效率。
但在存储过程中要不要使用绑定变量，心中一直比较疑惑，前两天吃饭时和大叔聊了下，他说过程中两者应该没太大区别，又增加我一丝疑惑。今天兴致来了打算自己测下。


create table wj_test(a number);
1.单条SQL测试
select/*jarno1*/ count(*)   from wj_test where a=1;
select/*jarno1*/ count(*)   from wj_test where a=2;
select/*jarno1*/ count(*)   from wj_test where a=3;


select SQL_ID,sql_text,EXECUTIONS from v$sqlarea where SQL_TEXT like '%jarno%';
           SQL_ID        SQL_TEXT        EXECUTIONS
        17wcqfh96vkqv        select /*jarno*/ count(*)   from wj_test where a=1         1
        ghtnh5c8hbk8a        select /*jarno*/ count(*)   from wj_test where a=2         1
        6uqwktrnj9gxf        select /*jarno*/ count(*)   from wj_test where a=3         1


不出所料此SQL被解析了3次。 


2.过程测试:
为了测试公平，先清空 shared_pool;
alter system flush shared_pool;
System altered


SQL> select name,value from v$sysstat where name like 'parse%';


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                          795
parse time elapsed                                                     1851
parse count (total)                                                   60610
parse count (hard)                                                     5784
parse count (failures)                                                   40




create or replace procedure sp_wj_test is
a number;
begin
  for i in 1..10000 loop
  select/*jarno1*/ count(*) into a from wj_test where a=i;
  insert /*jarno1*/ into wj_test values (i);
  commit;
  end loop;
end sp_wj_test;


SQL> alter system flush shared_pool; 


SQL> select name,value from v$sysstat where name like 'parse%';


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                          801
parse time elapsed                                                     1859
parse count (total)                                                   61402
parse count (hard)                                                     5888
parse count (failures)                                                   40


SQL> exec sp_wj_test;


PL/SQL procedure successfully completed


SQL> select name,value from v$sysstat where name like 'parse%';


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                          803
parse time elapsed                                                     1861
parse count (total)                                                   61444
parse count (hard)                                                     5903
parse count (failures)                                                   40


SQL> select SQL_ID,sql_text,EXECUTIONS from v$sqlarea where SQL_TEXT like '%jarno1%';


SQL_ID        SQL_TEXT                                                                         EXECUTIONS
------------- -------------------------------------------------------------------------------- ----------
6cqtdh9v7pfdh select SQL_ID,sql_text,EXECUTIONS from v$sqlarea where SQL_TEXT like '%jarno1%'           1


可以看出，创建过程时，数据库硬解析增加了100多。执行过程时（1万次查询和插入），数据库硬解析才增加了15 。
v$sql中没有我过程中执行的那两条语句。可以猜测ORACLE编译时将SQL转换成了绑定变量的形式。


为了验证这个想法，于是进行SQL跟踪


SQL> alter system flush shared_pool;


System altered


SQL> alter session set sql_trace=true;


Session altered


SQL> exec sp_wj_test;


PL/SQL procedure successfully completed


SQL> alter session set sql_trace=false;


Session altered


trans: 
============================================================== 
PARSING IN CURSOR #5 len=41 dep=1 uid=33 oct=3 lid=33 tim=10560281481 hv=2013322977 ad='890d277c'
SELECT COUNT(*) FROM WJ_TEST WHERE A=:B1 
END OF STMT
PARSE #5:c=0,e=251,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=10560281477


PARSING IN CURSOR #1 len=33 dep=1 uid=33 oct=2 lid=33 tim=10560330961 hv=184852536 ad='89134c20'
INSERT INTO WJ_TEST VALUES (:B1 )
END OF STMT
PARSE #1:c=0,e=78,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=10560330959
=============================================================
OK,SQL执行时果然是以绑定变量的方式执行的。




3 匿名块测试
    匿名块执行时是否会被替换成绑定变量？之前在论坛看过很多以匿名块来举例应用绑定变量的好处。那么匿名块如果不写绑定变量是否效率大打折扣了呢？
     
SQL> alter system flush shared_pool;


System altered


SQL> select name,value from v$sysstat where name like 'parse%'
  2  ;


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                          870
parse time elapsed                                                     1935
parse count (total)                                                   63157
parse count (hard)                                                     6342
parse count (failures)                                                   46


SQL>  alter session set sql_trace=true;


Session altered


SQL> 
SQL> declare
  2  a number;
  3  begin
  4    for i in 1..10000 loop
  5    select/*jarno1*/ count(*) into a from wj_test where a=i;
  6    insert /*jarno1*/ into wj_test values (i);
  7    commit;
  8    end loop;
  9  end;
10  /




PL/SQL procedure successfully completed


SQL> select name,value from v$sysstat where name like 'parse%';


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                          875
parse time elapsed                                                     1940
parse count (total)                                                   63224
parse count (hard)                                                     6385
parse count (failures)                                                   46


SQL>  alter session set sql_trace=false;


Session altered




=============================================================
PARSING IN CURSOR #3 len=41 dep=1 uid=33 oct=3 lid=33 tim=11632769522 hv=2013322977 ad='890d277c'
SELECT COUNT(*) FROM WJ_TEST WHERE A=:B1 
END OF STMT
PARSE #3:c=0,e=192,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=11632769519


PARSING IN CURSOR #1 len=33 dep=1 uid=33 oct=2 lid=33 tim=11632805231 hv=184852536 ad='89134c20'


INSERT INTO WJ_TEST VALUES (:B1 )
END OF STMT
PARSE #1:c=0,e=157,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=11632805228
=============================================================
出乎我意料！硬解析并未增加太多，再看跟踪文件，SQL执行时还是转化成了绑定变量。
看来无论是存储过程，还是匿名块，oracle都会将普通的变量转换成绑定变量的形式 。
以后写过程不用纠结了。