Sql最佳化（六）程式可擴充套件性：soft parse/hard parse，以及為什麼要使用繫結變數

一、 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次硬解析。後者消耗的系統資源大大超過前者。