关于oracle绑定变量的测试

使用绑定变量可以减少硬解析，所以在客户端执行SQL语句时一般会写成绑定变量的形式，以增加SQL执行效率。

但在存储过程中要不要使用绑定变量，心中一直比较疑惑，前两天吃饭时和大叔聊了下，他说过程中两者应该没太大区别，又增加我一丝疑惑。今天兴致来了打算自己测下。

create table wj_test(a number);

1.单条SQL测试

select count(*)   from wj_test where a=1;

select count(*)   from wj_test where a=2;

select 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 count(*)   from wj_test where a=1         1

        ghtnh5c8hbk8a        select count(*)   from wj_test where a=2         1

        6uqwktrnj9gxf        select 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 count(*) into a from wj_test where a=i;

  insert 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 count(*) into a from wj_test where a=i;

  6    insert 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都会将普通的变量转换成绑定变量的形式 。

以后写过程不用纠结了。