Oracle資料庫SQL語句執行過程

1 、使用者程式在客戶端執行 SQL 語句時，客戶端會把這條 SQL 語句傳送給伺服器端，讓伺服器端的程式來處理這語句。也就是說， Oracle 客戶端是不會做任何的操作，他的主要任務就是把客戶端產生的一些 SQL 語句傳送給伺服器端。

2 、伺服器程式從使用者程式把資訊接收到後，在 PGA 中就要此程式分配所需記憶體，儲存相關的資訊 , 如在會話記憶體儲存相關的登入資訊等。雖然在客戶端也有一個資料庫程式，但是，這個程式的作用跟伺服器上的程式作用是不相同的，伺服器上的資料庫程式才會對 SQL 語句進行相關的處理。當然客戶端的程式跟伺服器的程式是一一對應的。也就是說，在客戶端連線上伺服器後，在客戶端與伺服器端都會形成一個程式，客戶端上的我們叫做客戶端程式，而伺服器上的我們叫做伺服器程式。

3 、當客戶端把 SQL 語句傳送到伺服器後，伺服器程式會對該語句進行解析。這個解析的工作是在伺服器端所進行的，解析過程又可細化。

1 ）查詢快取記憶體（ library cache ）

伺服器程式在接到客戶端傳送過來的 SQL 語句時，不會直接去資料庫查詢。伺服器程式把這個 SQL 語句的字元轉化為 ASCII 等效數字碼，接著這個 ASCII 碼被傳遞給一個 HASH 函式，並返回一個 hash 值，然後伺服器程式將到 shared pool 中的 library cache （快取記憶體）中去查詢是否存在相同的 hash 值。如果存在，伺服器程式將使用這條語句已快取記憶體在 shared pool 的 library cache 中的已分析過的版本來執行，省去後續的解析工作，這便是軟解析。若快取記憶體中不存在，則需要進行後面的步驟，這便是硬解析。硬解析通常是昂貴的操作，大約佔整個 SQL 執行的 70% 左右的時間，硬解析會生成執行樹，執行計劃，等等。

所以，採用高速資料快取的話，可以提高 SQL 語句的查詢效率。其原因有兩方面：一方面是從記憶體中讀取資料要比從硬碟中的資料檔案中讀取資料效率要高，另一方面也是因為避免語句解析而節省了時間。

不過這裡要注意一點，這個資料快取跟有些客戶端軟體的資料快取是兩碼事。有些客戶端軟體為了提高查詢效率，會在應用軟體的客戶端設定資料快取。由於這些資料快取的存在，可以提高客戶端應用軟體的查詢效率。但是，若其他人在伺服器進行了相關的修改，由於應用軟體資料快取的存在，導致修改的資料不能及時反映到客戶端上。從這也可以看出，應用軟體的資料快取跟資料庫伺服器的高速資料快取不是一碼事。

2 ）語句合法性檢查（ data dictionary cache ）

當在快取記憶體中找不到對應的 SQL 語句時，則伺服器程式就會開始檢查這條語句的合法性。這裡主要是對 SQL 語句的語法進行檢查，看看其是否合乎語法規則。如果伺服器程式認為這條 SQL 語句不符合語法規則的時候，就會把這個錯誤資訊反饋給客戶端。在這個語法檢查的過程中，不會對 SQL 語句中所包含的表名、列名等等進行檢查，只是檢查語法。

3 ）語言含義檢查（ data dictionary cache ）

若 SQL 語句符合語法上的定義的話，則伺服器程式接下去會對語句中涉及的表、索引、檢視等物件進行解析，並對照資料字典檢查這些物件的名稱以及相關結構，看看這些欄位、表、檢視等是否在資料庫中。如果表名與列名不準確的話，則資料庫會就會反饋錯誤資訊給客戶端。

所以，有時候我們寫 select 語句的時候，若語法與表名或者列名同時寫錯的話，則系統是先提示說語法錯誤，等到語法完全正確後再提示說列名或表名錯誤。

4 ）獲得物件解析鎖（ control structer ）

當語法、語義都正確後，系統就會對我們需要查詢的物件加鎖。這主要是為了保障資料的一致性，防止我們在查詢的過程中，其他使用者對這個物件的結構發生改變。

5 ）資料訪問許可權的核對（ data dictionary cache ）

當語法、語義通過檢查之後，客戶端還不一定能夠取得資料，伺服器程式還會檢查連線使用者是否有這個資料訪問的許可權。若使用者不具有資料訪問許可權的話，則客戶端就不能夠取得這些資料。要注意的是資料庫伺服器程式先檢查語法與語義，然後才會檢查訪問許可權。

6 ）確定最佳執行計劃

當語法與語義都沒有問題許可權也匹配，伺服器程式還是不會直接對資料庫檔案進行查詢。伺服器程式會根據一定的規則，對這條語句進行優化。在執行計劃開發之前會有一步查詢轉換，如：檢視合併、子查詢解巢狀、謂語前推及物化檢視重寫查詢等。為了確定採用哪個執行計劃， Oracle 還需要收集統計資訊確定表的訪問聯結方法等，最終確定可能的最低成本的執行計劃。

不過要注意，這個優化是有限的。一般在應用軟體開發的過程中，需要對資料庫的 sql 語句進行優化，這個優化的作用要大大地大於伺服器程式的自我優化。

當伺服器程式的優化器確定這條查詢語句的最佳執行計劃後， 就會將這條 SQL 語句與執行計劃儲存到資料快取記憶體（ library cache ）。如此，等以後還有這個查詢時，就會省略以上的語法、語義與許可權檢查的步驟，而直接執行 SQL 語句，提高 SQL 語句處理效率。

4 、繫結變數賦值

如果 SQL 語句中使用了繫結變數，掃描繫結變數的宣告，給繫結變數賦值，將變數值帶入執行計劃。若在解析的第一個步驟， SQL 在高速緩衝中存在，則直接跳到該步驟。

5 、語句執行

語句解析只是對 SQL 語句的語法進行解析，以確保伺服器能夠知道這條語句到底表達的是什麼意思。等到語句解析完成之後，資料庫伺服器程式才會真正的執行這條 SQL 語句。

對於 SELECT 語句：

1 ）首先伺服器程式要判斷所需資料是否在 db buffer 存在，如果存在且可用，則直接獲取該資料而不是從資料庫檔案中去查詢資料，同時根據 LRU 演算法增加其訪問計數；

2 ）若資料不在緩衝區中，則伺服器程式將從資料庫檔案中查詢相關資料，並把這些資料放入到資料緩衝區中（ buffer cache ）。

其中，若資料存在於 db buffer ，其可用性檢查方式為：檢視 db buffer 塊的頭部是否有事務，如果有事務，則從回滾段中讀取資料；如果沒有事務，則比較 select 的 scn 和 db buffer 塊頭部的 scn ，如果前者小於後者，仍然要從回滾段中讀取資料；如果前者大於後者，說明這是一非髒快取，可以直接讀取這個 db buffer 塊的中內容。

對於 UPDATE 語句（ insert 、 delete 、 update ）：

1 ）檢查所需的資料庫是否已經被讀取到緩衝區快取中。如果已經存在緩衝區快取，則直接執行步驟 3 ；

2 ）若所需的資料庫並不在緩衝區快取中，則伺服器將資料塊從資料檔案讀取到緩衝區快取中；

3 ）對想要修改的表取得的資料行鎖定（ Row Exclusive Lock ），之後對所需要修改的資料行取得獨佔鎖；

4 ）將資料的 Redo 記錄複製到 redo log buffer ；

5 ）產生資料修改的 undo 資料；

6 ）修改 db buffer ；

7 ） dbwr 將修改寫入資料檔案；

其中，第 2 步，伺服器將資料從資料檔案讀取到 db buffer 經經歷以下步驟：

a ）首先伺服器程式將在表頭部請求 TM 鎖（保證此事務執行過程其他使用者不能修改表的結構），如果成功加 TM 鎖，再請求一些行級鎖（ TX 鎖），如果 TM 、 TX 鎖都成功加鎖，那麼才開始從資料檔案讀資料。

 

b ）在讀資料之前，要先為讀取的檔案準備好 buffer 空間。伺服器程式需要掃描 LRU list 尋找 free db buffer ，掃描的過程中，伺服器程式會把發現的所有已經被修改過的 db buffer 註冊到 dirty list 中。如果 free db buffer 及非髒資料塊緩衝區不足時，會觸發 dbwr 將 dirty buffer 中指向的緩衝塊寫入資料檔案，並且清洗掉這些緩衝區來騰出空間緩衝新讀入的資料。

c ）找到了足夠的空閒 buffer ，伺服器程式將從資料檔案中讀入這些行所在的每一個資料塊（ db block ）（ DB BLOCK 是 ORACLE 的最小操作單元，即使你想要的資料只是 DB BLOCK 中很多行中的一行或幾行， ORACLE 也會把這個 DB BLOCK 中的所有行都讀入 Oracle DB BUFFER 中）放入 db buffer 的空閒的區域或者覆蓋已被擠出 LRU list 的非髒資料塊緩衝區，並且排列在 LRU 列表的頭部，也就是在資料塊放入 db buffer 之前也是要先申請 db buffer 中的鎖存器，成功加鎖後，才能讀資料到 db buffer 。

若資料塊已經存在於 db buffer cache （有時也稱 db buffer 或 db cache ），即使在 db buffer 中找到一個沒有事務，而且 SCN 比自己小的非髒快取資料塊，伺服器程式仍然要到表的頭部對這條記錄申請加鎖，加鎖成功才能進行後續動作，如果不成功，則要等待前面的程式解鎖後才能進行動作（這個時候阻塞是 tx 鎖阻塞）。

在記 redo 日誌時，其具體步驟如下：

1 ）資料被讀入到 db buffer 後，伺服器程式將該語句所影響的並被讀入 db buffer 中的這些行資料的 rowid 及要更新的原值和新值及 scn 等資訊從 PGA 逐條的寫入 redo log buffer 中。在寫入 redo log buffer 之前也要事先請求 redo log buffer 的鎖存器，成功加鎖後才開始寫入。

2 ）當寫入達到 redo log buffer 大小的三分之一或寫入量達到 1M 或超過三秒後或發生檢查點時或者 dbwr 之前發生，都會觸發 lgwr 程式把 redo log buffer 的資料寫入磁碟上的 redo file 檔案中（這個時候會產生 log file sync 等待事件）。

3 ）已經被寫入 redo file 的 redo log buffer 所持有的鎖存器會被釋放，並可被後來的寫入資訊覆蓋， redo log buffer 是迴圈使用的。 Redo file 也是迴圈使用的，當一個 redo file 寫滿後， lgwr 程式會自動切換到下一 redo file （這個時候可能出現 log file switch （ check point complete ）等待事件）。如果是歸檔模式，歸檔程式還要將前一個寫滿的 redo file 檔案的內容寫到歸檔日誌檔案中（這個時候可能出現 log file switch （ archiving needed ）。

在為事務建立 undo 資訊時，其具體步驟如下：

1 ）在完成本事務所有相關的 redo log buffer 之後，伺服器程式開始改寫這個 db buffer 的塊頭部事務列表並寫入 scn （一開始 scn 是寫在 redo log buffer 中的，並未寫在 db buffer ）。

2 ）然後 copy 包含這個塊的頭部事務列表及 scn 資訊的資料副本放入回滾段中，將這時回滾段中的資訊稱為資料塊的“前映像”，這個“前映像”用於以後的回滾、恢復和一致性讀。（回滾段可以儲存在專門的回滾表空間中，這個表空間由一個或多個物理檔案組成，並專用於回滾表空間，回滾段也可在其它表空間中的資料檔案中開闢）。

在修改資訊寫入資料檔案時，其具體步驟如下：

1 ）改寫 db buffer 塊的資料內容，並在塊的頭部寫入回滾段的地址。

2 ）將 db buffer 指標放入 dirty list 。如果一個行資料多次 update 而未 commit ，則在回滾段中將會有多個“前映像”，除了第一個“前映像”含有 scn 資訊外，其他每個 " 前映像 " 的頭部都有 scn 資訊和 " 前前映像 " 回滾段地址。一個 update 只對應一個 scn ，然後伺服器程式將在 dirty list 中建立一條指向此 db buffer 塊的指標（方便 dbwr 程式可以找到 dirty list 的 db buffer 資料塊並寫入資料檔案中）。接著伺服器程式會從資料檔案中繼續讀入第二個資料塊，重複前一資料塊的動作，資料塊的讀入、記日誌、建立回滾段、修改資料塊、放入 dirty list 。

3 ）當 dirty queue 的長度達到閥值（一般是 25% ），伺服器程式將通知 dbwr 把髒資料寫出，就是釋放 db buffer 上的鎖存器，騰出更多的 free db buffer 。前面一直都是在說明 oracle 一次讀一個資料塊，其實 oracle 可以一次讀入多個資料塊（ db_file_multiblock_read_count 來設定一次讀入塊的個數）

當執行 commit 時，具體步驟如下：

1 ） commit 觸發 lgwr 程式，但不強制 dbwr 立即釋放所有相應 db buffer 塊的鎖。也就是說有可能雖然已經 commit 了，但在隨後的一段時間內 dbwr 還在寫這條 sql 語句所涉及的資料塊。表頭部的行鎖並不在 commit 之後立即釋放，而是要等 dbwr 程式完成之後才釋放，這就可能會出現一個使用者請求另一使用者已經 commit 的資源不成功的現象。

2 ）從 Commit 和 dbwr 程式結束之間的時間很短，如果恰巧在 commit 之後， dbwr 未結束之前斷電，因為 commit 之後的資料已經屬於資料檔案的內容，但這部分檔案沒有完全寫入到資料檔案中。所以需要前滾。由於 commit 已經觸發 lgwr ，這些所有未來得及寫入資料檔案的更改會在例項重啟後，由 smon 程式根據重做日誌檔案來前滾，完成之前 commit 未完成的工作（即把更改寫入資料檔案）。

3 ）如果未 commit 就斷電了，因為資料已經在 db buffer 更改了，沒有 commit ，說明這部分資料不屬於資料檔案。由於 dbwr 之前觸發 lgwr 也就是隻要資料更改，（肯定要先有 log ）所有 dbwr 在資料檔案上的修改都會被先一步記入重做日誌檔案，例項重啟後， SMON 程式再根據重做日誌檔案來回滾。

其實 smon 的前滾回滾是根據檢查點來完成的，當一個全部檢查點發生的時候，首先讓 LGWR 程式將 redologbuffer 中的所有緩衝（包含未提交的重做資訊）寫入重做日誌檔案，然後讓 dbwr 程式將 dbbuffer 已提交的緩衝寫入資料檔案（不強制寫未提交的）。然後更新控制檔案和資料檔案頭部的 SCN ，表明當前資料庫是一致的，在相鄰的兩個檢查點之間有很多事務，有提交和未提交的。

當執行 rollback 時，具體步驟如下：

伺服器程式會根據資料檔案塊和 db buffer 中塊的頭部的事務列表和 SCN 以及回滾段地址找到回滾段中相應的修改前的副本，並且用這些原值來還原當前資料檔案中已修改但未提交的改變。如果有多個”前映像“，伺服器程式會在一個“前映像”的頭部找到“前前映像”的回滾段地址，一直找到同一事務下的最早的一個“前映像”為止。一旦發出了 commit ，使用者就不能 rollback ，這使得 commit 後 dbwr 程式還沒有全部完成的後續動作得到了保障。

-------------------------End By TangYun[Tony.Tang]-2019-01---------------------------------

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24930246/viewspace-2558384/，如需轉載，請註明出處，否則將追究法律責任。