Sql优化（六）程序可扩展性：soft parse/hard parse，以及为什么要使用绑定变量

有时单个sql运行效率还不错，但程序一发布，并发进程多了后，系统就运行很慢，这说明程序的扩展性较差。程序扩展性(scalability)差的原因有很多，例如设计不合理导致的互锁。除了锁之外，硬解析也是一个重要原因。硬解析会造成服务器cpu资源紧张，tom甚至认为不使用绑定变量往往是影响oracle性能和扩展性的最大问题。

[@more@]

一、 soft parse和hard parse

当oracle执行一句sql时，会先在library cache中进行匹配（相同sql是否运行过？），如果匹配成功则直接使用，这称为soft parse;

如果匹配失败，则需要parse,name resolved,security-check,optimize等等，这称为hard parse(硬解析)，可以想像成源程序先编译后运行。

硬解析的危害：

1) 占用资源更多,执行慢，因为不会重用已解析好的query plan

2) 硬解析导致library cache上的latch竞争，这会降低系统的并发性，使oracle无法充分利用系统资源。（此时即使系统资源看上去不忙，oracle也会很慢）

3) 一个有很多硬解析的简单应用可能导致数据库所有应用变慢。

二、 为什么必须使用绑定变量

如果不使用绑定变量而使用常量，会导致大量硬解析。以下是一个测试，对比使用绑定变量和使用常量时解析情况

SQL> select a.name name, b.value

2 from v$statname a, v$mystat b

3 where a.statistic# = b.statistic# and (a.name) in('parse count (hard)','parse count (total)')

4 order by name;

parse count (hard) 19

parse count (total) 92

SQL> declare

i number;

n number;

begin

for i in 1..10 loop

n:=i;

insert into test_table3 values(n);

end loop;

end;

/

PL/SQL procedure successfully completed.

SQL> select a.name name, b.value

from v$statname a, v$mystat b

where a.statistic# = b.statistic# and (a.name) in('parse count (hard)','parse count (total)')

order by name;

parse count (hard) 22

parse count (total) 106

SQL> declare

begin

insert into test_table3 values(1);

insert into test_table3 values(2);

insert into test_table3 values(3);

insert into test_table3 values(4);

insert into test_table3 values(5);

insert into test_table3 values(6);

insert into test_table3 values(7);

insert into test_table3 values(8);

insert into test_table3 values(9);

insert into test_table3 values(10);

end;

/

PL/SQL procedure successfully completed.

SQL> select a.name name, b.value

from v$statname a, v$mystat b

where a.statistic# = b.statistic# and (a.name) in('parse count (hard)','parse count (total)')

order by name;

parse count (hard) 33

parse count (total) 118

可以看到，在上面的例子中，使用绑定变量时，有3次硬解析（包括PLSQL块），而使用常量时，则产生了11次硬解析。后者消耗的系统资源大大超过前者。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18474/viewspace-1060734/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18474/viewspace-1060734/