绑定变量和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 -