2014-08-11 BaoXinjian
一、摘要
Oracle硬解析和軟解析是我們經常遇到的問題，所以需要考慮何時產生軟解析何時產生硬解析，如何判斷
1. SQL的執行過程
當發布一條SQL或PL/SQL命令時，Oracle會自動尋找該命令是否存在於共享池中來決定對當前的語句使用硬解析或軟解析。
通常情況下，SQL語句的執行過程如下：
Step1. SQL代碼的語法(語法的正確性)及語義檢查(對象的存在性與權限)。
Step2. 將SQL代碼的文本進行哈希得到哈希值。
Step3. 如果共享池中存在相同的哈希值，則對這個命令進一步判斷是否進行軟解析，否則到e步驟。
Step4. 對於存在相同哈希值的新命令行，其文本將與已存在的命令行的文本逐個進行比較。
這些比較包括大小寫，字符串是否一致，空格，注釋等，如果一致，則對其進行軟解析，轉到步驟Step6，無需再次硬解析。
否則到步驟Step5。
Step5. 硬解析，生成執行計劃。
Step6. 執行SQL代碼，返回結果。
2. Oracle對此sql將進行幾個步驟的處理過程：
Step1. 語法檢查(syntax check)
檢查此sql的拼寫是否語法。
Step2. 語義檢查(semantic check)
諸如檢查sql語句中的訪問對象是否存在及該用戶是否具備相應的權限。
Step3、對sql語句進行解析(parse)
利用內部算法對sql進行解析，生成解析樹(parse tree)及執行計劃(execution plan)。
Step4、執行sql，返回結果(execute and return)
3. 硬解析的危害：
(1) 占用資源更多,執行慢，因為不會重用已解析好的query plan。
(2) 硬解析導致library cache上的latch競爭，這會降低系統的並發性，使oracle無法充分利用系統資源。（此時即使系統資源看上去不忙，oracle也會很慢）。
(3) 一個有很多硬解析的簡單應用可能導致數據庫所有應用變慢。
4. 總結
其中，軟、硬解析就發生在第三個過程裡（對sql語句進行解析parse）。
Oracle利用內部的hash算法來取得該sql的hash值，然後在library cache裡查找是否存在該hash值；
假設存在，則將此sql與cache中的進行比較；
假設“相同”，就將利用已有的解析樹與執行計劃，而省略了優化器的相關工作。這也就是軟解析的過程。
誠然，如果上面的2個假設中任有一個不成立，那麼優化器都將進行創建解析樹、生成執行計劃的動作。這個過程就叫硬解析。
創建解析樹、生成執行計劃對於sql的執行來說是開銷昂貴的動作，所以，應當極力避免硬解析，盡量使用軟解析。
二、軟解析
Oracle中SQL語句執行過程中,Oracle內部解析原理如下:
1、當一用戶第一次提交一個SQL表達式時,Oracle會將這SQL進行Hard parse,這過程有點像程序編譯,檢查語法、表名、字段名等相關信息（如下圖），這過程會花比較長的時間，因為它要分析語句的語法與語義。然後獲得最優化後的執行計劃（sql plan），並在內存中分配一定的空間保存該語句與對應的執行計劃等信息。
2、當用戶第二次請求或多次請求時，Oracle會自動找到先前的語句與執行計劃，而不會進行Hard parse,而是直接進行Soft parse（把語句對應的執行計劃調出，然後執行）,從而減少數據庫的分析時間。
注意的是：Oracle中只能完全相同的語句，包大小寫、空格、換行都要求一樣時，才會重復使用以前的分析結果與執行計劃。
3、對於大量的、頻繁訪問的SQL語句，如果不采用Bind 變量的方式，哪Oracle會花費大量的Shared latch與CPU在做Hard parse處理，所以，要盡量提高語句的重用率，減少語句的分析時間，通過了解Oracle SQL語句的分析過程可以明白Oracle的內部處理邏輯，並在設計與實現上避免。
在用JDBC或其它持久化數據(如Hibernate,JDO等)操作時，盡量用占位符（？）
4、解析過程圖
5、總結
ORACLE sql 的處理過程大致如下：
硬解析和軟解析都在第5步進行
硬解析通常是昂貴的操作，大約占整個SQL執行的70%左右的時間，硬解析會生成執行樹，執行計劃，等等。
當再次執行同一條SQL語句的時候，由於發現library cache中有相同的HASH值，這個時候不會硬解析，而會軟解析，
那麼軟解析究竟是干了什麼呢?其實軟解析就是跳過了生成解析樹，生成執行計劃這個耗時又耗CPU的操作，直接利用生成的執行計劃運行該SQL語句。
三、軟解析
1.下面的三個查詢語句，不能使用相同的共享SQL區。盡管查詢的表對象使用了大小寫，但Oracle為其生成了不同的執行計劃
select * from emp; select * from Emp; select * from EMP;
2.類似的情況，下面的查詢中，盡管其where子句empno的值不同，Oracle同樣為其生成了不同的執行計劃
select * from emp where empno=7369 select * from emp where empno=7788
3.在判斷是否使用硬解析時，所參照的對象及schema應該是相同的，如果對象相同，而schema不同，則需要使用硬解析,生成不同的執行計劃
sys@ASMDB> select owner,table_name from dba_tables where table_name like 'TB_OBJ%';
OWNER TABLE_NAME
------------------------------ ------------------------------
USR1 TB_OBJ --兩個對象的名字相同，當所有者不同
SCOTT TB_OBJ
usr1@ASMDB> select * from tb_obj;
scott@ASMDB> select * from tb_obj; --此時兩者都需要使用硬解析以及走不同的執行計劃
四、硬解析
硬 解析即整個SQL語句的執行需要完完全全的解析，生成執行計劃。而硬解析，生成執行計劃需要耗用CPU資源，以及SGA資源。在此不得不提的是對庫緩存中 闩的使用。闩是鎖的細化，可以理解為是一種輕量級的串行化設備。當進程申請到闩後，則這些闩用於保護共享內存的數在同一時刻不會被兩個以上的進程修改。在 硬解析時，需要申請闩的使用，而闩的數量在有限的情況下需要等待。大量的闩的使用由此造成需要使用闩的進程排隊越頻繁，性能則逾低下。
1. 下面對上面的兩種情形進行演示
在兩個不同的session中完成，一個為sys帳戶的session，一個為scott賬戶的session，不同的session，其SQL命令行以不同的帳戶名開頭
如" sys@ASMDB> " 表示使用時sys帳戶的session，" scott@ASMDB> "表示scott帳戶的session
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --當前的硬解析值為569
parse count (hard) 64 569
scott@ASMDB> select * from emp;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --執行上一個查詢後硬解析值為570，解析次數增加了一次
parse count (hard) 64 570
scott@ASMDB> select * from Emp;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --執行上一個查詢後硬解析值為571
parse count (hard) 64 571
scott@ASMDB> select * from EMP;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --執行上一個查詢後硬解析值為572
parse count (hard) 64 572
scott@ASMDB> select * from emp where empno=7369;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --執行上一個查詢後硬解析值為573
parse count (hard) 64 573
scott@ASMDB> select * from emp where empno=7788; --此處原來empno=7369，復制錯誤所致，現已更正為7788@20130905
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --執行上一個查詢後硬解析值為574
parse count (hard) 64 574
從上面的示例中可以看出，盡管執行的語句存在細微的差別，但Oracle還是為其進行了硬解析，生成了不同的執行計劃。即便是同樣的SQL語句，而兩條語句中空格的多少不一樣，Oracle同樣會進行硬解析。
五、硬解析改進 - 使用動態語句
1. 更改參數cursor_sharing
參數cursor_sharing決定了何種類型的SQL能夠使用相同的SQL area
CURSOR_SHARING = { SIMILAR | EXACT | FORCE }
EXACT --只有當發布的SQL語句與緩存中的語句完全相同時才用已有的執行計劃。
FORCE --如果SQL語句是字面量，則迫使Optimizer始終使用已有的執行計劃,無論已有的執行計劃是不是最佳的。
SIMILAR --如果SQL語句是字面量，則只有當已有的執行計劃是最佳時才使用它，如果已有執行計劃不是最佳則重新對這個SQL
--語句進行分析來制定最佳執行計劃。
可以基於不同的級別來設定該參數，如ALTER SESSION, ALTER SYSTEM
sys@ASMDB> show parameter cursor_shar --查看參數cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
sys@ASMDB> alter system set cursor_sharing='similar'; --將參數cursor_sharing的值更改為similar
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --當前硬解析的值為865
parse count (hard) 64 865
scott@ASMDB> select * from dept where deptno=10;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --執行上一條SQL查詢後，硬解析的值變為866
parse count (hard) 64 866
scott@ASMDB> select * from dept where deptno=20;
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- --執行上一條SQL查詢後，硬解析的值沒有發生變化還是866
parse count (hard) 64 866
sys@ASMDB> select sql_text,child_number from v$sql -- 在下面的結果中可以看到SQL_TEXT列中使用了綁定變量:"SYS_B_0"
2 where sql_text like 'select * from dept where deptno%';
SQL_TEXT CHILD_NUMBE
-------------------------------------------------- ------------
select * from dept where deptno=:"SYS_B_0" 0
sys@ASMDB> alter system set cursor_sharing='exact'; --將cursor_sharing改回為exact
--接下來在scott的session 中執行deptno=40 和的查詢後再查看sql_text，當cursor_sharing改為exact後，每執行那個一次
--也會在v$sql中增加一條語句
sys@ASMDB> select sql_text,child_number from v$sql
2 where sql_text like 'select * from dept where deptno%';
SQL_TEXT CHILD_NUMBER
-------------------------------------------------- ------------
select * from dept where deptno=50 0
select * from dept where deptno=40 0
select * from dept where deptno=:"SYS_B_0" 0
2. 使用綁定變量的方式
綁定變量要求變量名稱，數據類型以及長度是一致，否則無法使用軟解析
(1). 綁定變量（bind variable）是指在DML語句中使用一個占位符，即使用冒號後面緊跟變量名的形式，如下
select * from emp where empno=7788 --未使用綁定變量
select * from emp where empono=:eno --:eno即為綁定變量
在第二個查詢中，變量值在查詢執行時被提供。該查詢只編譯一次，隨後會把查詢計劃存儲在一個共享池（庫緩存）中，以便以後獲取和重用這個查詢計劃。
(2). 下面使用了綁定變量，但兩個變量其實質是不相同的，對這種情形，同樣使用硬解析
select * from emp where empno=:eno;
select * from emp where empno=:emp_no
使用綁定變量時要求不同的會話中使用了相同的回話環境，以及優化器的規則等
scott@ASMDB> create table tb_test(col int); --創建表tb_test
scott@ASMDB> create or replace procedure proc1 --創建存儲過程proc1使用綁定變量來插入新記錄
2 as
3 begin
4 for i in 1..10000
5 loop
6 execute immediate 'insert into tb_test values(:n)' using i;
7 end loop;
8 end;
9 /
Procedure created.
scott@ASMDB> create or replace procedure proc2 --創建存儲過程proc2，未使用綁定變量，因此每一個SQL插入語句都會硬解析
2 as
3 begin
4 for i in 1..10000
5 loop
6 execute immediate 'insert into tb_test values('||i||')';
7 end loop;
8 end;
9 /
Procedure created.
scott@ASMDB> exec runstats_pkg.rs_start
PL/SQL procedure successfully completed.
scott@ASMDB> exec proc1;
PL/SQL procedure successfully completed.
scott@ASMDB> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
scott@ASMDB> exec proc2;
PL/SQL procedure successfully completed.
scott@ASMDB> exec runstats_pkg.rs_stop(1000);
Run1 ran in 1769 hsecs
Run2 ran in 12243 hsecs --run2運行的時間是run1的/1769≈倍
run 1 ran in 14.45% of the time
Name Run1 Run2 Diff
LATCH.SQL memory manager worka 410 2,694 2,284
LATCH.session allocation 532 8,912 8,380
LATCH.simulator lru latch 33 9,371 9,338
LATCH.simulator hash latch 51 9,398 9,347
STAT...enqueue requests 31 10,030 9,999
STAT...enqueue releases 29 10,030 10,001
STAT...parse count (hard) 4 10,011 10,007 --硬解析的次數，前者只有四次
STAT...calls to get snapshot s 55 10,087 10,032
STAT...parse count (total) 33 10,067 10,034
STAT...consistent gets 247 10,353 10,106
STAT...consistent gets from ca 247 10,353 10,106
STAT...recursive calls 10,474 20,885 10,411
STAT...db block gets from cach 10,408 30,371 19,963
STAT...db block gets 10,408 30,371 19,963
LATCH.enqueues 322 21,820 21,498 --闩的隊列數比較
LATCH.enqueue hash chains 351 21,904 21,553
STAT...session logical reads 10,655 40,724 30,069
LATCH.library cache pin 40,348 72,410 32,062 --庫緩存pin
LATCH.kks stats 8 40,061 40,053
LATCH.library cache lock 318 61,294 60,976
LATCH.cache buffers chains 51,851 118,340 66,489
LATCH.row cache objects 351 123,512 123,161
LATCH.library cache 40,710 234,653 193,943
LATCH.shared pool 20,357 243,376 223,019
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
157,159 974,086 816,927 16.13% --proc2使用闩的數量也遠遠多於proc1，其比值是.13% PL/SQL procedure successfully completed.
(3). 使用綁定變量的好處
六、總結
1.盡可能的避免硬解析，因為硬解析需要更多的CPU資源，闩等。
2.cursor_sharing參數應權衡利弊，需要考慮使用similar與force帶來的影響。
3.盡可能的使用綁定變量來避免硬解析。
********************作者： 鮑新建********************
參考：樂沙彌大神 http://blog.csdn.net/leshami/article/details/6195483
http://10.61.208.50:15871/cgi-bin/blockpage.cgi?ws-session=18446744072512592920
http://czmmiao.iteye.com/category/143940
