绑定变量和cursor_sharing:
如果SHARED_POOL_SIZE设置得足够大,又可以排除Bug的因素,那么大多数的ORA-04031错误都是由共享池中的大量SQL代码等导致过多内存碎片引起的。
可能的主要原因有:
·SQL没有足够的共享;
·大量不必要的解析调用;
·没有使用绑定变量。
实际上说,应用的编写和调整始终是最重要的内容,Shared Pool的调整根本上要从应用入手。根本上,使用绑定变量可以充分降低Shared Pool和Library Cache的Latch竞争,从而提高性能。
反复的SQL硬解析不仅会消耗大量的CPU资源,也会占用更多的内存,严重影响数据库性能,而使用绑定变量则可以使SQL充分共享,实现SQL的软解析,提高系统性能。以下是Oracle 10g中一个关于绑定变量和非绑定变量的测试对比,由此可以略窥绑定性能影响之一斑。
首先创建测试表并记录解析统计数据:
sys@NEI> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
tq@NEI> create table dbtan (id number);
表已创建。
tq@NEI> select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and name like 'parse%';
NAME VALUE
------------------------------ ---------
parse time cpu 11
parse time elapsed 140
parse count (total) 189
parse count (hard) 30
parse count (failures) 2
进行循环插入数据,以下代码并未使用绑定变量:
tq@NEI> begin
2 for i in 1..10 loop
3 execute immediate 'insert into dbtan values('||i||')';
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
完成之后检查统计信息,注意硬解析数增加了10次,也就是说每次INSERT操作都需要进行一次独立的解析:
tq@NEI> select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and name like 'parse%';
NAME VALUE
------------------------------ ---------
parse time cpu 13
parse time elapsed 159
parse count (total) 205
parse count (hard) 40
parse count (failures) 2
查询v$sqlarea视图,可以找到这些不能共享的SQL,注意每条SQL都只执行了一次,这些SQL不仅解析要消耗密集的SQL资源,也要占用共享内存存储这些不同的SQL代码:
tq@NEI> col sql_text for a40
tq@NEI> select sql_text,version_count,parse_calls,executions
2 from v$sqlarea
3 where sql_text like 'insert into dbtan%';
SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
---------------------------------------- ------------- ----------- ----------
insert into dbtan values(10) 1 1 1
insert into dbtan values(3) 1 1 1
insert into dbtan values(6) 1 1 1
insert into dbtan values(7) 1 1 1
insert into dbtan values(2) 1 1 1
insert into dbtan values(1) 1 1 1
insert into dbtan values(8) 1 1 1
insert into dbtan values(5) 1 1 1
insert into dbtan values(9) 1 1 1
insert into dbtan values(4) 1 1 1
已选择10行。
重建测试表,进行第二次测试:
tq@NEI> drop table dbtan purge;
表已删除。
tq@NEI> create table dbtan (id number);
表已创建。
tq@NEI> select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and name like 'parse%';
NAME VALUE
------------------------------ ---------
parse time cpu 34
parse time elapsed 294
parse count (total) 748
parse count (hard) 141
parse count (failures) 3
这一次使用绑定变量,同样10次数据插入:
tq@NEI> begin
2 for i in 1..10 loop
3 execute immediate 'insert into dbtan values(:v1)' using i;
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
现在看一下SQL解析的统计数据库,硬解析由原来的141增加到143
tq@NEI> select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and name like 'parse%';
NAME VALUE
------------------------------ ---------
parse time cpu 34
parse time elapsed 295
parse count (total) 755
parse count (hard) 143
parse count (failures) 3
对于该SQL,共享池中只存在一份,解析一次,执行10次,这就是绑定变量的优势所在:
tq@NEI> select sql_text,version_count,parse_calls,executions
2 from v$sqlarea
3 where sql_text like 'insert into dbtan%';
SQL_TEXT VERSION_COUNT PARSE_CALLS EXECUTIONS
---------------------------------------- ------------- ----------- ----------
insert into dbtan values(:v1) 1 1 10
在应用程序开发的过程中,都应该优先考虑使用绑定变量(在JAVA应用中可以使用PreparedStatement进行变量绑定),但是如果应用没有很好地使用绑定变量,那么Oracle从8.1.6开始提供了一个新的初始化参数用以在Server端进行强制变量绑定,这个参数就是cursor_sharing。最初这个参数有两个可选设置:exact和force。
缺省值是exact,表示精确匹配;force表示在Server端执行强制绑定。在8i的版本里使用这个参数对某些应用可以带来极大的性能提高,但是同时也存在一些副作用,比如优化器无法生成精确的执行计划,SQL执行计划发生改变等(所以如果启用cursor_sharing参数时,一定确认用户的应用在此模式下经过充分的测试)。
从Oracle 9i开始,Oracle引入了绑定变量Peeking的机制,SQL在第一次执行时,首先在Session的PGA中使用具体值生成精确的执行计划,以期可以提高执行计划的准确性,然而Peeking的方式只有在第一次硬解析时生效,所以仍然可能存在问题,导致后续的SQL错误的执行;同时,在Oracle 9i中,cursor_sharing参数有了第3个选项:similar。该参数指定Oracle在存在柱状图信息时,对于不同的变量值,重新解析,从而可以利用柱状图更加精确地制定SQL执行计划。也即当存在柱状图信息时,similar的表现和exact相同;当柱状图信息不存在时,similar的表现和force相同。
但是需要注意的是,在某些版本中(如:Oracle 9.2.0.5),设置cursor_sharing为similar可能导致SQL的version_count过高的Bug,该选项在不同版本中都可能存在问题,是需要斟酌使用的一个参数,设置该参数不过是一个临时的解决办法,根本的性能提升仍然需要通过优化SQL来解决。
除了Bug之外,在正常情况下,由于Similar的判断机制,可能也会导致SQL无法共享。在收集了柱状图(Hisogram)信息之后,如果SQL未使用绑定变量,当SQL使用具备柱状图信息的Column时,数据库会认为SQL传递过来的每个常量都是不可靠的,需要为每个SQL生成一个Cursor,这种情况被称为UNSAFE BINDS。大量的version_count可能会导致数据库产生大量的cursor:pin S wait on X等待。解决这类问题,可以设置cursor_sharing为FORCE或者删除相应字段上的柱状图信息。
- The End -