Oracle中SQL語句運行過程的深度解析

最近項目做完了（也許並不能說做完了），但是出現了一個致命的障礙，就是效能。現在想想TOP的作者是講的是多麼的精闢，效能是貫穿在項目需求，項目分析設計，項目編碼和單元測試、整合等各個階段。而不是等項目開發完畢，再來考慮效能問題。但是，事實已是如此。所以，最近先從SQL語句開始最佳化， 為了更好地最佳化SQL，那麼首先自然需要知道Oracle中的SQL引擎對一條給定的SQL語句，他都做了哪些鮮為人知的事情。本文為個人學習總結，僅作學習記錄，其中不妥或者錯誤之處，敬請指出，本人感激不盡！

為了延續TOP中從Cursor開始說明，這裡的Cursor並非PL/SQL中的Cursor，至於這個Cursor是什麼，怎麼用中文解釋，暫時還不清楚。但是我想他其實是一個概念性的東西，代表了SQL在不同環境中的稱謂。頑皮點地說，當一條SQL語句進入了SQL引擎，其就被Cursor了。下面看一張TOP上的Cursor的生命週期：

1:Open Cursor：在本次會話的伺服器處理序的私人記憶體UGA（User Global Area）中會開闢一個記憶體區給這個Cursor。此時，SQL語句還沒有和該Cursor相關聯。

2:Parse Cursor：這個過程就會讓SQL語句和Cursor相關聯了。這個步驟就是通常意義的SQL解析，最後將執行計畫存入共用區。然後UGA中的Cursor會用一個指標指向共用區的一個可共用的Cursor。

3:Define output variables：定義輸出變數主要是對於查詢和使用了returning文法的insert，update，和delete語句。

4:Bind input variables：如果SQL使用了綁定變數，則這裡需要擷取綁定變數的值。

5:Execute cursor：執行，但是SQL語句真正的執行往往會延遲到下個步驟。

6:Fetch cursor：如果sql返回資料，則這裡擷取返回的資料。

7:Close cursor：關閉cursor，釋放UGA中佔用的資源。但是共用區中的cursor並不會釋放。

在上面這個過程中，對於我們關心的SQL語句的解析，一筆帶過。下面繼續分析第二個步驟Parse Cursor。對於SQL語句的解析，早就聽說是軟解析和硬解析，下面我們分析下整個解析的過程：

1、整個解析的過程從包含VPD的謂詞開始。如果該SQL中含有應用了VPD(Virtual Private Database)的表，則將會首先在SQL語句的where中附加上VPD安全性原則的謂詞。

2、文法、語義檢查和許可權控制。語法檢查，對sql語句關鍵字的正確性進行檢查，語義檢查主要判斷該SQL中是否引用了不存在的對象或者是否違反了相關約束條件；許可權控制

主要是判斷當前操作是否具備相應的操作許可權。

3、從共用區中擷取一個Parent Cursor，如果未擷取到，則在共用區中分配記憶體，定義一個新的Parent Cursor。這個Parent Cursor結構的關鍵資訊就是這個SQL語句本身。

4、邏輯最佳化，這個過程應用各種不同的演算法和策略對SQL語句進行轉換，產生很多等價的SQL。

5、物理最佳化，首先將上個步驟中各SQL語句產生各自的執行計畫，然後應用系統資料字典中的分析統計資訊或者通過動態採樣擷取的統計資訊為每個執行計畫計算出一個Cost，然後選擇最小Cost的執行計畫。

6、在共用區中儲存一個Child Cursor，這個Child Cursor和上面Parent Cursor是關聯的，Child Cursor中主要儲存執行計畫和運行環境資訊。

通過上面，可以看到，在共用區中有一個Parent Cursor和Child Cursor來儲存一個SQL語句的資訊，通過查詢v$sqlarea,v$sql,v$sqltext等視圖，我們可以擷取sql和cursor的相關資訊。

對於上面的各個過程，我們看到，Parent Cursor和Child Cursor是在共用區的，也就是說其可以被共用，嘿嘿，這就是為什麼有軟解析和硬解析了。

軟解析：如果Parent Cursor和Child Cursor在共用區中已經存在，則只需要前兩個步驟就行了。這個就是軟解析。

硬解析：如果Parent Cursor和Child Cursor不能共用，則需要完整的過程，這個就是硬解析了。

下面通過一個例子來說明關於共用的機制和需要注意的問題。

首先，對於如下幾個查詢語句

select * from t where n = 1024;

Select * from t where n = 1024;

select  *  from t where n = 1024;

select * from t where n = 1024;

執行上面四條查詢語句，然後，我們查看v$sqlarea視圖發現，第一條和第四條是相同的，只需要硬解析一次，當第二次執行的時候就不需要硬解析了，所以第一條語句的執行次數是2。這裡就說明SQL語句必須完全相同才能共用Parent Cursor。

select sql_id,sql_text,executions from v$sqlarea where  sql_text like '%n=1024%';結果就不貼了。

綁定變數：對於綁定變數，其對於開發來說，或許會增加其代碼量。從效能的角度來說，其對OLTP系統的影響巨大，很多系統的癱瘓，歸根於此。比如，對於如下的查詢語句，select * from t where n = 1024;如果在程式中，我們構造SQL語句的時候，使用如下的代碼：

private String buildSQL(int n){

StringBuilder sb = new StringBuilder("select * from t where n = ");

sb.append(n);

return sb.toString();

}

那麼，如果應用程式中這個n假如有10萬個不同的值，如果這些都被執行了，則在共用區就會儲存10萬條共用記錄。因為對於每個不同的值，其SQL就是兩條完全不同的SQL。

所以，建議在OLTP系統當中儘可能使用綁定變數的方式構造SQL語句。但是綁定變數，也會造成執行計畫可能並非最優的。比如：

select * from t where c < 1000;

如果這個查詢的結果記錄數為總資料的90%，則執行計畫將會選擇全表掃描，而不會走索引。

select * from t where c < 10;

對於這個查詢，最佳化器肯定會走索引，而不會走全表掃描。

但是當使用綁定變數的時候，查詢語句就是select * from t where c < :c ;這樣最佳化器並不能知道當前查詢的記錄數的情況，所以，在產生執行計畫的時候，可能最後選擇的執行計畫並不是最優的。

關於這個，在9i中好像得到了改善，增加了bind variable peeking，在硬解析的過程中會將綁定變數的值加上，再產生執行計畫。