SQL語句執行過程詳解
一條sql，plsql的執行到底是怎樣執行的呢？
一、SQL語句執行原理：
第一步:客戶端把語句發給伺服器端執行
當我們在客戶端執行 select 語句時,客戶端會把這條 SQL 語句傳送給伺服器端,讓伺服器端的
程式來處理這語句。也就是說,Oracle 客戶端是不會做任何的操作,他的主要任務就是把客戶端產生
的一些 SQL 語句傳送給伺服器端。雖然在客戶端也有一個資料庫程式,但是,這個程式的作用跟伺服器
上的程式作用事不相同的。伺服器上的資料庫程式才會對SQL 語句進行相關的處理。不過,有個問題需
要說明,就是客戶端的程式跟伺服器的程式是一一對應的。也就是說,在客戶端連線上伺服器後,在客戶
端與伺服器端都會形成一個程式,客戶端上的我們叫做客戶端程式;而伺服器上的我們叫做伺服器程式。
第二步:語句解析
當客戶端把 SQL 語句傳送到伺服器後,伺服器程式會對該語句進行解析。同理,這個解析的工作,
也是在伺服器端所進行的。雖然這只是一個解析的動作,但是,其會做很多“小動作”。
1. 查詢快取記憶體(library cache)。伺服器程式在接到客戶端傳送過來的 SQL 語句時,不
會直接去資料庫查詢。而是會先在資料庫的快取記憶體中去查詢,是否存在相同語句的執行計劃。如果在
資料快取記憶體中,則伺服器程式就會直接執行這個 SQL 語句,省去後續的工作。所以,採用高速資料緩
存的話,可以提高 SQL 語句的查詢效率。一方面是從記憶體中讀取資料要比從硬碟中的資料檔案中讀取
資料效率要高,另一方面,也是因為這個語句解析的原因。
不過這裡要注意一點,這個資料快取跟有些客戶端軟體的資料快取是兩碼事。有些客戶端軟體為了
提高查詢效率,會在應用軟體的客戶端設定資料快取。由於這些資料快取的存在,可以提高客戶端應用軟
件的查詢效率。但是,若其他人在伺服器進行了相關的修改,由於應用軟體資料快取的存在,導致修改的
資料不能及時反映到客戶端上。從這也可以看出,應用軟體的資料快取跟資料庫伺服器的高速資料快取
不是一碼事。
2. 語句合法性檢查(data dict cache)。當在快取記憶體中找不到對應的 SQL 語句時,則服
務器程式就會開始檢查這條語句的合法性。這裡主要是對 SQL 語句的語法進行檢查,看看其是否合乎
語法規則。如果伺服器程式認為這條 SQL 語句不符合語法規則的時候,就會把這個錯誤資訊,反饋給客
戶端。在這個語法檢查的過程中,不會對 SQL 語句中所包含的表名、列名等等進行 SQL 他只是語法
上的檢查。
3. 語言含義檢查(data dict cache)。若 SQL 語句符合語法上的定義的話,則伺服器程式
接下去會對語句中的欄位、表等內容進行檢查。看看這些欄位、表是否在資料庫中。如果表名與列名不
準確的話,則資料庫會就會反饋錯誤資訊給客戶端。所以,有時候我們寫 select 語句的時候,若語法
與表名或者列名同時寫錯的話,則系統是先提示說語法錯誤,等到語法完全正確後,再提示說列名或表名
錯誤。
4. 獲得物件解析鎖(control structer)。當語法、語義都正確後,系統就會對我們需要查詢
的物件加鎖。這主要是為了保障資料的一致性,防止我們在查詢的過程中,其他使用者對這個物件的結構發
生改變。
5. 資料訪問許可權的核對(data dict cache)。當語法、語義透過檢查之後,客戶端還不一定
能夠取得資料。伺服器程式還會檢查,你所連線的使用者是否有這個資料訪問的許可權。若你連線上伺服器
的使用者不具有資料訪問許可權的話,則客戶端就不能夠取得這些資料。有時候我們查詢資料的時候,辛辛苦
苦地把 SQL 語句寫好、編譯透過,但是,最後系統返回個 “沒有許可權訪問資料”的錯誤資訊,讓我們氣
半死。這在前端應用軟體開發除錯的過程中,可能會碰到。所以,要注意這個問題,資料庫伺服器程式先
檢查語法與語義,然後才會檢查訪問許可權。
6. 確定最佳執行計劃 ?。當語句與語法都沒有問題,許可權也匹配的話,伺服器程式還是不會直接對
資料庫檔案進行查詢。伺服器程式會根據一定的規則,對這條語句進行最佳化。不過要注意,這個最佳化是有
限的。一般在應用軟體開發的過程中,需要對資料庫的 sql 語言進行最佳化,這個最佳化的作用要大大地大
於伺服器程式的自我最佳化。所以,一般在應用軟體開發的時候,資料庫的最佳化是少不了的。當伺服器程式
的最佳化器確定這條查詢語句的最佳執行計劃後,就會將這條 SQL 語句與執行計劃儲存到資料快取記憶體
(library cache)。如此的話,等以後還有這個查詢時,就會省略以上的語法、語義與許可權檢查的步驟,
而直接執行 SQL 語句,提高 SQL 語句處理效率。
第三步:語句執行
語句解析只是對 SQL 語句的語法進行解析,以確保伺服器能夠知道這條語句到底表達的是什麼意
思。等到語句解析完成之後,資料庫伺服器程式才會真正的執行這條 SQL 語句。這個語句執行也分兩
種情況。
一是若被選擇行所在的資料塊已經被讀取到資料緩衝區的話,則伺服器程式會直接把這個資料傳遞
給客戶端,而不是從資料庫檔案中去查詢資料。
若資料不在緩衝區中,則伺服器程式將從資料庫檔案中查詢相關資料,並把這些資料放入到資料緩衝
區中(buffer cache)。
第四步:提取資料
當語句執行完成之後,查詢到的資料還是在伺服器程式中,還沒有被傳送到客戶端的使用者程式。所以,
在伺服器端的程式中,有一個專門負責資料提取的一段程式碼。他的作用就是把查詢到的資料結果返回給
使用者端程式,從而完成整個查詢動作。從這整個查詢處理過程中,我們在資料庫開發或者應用軟體開發過
程中,需要注意以下幾點:
一是要了解資料庫快取跟應用軟體快取是兩碼事情。資料庫快取只有在資料庫伺服器端才存在,在
客戶端是不存在的。只有如此,才能夠保證資料庫快取中的內容跟資料庫檔案的內容一致。才能夠根據
相關的規則,防止資料髒讀、錯讀的發生。而應用軟體所涉及的資料快取,由於跟資料庫快取不是一碼事
情,所以,應用軟體的資料快取雖然可以提高資料的查詢效率,但是,卻打破了資料一致性的要求,有時候
會發生髒讀、錯讀等情況的發生。所以,有時候,在應用軟體上有專門一個功能,用來在必要的時候清除
資料快取。不過,這個資料快取的清除,也只是清除本機上的資料快取,或者說,只是清除這個應用程式
的資料快取,而不會清除資料庫的資料快取。
二是絕大部分 SQL 語句都是按照這個處理過程處理的。我們 DBA 或者基於 Oracle 資料庫的
開發人員瞭解這些語句的處理過程,對於我們進行涉及到 SQL 語句的開發與除錯,是非常有幫助的。有
時候,掌握這些處理原則,可以減少我們排錯的時間。特別要注意,資料庫是把資料查詢許可權的審查放在
語法語義的後面進行檢查的。所以,有時會若光用資料庫的許可權控制原則,可能還不能滿足應用軟體許可權
控制的需要。此時,就需要應用軟體的前臺設定,實現許可權管理的要求。而且,有時應用資料庫的許可權管
理,也有點顯得繁瑣,會增加伺服器處理的工作量。因此,對於記錄、欄位等的查詢許可權控制,大部分程
序涉及人員喜歡在應用程式中實現,而不是在資料庫上實現。
DBCC DROPCLEANBUFFERS
從緩衝池中刪除所有清除緩衝區。
DBCC FREEPROCCACHE
從過程快取中刪除所有元素。
DBCC FREESYSTEMCACHE
從所有快取中釋放所有未使用的快取條目
SQL語句中的函式、關鍵字、排序等執行順序:
1. FROM 子句返回初始結果集。
2. WHERE 子句排除不滿足搜尋條件的行。
3. GROUP BY 子句將選定的行收集到 GROUP BY 子句中各個唯一值的組中。
4. 選擇列表中指定的聚合函式可以計算各組的彙總值。
5. 此外,HAVING 子句排除不滿足搜尋條件的行。
6. 計算所有的表示式;
7. 使用 order by 對結果集進行排序。
8. 查詢你要搜尋的欄位。
二、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 file
switch(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語句執行過程詳解

相關文章