分析執行計劃最佳化SQLSQL語句處理的過程(轉)

第1章 SQL語句處理的過程　　在調整之前我們需要了解一些背景知識，只有知道這些背景知識，我們才能更好的去調整sql語句。
本節介紹了SQL語句處理的基本過程，主要包括：
　　· 查詢語句處理
　　· DML語句處理(insert, update, delete)
　　· DDL 語句處理(create .. , drop .. , alter .. , )
　　· 事務控制(commit, rollback)

　　SQL 語句的執行過程(SQL Statement Execution)

　　 圖3-1 概要的列出了處理和執行一個sql語句的需要各個重要階段。在某些情況下，Oracle執行sql的過程可能與下面列出的各個階段的順序有所不同。如DEFINE階段可能在FETCH階段之前，這主要依賴你如何書寫程式碼。

　　對許多oracle的工具來說，其中某些階段會自動執行。絕大多數使用者不需要關心各個階段的細節問題，然而，知道執行的各個階段還是有必要的，這會幫助你寫出更高效的SQL語句來，而且還可以讓你猜測出效能差的SQL語句主要是由於哪一個階段造成的，然後我們針對這個具體的階段，找出解決的辦法。

　　圖 3-1 SQL語句處理的各個階段

　　DML語句的處理

　　本節給出一個例子來說明在DML語句處理的各個階段到底發生了什麼事情。假設你使用Pro*C程式來為指定部門的所有職員增加工資。程式已經連到正確的使用者，你可以在你的程式中嵌入如下的SQL語句：
EXEC SQL UPDATE employees
SET salary = 1.10 * salary WHERE department_id = :var_department_id; var_department_id是程式變數，裡面包含部門號，我們要修改該部門的職員的工資。當這個SQL語句執行時，使用該變數的值。

　　每種型別的語句都需要如下階段：
　　· 第1步: Create a Cursor 建立遊標
　　· 第2步: Parse the Statement 分析語句
　　· 第5步: Bind Any Variables 繫結變數
　　· 第7步: Run the Statement 執行語句
　　· 第9步: Close the Cursor 關閉遊標

　　如果使用了並行功能，還會包含下面這個階段：
　　· 第6步: Parallelize the Statement 並行執行語句

　　如果是查詢語句，則需要以下幾個額外的步驟，如圖 3所示：
　　· 第3步: Describe Results of a Query 描述查詢的結果集
　　· 第4步: Define Output of a Query 定義查詢的輸出資料
　　· 第8步: Fetch Rows of a Query 取查詢出來的行

　　下面具體說一下每一步中都發生了什麼事情：.

　　第1步: 建立遊標(Create a Cursor)

由程式介面呼叫建立一個遊標（cursor）。任何SQL語句都會建立它，特別在執行DML語句時，都是自動建立遊標的，不需要開發人員干預。多數應用中，遊標的建立是自動的。然而，在預編譯程式(pro*c)中游標的建立，可能是隱含的，也可能顯式的建立。在儲存過程中也是這樣的。

　　第2步:分析語句(Parse the Statement)

　　在語法分析期間，SQL語句從使用者程式傳送到Oracle，SQL語句經語法分析後，SQL語句本身與分析的資訊都被裝入到共享SQL區。在該階段中，可以解決許多型別的錯誤。

　　語法分析分別執行下列操作：
l 翻譯SQL語句，驗證它是合法的語句，即書寫正確
l 實現資料字典的查詢，以驗證是否符合表和列的定義
l 在所要求的物件上獲取語法分析鎖，使得在語句的語法分析過程中不改變這些物件的定義
l 驗證為存取所涉及的模式物件所需的許可權是否滿足
l 決定此語句最佳的執行計劃
l 將它裝入共享SQL區
l 對分佈的語句來說，把語句的全部或部分路由到包含所涉及資料的遠端節點

　　以上任何一步出現錯誤，都將導致語句報錯，中止執行。

　　只有在共享池中不存在等價SQL語句的情況下，才對SQL語句作語法分析。在這種情況下，資料庫核心重新為該語句分配新的共享SQL區，並對語句進行語法分析。進行語法分析需要耗費較多的資源，所以要儘量避免進行語法分析，這是最佳化的技巧之一。

　　語法分析階段包含了不管此語句將執行多少次，而只需分析一次的處理要求。Oracle只對每個SQL語句翻譯一次，在以後再次執行該語句時，只要該語句還在共享SQL區中，就可以避免對該語句重新進行語法分析，也就是此時可以直接使用其對應的執行計劃對資料進行存取。這主要是透過繫結變數(bind variable)實現的，也就是我們常說的共享SQL，後面會給出共享SQL的概念。

　　雖然語法分析驗證了SQL語句的正確性，但語法分析只能識別在SQL語句執行之前所能發現的錯誤(如書寫錯誤、許可權不足等)。因此，有些錯誤透過語法分析是抓不到的。例如，在資料轉換中的錯誤或在資料中的錯（如企圖在主鍵中插入重複的值）以及死鎖等均是隻有在語句執行階段期間才能遇到和報告的錯誤或情況。

　　查詢語句的處理

　　查詢與其它型別的SQL語句不同，因為在成功執行後作為結果將返回資料。其它語句只是簡單地返回成功或失敗，而查詢則能返回一行或許多行資料。查詢的結果均採用表格形式，結果行被一次一行或者批次地被檢索出來。從這裡我們可以得知批次的fetch資料可以降低網路開銷，所以批次的fetch也是最佳化的技巧之一。

有些問題只與查詢處理相關，查詢不僅僅指SELECT語句，同樣也包括在其它SQL語句中的隱含查詢。例如，下面的每個語句都需要把查詢作為它執行的一部分：
INSERT INTO table SELECT...
UPDATE table SET x = y WHERE...
DELETE FROM table WHERE...
CREATE table AS SELECT...

　　具體來說，查詢
· 要求讀一致性
· 可能使用回滾段作中間處理
· 可能要求SQL語句處理描述、定義和取資料階段

　　第3步: 描述查詢結果(Describe Results of a Query)

　　描述階段只有在查詢結果的各個列是未知時才需要；例如，當查詢由使用者互動地輸入需要輸出的列名。在這種情況要用描述階段來決定查詢結果的特徵（資料型別，長度和名字）。

　　第4步: 定義查詢的輸出資料(Define Output of a Query)

　　在查詢的定義階段，你指定與查詢出的列值對應的接收變數的位置、大小和資料型別，這樣我們透過接收變數就可以得到查詢結果。如果必要的話，Oracle會自動實現資料型別的轉換。這是將接收變數的型別與對應的列型別相比較決定的。

　　第5步: 繫結變數(Bind Any Variables)

　　此時，Oracle知道了SQL語句的意思，但仍沒有足夠的資訊用於執行該語句。Oracle 需要得到在語句中列出的所有變數的值。在該例中，Oracle需要得到對department_id列進行限定的值。得到這個值的過程就叫繫結變數(binding variables)

　　此過程稱之為將變數值捆綁進來。程式必須指出可以找到該數值的變數名（該變數被稱為捆綁變數，變數名實質上是一個記憶體地址，相當於指標）。應用的終端使用者可能並沒有發覺他們正在指定捆綁變數，因為Oracle 的程式可能只是簡單地指示他們輸入新的值，其實這一切都在程式中自動做了。因為你指定了變數名，在你再次執行之前無須重新捆綁變數。你可以改變繫結變數的值，而Oracle在每次執行時，僅僅使用記憶體地址來查詢此值。如果Oracle 需要實現自動資料型別轉換的話（除非它們是隱含的或預設的），你還必須對每個值指定資料型別和長度。關於這些資訊可以參考oracle的相關文件，如Oracle Call Interface Programmer's Guide

　　第6步: 並行執行語句(Parallelize the Statement )

　　ORACLE 可以在SELECTs, INSERTs, UPDATEs, MERGEs, DELETEs語句中執行相應並行查詢操作，對於某些DDL操作，如建立索引、用子查詢建立表、在分割槽表上的操作，也可以執行並行操作。並行化可以導致多個伺服器程式(oracle server processes)為同一個SQL語句工作，使該SQL語句可以快速完成，但是會耗費更多的資源，所以除非很有必要，否則不要使用並行查詢。

　　第7步: 執行語句(Run the Statement)

　　到了現在這個時候，Oracle擁有所有需要的資訊與資源，因此可以真正執行SQL語句了。如果該語句為SELECT查詢或INSERT語句，則不需要鎖定任何行，因為沒有資料需要被改變。然而，如果語句為UPDATE或DELETE語句，則該語句影響的所有行都被鎖定，防止該使用者提交或回滾之前，別的使用者對這些資料進行修改。這保證了資料的一致性。對於某些語句，你可以指定執行的次數，這稱為批處理(array processing)。指定執行N次，則繫結變數與定義變數被定義為大小為N的陣列的開始位置，這種方法可以減少網路開銷，也是最佳化的技巧之一。

　　第8步: 取出查詢的行(Fetch Rows of a Query)

　　在fetch階段，行資料被取出來，每個後續的存取操作檢索結果集中的下一行資料，直到最後一行被取出來。上面提到過，批次的fetch是最佳化的技巧之一。

　　第9步: 關閉遊標(Close the Cursor)

　　SQL語句處理的最後一個階段就是關閉遊標

　　DDL語句的處理(DDL Statement Processing)

　　DDL語句的執行不同與DML語句和查詢語句的執行，這是因為DDL語句執行成功後需要對資料字典資料進行修改。對於DDL語句，語句的分析階段實際上包括分析、查詢資料字典資訊和執行。事務管理語句、會話管理語句、系統管理語句只有分析與執行階段，為了重新執行該語句，會重新分析與執行該語句。

　　事務控制(Control of Transactions)

　　一般來說，只有使用ORACLE程式設計介面的應用設計人員才關心操作的型別，並把相關的操作組織在一起，形成一個事務。一般來說，我門必須定義事務，這樣在一個邏輯單元中的所有工作可以同時被提交或回滾，保證了資料的一致性。一個事務應該由邏輯單元中的所有必須部分組成，不應該多一個，也不應該少一個。
　　· 在事務開始和結束的這段時間內，所有被引用表中的資料都應該在一致的狀態(或可以被回溯到一致的狀態)
　　· 事務應該只包含可以對資料進行一致更改(one consistent change to the data)的SQL語句

　　例如，在兩個帳號之間的轉帳(這是一個事務或邏輯工作單元)，應該包含從一個帳號中借錢(由一個SQL完成)，然後將借的錢存入另一個帳號(由另一個SQL完成)。這2個操作作為一個邏輯單元，應該同時成功或同時失敗。其它不相關的操作，如向一個帳戶中存錢，不應該包含在這個轉帳事務中。

　　在設計應用時，除了需要決定哪種型別的操作組成一個事務外，還需要決定使用BEGIN_DISCRETE_TRANSACTIO儲存過程是否對提高小的、非分散式的事務的效能有作用。

[@more@]