Oracle SQL語句執行完整過程：

標籤：

SQL語句執行完整過程：

1. 使用者進程提交一個sql 語句: update temp set a=a*2，給伺服器處理序。

2.伺服器處理序從使用者進程把資訊接收到後, 在PGA 中就要此進程分配所需記憶體,儲存相關的資訊,如在會話記憶體儲存相關的登入資訊等。

3.服務 器進程把這個sql  語句的字元轉化為ASCII 等效數字碼, 接著這個ASCII   碼被傳遞給一個HASH 函數, 並返回一個hash 值,然後伺服器處理序將到shared pool  中的library cache 中去尋找是否存在相 同的hash 值,如果存在, 伺服器處理序將使用這條語句已快取在SHARED POOL 的library cache 中的已分析過的版本來執行。

4.如果不存在, 伺服器處理序將在CGA 中, 配合UGA  內容對sql,進行文法分析,首先檢查文法的正確性,接著對語句中涉及的表,索引,視圖等對象進行解析,並對照資料字典檢查這些對象的名稱以及相關結構，並根據ORACLE 選用的最佳化模式以及資料字典中是否存在相應對象的統計資料和是否使用了儲存大綱來產生一個執行計畫或從儲存大綱中選用一個執行計畫,然後再用資料字典核對此使用者對相應對象的執行許可權,最後產生一個編譯代碼。

5.ORACLE  將這條sql 語句的本身實際文本、HASH 值、編譯代碼、與此語名相關聯的任何統計資料 和該語句的執行計畫緩衝在SHARED POOL  的library cache 中。伺服器處理序通過SHARED POOL 鎖存器(shared pool latch) 來申請可以向哪些共用PL/SQL 區中緩衝這此內容,也就是說被SHARED POOL 鎖存 器鎖定的PL/SQL 區中的塊不可被覆蓋,因為這些塊可能被其它進程所使用。

6.在SQL分析階段將用到LIBRARY  CACHE,從資料字典中核對錶、視圖等結構的時候,需要將資料 字典從磁碟讀入LIBRARY  CACHE,因此,在讀入之前也要使用LIBRARY  CACHE 鎖存器(library cache pin,library cache lock) 來申請用於快取資料字典。到現在為止, 這個sql 語句已經被編譯成可執行檔代碼了,但還不知道要操作哪些資料, 所以伺服器處理序還要為這個sql 準備預先處理資料。

7.首先伺服器處理序要判斷所需資料是否在db buffer 存在,如果存在且可用,則直接擷取該資料, 同時根據LRU 演算法增加其訪問計數; 如果buffer 不存在所需資料,則要從資料檔案上讀取首先伺服器處理序將在表頭部 請求TM 鎖(保證此事務執行過程其他使用者不能修改表的結構), 如果成功加TM 鎖,再請求一些行級鎖(TX 鎖), 如果TM、TX 鎖都成功加鎖,那麼才開始從資料檔案讀資料,在讀資料之前, 要先為讀取的檔案準備好buffer 空 間。伺服器處理序需要掃面LRU list  尋找free db buffer,掃描的過程中,伺服器處理序會把發現的所有 已經被修改過的db buffer  註冊到dirty list 中,  這些dirty buffer  會通過dbwr 的觸發條件,隨後會被寫出到資料檔案,找到了足 夠的空閑buffer,就可以把請求的資料行所在 的資料區塊放入到db buffer 的空閑地區或者 覆蓋已經被擠出LRU list 的非髒資料區塊緩衝區,並排列 在LRU list 的頭部, 也就是在資料區塊放入DB BUFFER  之前也是要先申請db buffer 中的鎖存器,成功加鎖後, 才能讀資料到db buffer。

8.記日誌現在資料已經被讀入到db buffer 了,現在伺服器處理序將該語句所影響的並被讀  入db buffer  中的這些行資料的rowid 及要更新 的原值和新值及scn  等資訊從PGA  逐條的寫入redo log buffer  中。在寫入redo log buffer 之 前也要事先請求redo log buffer 的鎖存器,成功加鎖後才開始寫入,當 寫入達到redo log buffer  大小的三分之一或寫入量達到1M 或超過 三秒後或發生檢查點時或者dbwr 之前發生, 都會觸發lgwr  進程把redo log buffer  的資料寫入磁碟上的redo file 檔案中(這個時候會產生log file sync 等待事件)  已經被寫入redofile  的redo log buffer 所持有的鎖存器會被釋放,並可被後來的寫入資訊覆蓋, redo log buffer是迴圈使用的。Redo file 也是迴圈使用的, 當一個redo file 寫滿後,lgwr 進程會自動切換到 下一redo file( 這個時候可能出現log fileswitch(checkpoint complete)等待事件)。如果是歸檔模式,歸檔進  程還要將前一個寫滿的redo file 檔案的內容寫到歸檔記錄檔中( 這個時候可能出現log fileswitch(archiving needed)。

9.  為事務建立復原段在完成本事務所有相關的redo log buffer 之後,伺服器處理序開始改寫 這個db buffer 的塊頭部事務列表並寫入scn, 然後copy  包含這個塊的頭部事務列表及scn 資訊的資料副本放入復原段中,將這時復原段““中的資訊稱為資料區塊的前映像,”這個前映“像用於以後的復原、恢複和一致性讀。(復原段可以儲存在專門的復原資料表空間中,這個資料表空間由一個或多個物理檔案組成,並專用於復原資料表空間,復原段也可在其它資料表空間中的資料檔案中開闢。

10. 本事務修改資料區塊準備工作都已經做好了, 現在可以改寫db buffer 塊的資料內容了,並在塊的頭部寫入復原段的地址。

11. 放入dirty list  如果一個行資料多次update  而未commit,“則在復原段中將會有多個前“映像,除了第”“ 一個前映像含有scn 資訊外,““ 其他每個前映像的頭部都有scn “” 資訊和前前映像復原段地址。一個update 只對應一 個scn, 然後伺服器處理序將在dirty list 中建立一  條指向此db buffer 塊的指標( 方便dbwr  進程可以找到dirty list  的db buffer 資料區塊並寫入資料檔案中) 。接著伺服器處理序會從資料檔案中繼續讀入第二個資料區塊,重複前一資料區塊的動作,資料區塊的讀入、記日誌、建立復原段、 修改資料區塊、放入dirty list 。當dirty queue 的長度達到閥值( 一般是25%), 伺服器處理序將通知dbwr 把髒資料寫出, 就是釋放db buffer 上的鎖存器, 騰出更多的free db buffer 。前面一直都是在說明oracle 一次讀一個資料區塊, 其實oracle 可以一次讀入多個資料區塊(db_file_multiblock_read_count 來設定一次讀入塊的個數)  說明:  在前置處理過的資料已經緩衝在db buffer 或剛剛被從資料檔案 讀入到db buffer 中, 就要根據sql 語句的類型來決定接下來如何操作。  1> 如果是select 語句, 則要查看db buffer 塊的頭部是否有事務,如果有事務,則從復原段中讀取資料;如果沒有事務, 則比較select  的scn  和db buffer  塊頭部的scn,如果前者小於後者,仍然要從復原段中讀取資料;如果前者大於後者,說明這是一非髒緩衝, 可以直接讀取這個db buffer 塊的中內容。 2> 如果是DML 操作, 則即使在db buffer 中找到一個沒有事務, 而且SCN 比自己小的非髒 快取資料塊,伺服器處理序仍然要到表的頭部對這條記錄申請加鎖,加鎖成功才能進行後續動作,如果不成功,則要等待前面的進程解鎖後才能進行動作( 這個時候阻塞是tx 鎖阻塞)。  使用者commit  或rollback 到現在為止, 資料已經在db buffer 或資料檔案中修改完 成,但是否要永久寫到數檔案中,要由用 戶來決定commit(儲存更改到資料檔案) rollback 撤銷資料的更改)。 1. 使用者執行commit 命令  只有當sql  語句所影響的所有行所在的最後一個塊被讀入db buffer  並且重做資訊被寫入redo log buffer(僅指日誌緩衝區,而不包括記錄檔)之後, 使用者才可以發去commit 命令,commit  觸發lgwr 進程,但不 強制立即dbwr 來釋放所有相應db buffer 塊的鎖(也就是no-force-at-commit,即提交不強制寫),也就是說有 可能雖然已經commit 了,但在隨後的一 段時間內dbwr  還在寫這條sql 語句所涉及的資料區塊。表頭部的行鎖 並不在commit 之後立即釋放, 而是要等dbwr 進程完成之後才釋放,這就可能會出現一個使用者請求另一使用者 已經commit 的資源不成功的現象。 A . 從Commit  和dbwr 進程結束之間的時間很短,如果 恰巧在commit 之後,dbwr 未結束之前斷電,因為commit 之後的資料已經屬於資料檔案的內容,但這部分檔案沒有完全寫入到資料檔案中。所以需要前滾。由 於commit 已經觸 發lgwr,這些所有未來得及寫入資料檔案的更改會在執行個體重啟後, 由smon 進程根據重做記錄檔來前滾, 完成之前commit 未完成的工作(即把更改寫入資料檔案)。 B. 如果未commit 就斷電了, 因為資料已經在db buffer 更改了, 沒有commit,說明這部分資料不屬於資料檔案, 由於dbwr  之前觸發lgwr 也就是只要資料更改,( 肯定要先有log)  所有DBWR,在資料檔案上的修改都會被先一步記入重做記錄檔,執行個體重啟後,SMON 進程再根據重做記錄檔來復原。  其實smon 的前滾復原是根據檢查點來完成的,當一個全部檢查點發生的時候, 首先讓LGWR  進程將redo log buffer 中的所有緩衝(包含未提交的重做資訊)寫入重做記錄檔, 然後讓dbwr  進程將db buffer 已提交的緩衝寫入資料檔案(不強制寫未提交的)。然後更新控制檔案和 資料檔案頭部的SCN,表明當前資料庫是一致的,在相鄰的兩個檢查點之間有很多事務,有提交和未提交的。 像前面的前滾復原比較完整的說法是如下的說明:   A.發生檢查點之前斷電,並且當時有一個未提交的改變進行中,執行個體重啟之後,SMON 進程將從上一個檢查點開始核對這個檢查點之後記錄在重做記錄檔中已提交的和未提交改變,因為 dbwr  之前會觸發lgwr, 所以dbwr 對資料檔案的修改一定會被先記錄在重做記錄檔中。因此, 斷電前被DBWN 寫進資料檔案的改變將通過重做記錄檔中的記錄進行還原,叫做復原, B. 如果斷電時有一個已提交, 但dbwr 動作還沒有完全完成的改變存在,因為已經提交, 提交會觸發lgwr 進程,所以不 管dbwr 動作是否已完成,該語句將要影響的行及其產生的結果一定已經記錄在重做記錄檔中了,則執行個體重啟後,SMON 進程根據重做記錄檔進行前滾. 執行個體失敗後用於恢複的時間由兩個檢查點之間的間隔大小來決定,可以通個四個參數設定檢查點執行的頻率:  Log_checkpoint_interval: 決定兩個檢查點之間寫入重做記錄檔的系統物理塊(redo blocks)的大小, 預設值是0,無限制。 log_checkpoint_timeout:                 兩個檢查點之間的時間長度（秒）預設值1800s。fast_start_io_target: 決定了用於恢複時需要處理的塊的多少, 預設值是0,  無限制。fast_start_mttr_target: 直接決定了用於恢複的時間的長短, 預設值是0,無限制(SMON 進程執行的前滾和復原與使用者的復原是不同的,SMON 是根據重做記錄檔進行前滾或復原,而使用者的復原一定是根據復原段 的內容進行復原的。 在這裡要說一下復原段儲存的資料, 假如是delete 操作,則復原段將會記錄整個行的資料, 假如是update,則復原段只記錄被修改了的欄位的變化前的資料(前映像),也就是沒有被修改的欄位是不會被記錄的, 假如是insert,則復原段只 記錄插入記錄的rowid 。這樣假如事務提交,那復原段中簡單標記該事務已經提交;假如是回退, 則如果操作是delete,回退的時候把復原段中資料重新寫回資料區塊, 操作如果是update,則把變化前資料修改回去,操作如果 是insert, 則根據記錄的rowid 把該記錄刪除。 2. 如果使用者rollback。  則伺服器處理序會根據資料檔案塊和DB BUFFER 中塊的頭部的事務列表 和SCN 以及復原段地址找到復原段中相應的修改前的副本,並且用這些原值來還原當前資料檔案中已修改但未提交的改變。如果有多個“”前映像,“伺服器處理序會在一個前映”“”像的頭部找到前前映像的復原段地址,一直找到同一事務下的最早的“”一個前映像 為止。一旦發出了COMMIT,使用者就不能rollback, 這使得COMMIT  後DBWR 進程還沒有全部完成的後續動作得到了保障。到現在為例一個事務已經結束了。 說明:  TM 鎖:  符合lock 機制的, 用於保護對象的定義不被修改。TX 鎖: 這個鎖代表一個事務,是行級鎖,用資料區塊頭、資料記錄頭的一些欄位表示, 也是符合lock 機制, 有resource structure、lock structure、enqueue 演算法。

 

 

僅僅是一個SQL語句就包含了這麼複雜的處理過程，如果算上硬體互動（鍵盤滑鼠操作），作業系統處理，網路傳輸等等，僅僅點擊一個查詢按鈕後台就介入了無比複雜的各方面的處理過程

Oracle SQL語句執行完整過程：