Oracle的軟解析(soft prase)和硬解析(hard prase)

說到軟解析(soft prase)和硬解析(hard prase)，就不能不說一下Oracle對sql的處理過程。當你發出一條sql語句交付Oracle，在執行和獲取結果前，Oracle對此sql將進行幾個步驟的處理過程：

1、語法檢查(syntax check)
檢查此sql的拼寫是否語法。
2、語義檢查(semantic check)
諸如檢查sql語句中的訪問物件是否存在及該使用者是否具備相應的許可權。
3、對sql語句進行解析(prase)
利用內部演算法對sql進行解析，生成解析樹(parse tree)及執行計劃(execution plan)。
4、執行sql，返回結果(execute and return)

其中，軟、硬解析就發生在第三個過程裡。

Oracle利用內部的hash演算法來取得該sql的hash值，然後在library cache裡查詢是否存在該hash值；
假設存在，則將此sql與cache中的進行比較；
假設"相同"，就將利用已有的解析樹與執行計劃，而省略了優化器的相關工作。這也就是軟解析的過程。

誠然，如果上面的2個假設中任有一個不成立，那麼優化器都將進行建立解析樹、生成執行計劃的動作。這個過程就叫硬解析。

建立解析樹、生成執行計劃對於sql的執行來說是開銷昂貴的動作，所以，應當極力避免硬解析，儘量使用軟解析。

這就是在很多專案中，倡導開發設計人員對功能相同的程式碼要努力保持程式碼的一致性，以及要在程式中多使用繫結變數的原因。

有如下兩句查詢語句：

1.SELECT * FROM EMP WHERE EMPNO = 123;

2.SELECT * FROM EMP WHERE EMPNO = :EMP_NO;

1句中查詢員工編號是123的員工資訊，ORACLE第一次經過分析編譯後執行。但如果下次還要再查詢編號為456和789的員工資訊時，ORACLE將會再將這句SQL分析編譯，然後再執行。

再看2句，首先定義變數EMP_NO，我們將123賦給變數，第一次的時候也是經過分析編譯後再執行，但是到了接下來再想查詢其他員工編號的資訊時，ORACLE會將第一次編譯後的查詢方案(在第一次編譯執行之後已經儲存在共享池中)用來進行下一次的查詢。

這就像JAVA，想想看，如果你用JAVA寫了一個軟體，給客戶的是你寫的JAVA程式碼，客戶在每次使用的時候都耀編譯程式碼，然後執行。這將會影響多大啊。

所以說，分析一個帶有硬編碼變數的語句(稱為硬分析)要明顯的比重用一個已經分析過的查詢方案(軟分析)要花費更長的時間和耗費更多的資源。

如果使用繫結變數，提交引用相同變數的完全相同的查詢的人將會使用共享池中的編譯方案，只需編譯子例程一次，就可以重複使用。這樣不僅可以使用較少的時間，而且可以減少鎖存時間，降低鎖存頻率。這將會提高軟體效能，大大提高可伸縮性。

下面的試驗將更能說明這個道理：

ALTER SYSTEM FLUSH SHARED_POOL;

SET SERVEROUTPUT ON;

SET TIMING ON;

DECLARE
          TYPE rc IS REF CURSOR;
          l_rc rc;
          l_dummy all_objects.object_name%TYPE;
          l_start NUMBER DEFAULT dbms_utility.get_time;
BEGIN
          FOR i IN 1 .. 1000 LOOP
          OPEN l_rc FOR 'select object_name from all_objects where object_id = '||i;
          FETCH l_rc INTO l_dummy;
          CLOSE l_rc;
          END LOOP;
          dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds ...');     
END;

PL/SQL 過程已成功完成。

已用時間: 00: 00: 53.05

上述程式碼使用動態SQL從ALL_OBJECTS表中查詢單行。它用值1，2，3…1000等硬編碼產生1000條不同的查詢進入WHERE子句，看看執行時間53秒。
再看下面程式碼：

DECLARE
        TYPE rc IS REF CURSOR;
        l_rc rc;
        l_dummy all_objects.object_name%TYPE;
        l_start NUMBER DEFAULT dbms_utility.get_time;
BEGIN
        FOR i IN 1 .. 1000 LOOP
        OPEN l_rc FOR 'select object_name from all_objects where object_id = :x' USING i;
        FETCH l_rc INTO l_dummy;
        CLOSE l_rc;
        END LOOP;
        dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2)|| 'seconds ...'); 
END;

PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.03

！!!! 0.03秒，差距竟然這麼大，回頭看程式碼，第二次只是在迴圈體中使用了變數X，將i的值賦給了X，這樣一來，ORACLE在執行的時候只需要編譯一次，其他999次都是從共享池中使用查詢方案，查詢時間和速度當然更快了。
所以。從軟體開發的一開始就要認識到繫結變數的重要性。