Oracle SQL的硬解析和軟解析

    我們都知道在Oracle中，每條SQL語句在執行之前都需要經過解析，這裡面又分為軟解析和硬解析。在Oracle中存在兩種型別的SQL語句，一類為 DDL語句(資料定義語言，例如CREATE,DROP,ALTER)，他們是從來不會共享使用的，也就是每次執行都需要進行硬解析。還有一類就是DML語句(資料操縱語言，例如INSERT,UPDATE,DELETE,SELECT)，他們會根據情況選擇要麼進行硬解析，要麼進行軟解析。

一、SQL 解析過程

Oracle對此SQL將進行幾個步驟的處理過程：

    1、語法檢查(syntax check): 檢查此sql的拼寫是否語法。

    2、語義檢查(semantic check): 諸如檢查sql語句中的訪問物件是否存在及該使用者是否具備相應的許可權。

    3、對sql語句進行解析(prase): 利用內部演算法對sql進行解析，生成解析樹(parse tree)及執行計劃(execution plan)。

    4、執行sql，返回結果(execute and return)。

二、解析過程詳解

2.1 語法檢測

判斷一條SQL語句的語法是否符合SQL的規範，比如執行：

SQL> selet * from test;

我們就可以看出由於Select關鍵字少了一個“c”，這條語句就無法透過語法檢驗的步驟了。

2.2 語義檢查

語法正確的SQL語句在解析的第二個步驟就是判斷該SQL語句所訪問的表及列是否準確？使用者是否有許可權訪問或更改相應的表或列？ 比如如下語句：

SQL> select * from test;

select * from test

*

ERROR at line 1:

ORA-00942: table or view does not exist

由於查詢使用者沒有可供訪問的test物件，因此該SQL語句無法透過語義檢查。

2.3 解析(Parse)

  2.3.1 Parse主要分為三種：

    1、Hard Parse (硬解析)

    2、Soft Parse (軟解析)

    3、Soft Soft Parse(好像有些資料中並沒有將這個算在其中)

   Hard Parse：就是上面提到的對提交的Sql完全重新從頭進行解析(當在Shared Pool中找不到時候將會進行此操作)，總共有以下5個執行步驟：

    1：語法分析

    2：許可權與物件檢查

    3：在共享池中檢查是否有完全相同的之前完全解析好的。如果存在，直接跳過4和5，執行Sql, 此時算soft parse。

    4：選擇執行計劃

    5：產生執行計劃

注：建立解析樹、生成執行計劃對於sql的執行來說是開銷昂貴的動作，所以，應當極力避免硬解析，儘量使用軟解析。這就是在很多專案中，倡導開發設計人員對功能相同的程式碼要努力保持程式碼的一致性，以及要在程式中多使用繫結變數的原因。

Soft Parse：如果在Shared Pool中找到了與之完全相同的Sql解析好的結果後，會跳過Hard Parse中的後面的兩個步驟。

    Soft Soft Parse：實際上是當設定了session_cursor_cache這個引數之後，Cursor被直接Cache在當前Session的PGA中的，在解析的時候只需要對其語法分析、許可權物件分析之後就可以轉到PGA中查詢了，如果發現完全相同的Cursor，就可以直接去取結果了，也就是實現了Soft Soft Parse。

   2.3.2 解析的步驟可以分為兩個步驟：

         1)　驗證SQL語句是否完全一致。

在這個步驟中，Oracle將會對傳遞進來的SQL語句使用HASH函式運算得出HASH值，再與共享池中現有語句的HASH值進行比較看是否一一對應。現有資料庫中SQL語句的HASH值我們可以透過訪問v$sql、v$sqlarea、v$sqltext等資料字典中的HASH_VALUE列查詢得出。

如果SQL語句的HASH值一致，那麼ORACLE事實上還需要對SQL語句的語義進行再次檢測，以決定是否一致。那麼為什麼Oracle需要再次對語句文字進行檢測呢？不是SQL語句的HASH值已經對應上了？事實上就算是SQL語句的HASH值已經對應上了，並不能說明這兩條SQL語句就已經可以共享了。

例如：假如使用者SYS有自己的一張表EMP，他要執行查詢語句：select * from emp; 使用者SYSTEM也有一張EMP表，同樣要查詢select * from emp；這樣他們兩條語句在文字上是一模一樣的，他們的HASH值也會一樣，但是由於涉及到查詢的相關表不一樣，他們事實上是無法共享的. 

SQL> conn / as sysdba

已連線。

SQL> show user

USER 為 "SYS"

SQL>  create table emp ( x int ) ;

表已建立。

SQL> select * from emp;

未選定行

SQL> conn system/admin;

已連線。

SQL>  create table emp ( x int );

表已建立。

SQL> select * from emp;

未選定行

SQL> select address,hash_value, executions, sql_text from v$sql where upper(sql_text) like 'SELECT * FROM EMP%';

ADDRESS      HASH_VALUE  EXECUTIONS    SQL_TEXT                                                                

-----------------------  ---------------------------------------------------------

2769AE64    1745700775     1         select * from emp                                                                                                                         

2769AE64    1745700775     1         select * from emp                                                    

2 rows selected.

從結果可以看到這2個查詢的語句文字和HASH值都是一樣的，但是由於查詢的物件不同，是無法共享的，不同情況的語句還是需要硬解析的。因此在檢查共享池共同SQL語句的時候，是需要根據具體情況而定的。

    可以進一步查詢v$sql_shared_cursor以得知SQL為何不能共享的原因：

SQL>select address,auth_check_mismatch,translation_mismatch,optimizer_mismatch 

from v$sql_shared_cursor where address in ( 

select address from v$sql where upper(sql_text) like 'SELECT * FROM EMP%' )  

ADDRESS     A T O

----------------  ----- -- -- 

2769AE64     N N N

2769AE64     Y Y N

TRANSLATION_MISMATCH 表示SQL遊標涉及到的資料物件是不同的；

AUTH_CHECK_MISMATCH 表示對同樣一條SQL語句轉換是不匹配的。

optimizer_mismatch 表示會話的最佳化器環境是不同的。

         2)  驗證SQL語句執行環境是否相同

比如同樣一條SQL語句，一個查詢會話加了/*+ first_rows */的HINT，另外一個使用者加/*+ all_rows */的HINT，他們就會產生不同的執行計劃，儘管他們是查詢同樣的資料。

透過如上檢查以後，如果SQL語句是一致的，那麼就會重用原有SQL語句的執行計劃和最佳化方案，也就是我們通常所說的軟解析。如果SQL語句沒有找到同樣的副本，那麼就需要進行硬解析了。

Oracle根據提交的SQL語句再查詢相應的資料物件是否有統計資訊。如果有統計資訊的話，那麼CBO將會使用這些統計資訊產生所有可能的執行計劃(可能多達成千上萬個)和相應的Cost，最終選擇Cost最低的那個執行計劃。如果查詢的資料物件無統計資訊，則按RBO的預設規則選擇相應的執行計劃。這個步驟也是解析中最耗費資源的，因此我們應該極力避免硬解析的產生。至此，解析的步驟已經全部完成，Oracle將會根據解析產生的執行計劃執行SQL語句和提取相應的資料。 

   2.4  執行sql，返回結果(execute and return)

三、繫結變數

    使用了Bind Var能提高效能主要是因為這樣做可以儘量避免不必要的硬分析(Hard Parse)而節約了時間，同時節約了大量的CPU資源。

    當一個Client提交一條Sql給Oracle後，Oracle 首先會對其進行解析(Parse)，然後將解析結果提交給最佳化器(Optimiser)來進行最佳化而取得Oracle認為的最優的Query Plan，然後再按照這個最優的Plan來執行這個Sql語句(當然在這之中如果只需要軟解析的話會少部分步驟)。

但是，當Oracle接到 Client提交的Sql後會首先在共享池(Shared Pool)裡面去查詢是否有之前已經解析好的與剛接到的這一個Sql完全相同的Sql(注意這裡說的是完全相同，既要求語句上的字元級別的完全相同，又要求涉及的物件也必須完全相同)。當發現有相同的以後解析器就不再對新的Sql在此解析而直接用之前解析好的結果了。這裡就節約瞭解析時間以及解析時候消耗的CPU資源。尤其是在OLTP中執行著的大量的短小Sql，效果就會比較明顯了。因為一條兩條Sql的時間可能不會有多少感覺，但是當量大了以後就會有比較明顯的感覺了。