使用绑定变量可以减少硬解析,所以在客户端执行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都会将普通的变量转换成绑定变量的形式 。
以后写过程不用纠结了。
但在存储过程中要不要使用绑定变量,心中一直比较疑惑,前两天吃饭时和大叔聊了下,他说过程中两者应该没太大区别,又增加我一丝疑惑。今天兴致来了打算自己测下。
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都会将普通的变量转换成绑定变量的形式 。
以后写过程不用纠结了。