绑定变量和cursor_sharing

绑定变量和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。最初这个参数有两个可选设置:exactforce

缺省值是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 -