怎么解析Oracle SQL語句執(zhí)行流程與順序原理

怎么解析Oracle SQL語句執(zhí)行流程與順序原理，相信很多沒有經(jīng)驗(yàn)的人對此束手無策，為此本文總結(jié)了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個(gè)問題。

Oracle SQL語句執(zhí)行流程與順序原理解析

Oracle語句執(zhí)行流程

第一步：客戶端把語句發(fā)給服務(wù)器端執(zhí)行

當(dāng)我們在客戶端執(zhí)行SQL語句時(shí)，客戶端會(huì)把這條SQL語句發(fā)送給服務(wù)器端，讓服務(wù)器端的進(jìn)程來處理這語句。也就是說，Oracle 客戶端是不會(huì)做任何的操作，他的主要任務(wù)就是把客戶端產(chǎn)生的一些SQL語句發(fā)送給服務(wù)器端。服務(wù)器進(jìn)程從用戶進(jìn)程把信息接收到后， 在PGA 中就要此進(jìn)程分配所需內(nèi)存，存儲(chǔ)相關(guān)的信息，如：在會(huì)話內(nèi)存存儲(chǔ)相關(guān)的登錄信息等。

雖然在客戶端也有一個(gè)數(shù)據(jù)庫進(jìn)程，但是，這個(gè)進(jìn)程的作用跟服務(wù)器上的進(jìn)程作用是不相同的，服務(wù)器上的數(shù)據(jù)庫進(jìn)程才會(huì)對SQL 語句進(jìn)行相關(guān)的處理。不過，有個(gè)問題需要說明，就是客戶端的進(jìn)程跟服務(wù)器的進(jìn)程是一一對應(yīng)的。也就是說，在客戶端連接上服務(wù)器后，在客戶端與服務(wù)器端都會(huì)形成一個(gè)進(jìn)程，客戶端上的我們叫做客戶端進(jìn)程，而服務(wù)器上的我們叫做服務(wù)器進(jìn)程。

第二步：語句解析

當(dāng)客戶端把SQL語句傳送到服務(wù)器后，服務(wù)器進(jìn)程會(huì)對該語句進(jìn)行解析。這個(gè)解析的工作是在服務(wù)器端所進(jìn)行的，解析動(dòng)作又可分為很多小動(dòng)作。

1）查詢高速緩存（library cache）

服務(wù)器進(jìn)程在接到客戶端傳送過來的SQL語句時(shí)，不會(huì)直接去數(shù)據(jù)庫查詢。服務(wù)器進(jìn)程把這個(gè)SQL語句的字符轉(zhuǎn)化為ASCII等效數(shù)字碼，接著這個(gè)ASCII碼被傳遞給一個(gè)HASH函數(shù)，并返回一個(gè)hash值，然后服務(wù)器進(jìn)程將到shared pool中的library cache（高速緩存）中去查找是否存在相同的hash值。如果存在，服務(wù)器進(jìn)程將使用這條語句已高速緩存在SHARED POOL的library cache中的已分析過的版本來執(zhí)行，省去后續(xù)的解析工作，這便是軟解析。若調(diào)整緩存中不存在，則需要進(jìn)行后面的步驟，這便是硬解析。硬解析通常是昂貴的操作，大約占整個(gè)SQL執(zhí)行的70%左右的時(shí)間，硬解析會(huì)生成執(zhí)行樹，執(zhí)行計(jì)劃，等等。

所以，采用高速數(shù)據(jù)緩存的話，可以提高SQL 語句的查詢效率。其原因有兩方面：一方面是從內(nèi)存中讀取數(shù)據(jù)要比從硬盤中的數(shù)據(jù)文件中讀取數(shù)據(jù)效率要高，另一方面也是因?yàn)楸苊庹Z句解析而節(jié)省了時(shí)間。

不過這里要注意一點(diǎn)，這個(gè)數(shù)據(jù)緩存跟有些客戶端軟件的數(shù)據(jù)緩存是兩碼事。有些客戶端軟件為了提高查詢效率，會(huì)在應(yīng)用軟件的客戶端設(shè)置數(shù)據(jù)緩存。由于這些數(shù)據(jù)緩存的存在，可以提高客戶端應(yīng)用軟件的查詢效率。但是，若其他人在服務(wù)器進(jìn)行了相關(guān)的修改，由于應(yīng)用軟件數(shù)據(jù)緩存的存在，導(dǎo)致修改的數(shù)據(jù)不能及時(shí)反映到客戶端上。從這也可以看出，應(yīng)用軟件的數(shù)據(jù)緩存跟數(shù)據(jù)庫服務(wù)器的高速數(shù)據(jù)緩存不是一碼事。

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

當(dāng)在高速緩存中找不到對應(yīng)的SQL語句時(shí)，則服務(wù)器進(jìn)程就會(huì)開始檢查這條語句的合法性。這里主要是對SQL語句的語法進(jìn)行檢查，看看其是否合乎語法規(guī)則。如果服務(wù)器進(jìn)程認(rèn)為這條SQL語句不符合語法規(guī)則的時(shí)候，就會(huì)把這個(gè)錯(cuò)誤信息反饋給客戶端。在這個(gè)語法檢查的過程中，不會(huì)對SQL語句中所包含的表名、列名等等進(jìn)行檢查，只是檢查語法。

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

若SQL 語句符合語法上的定義的話，則服務(wù)器進(jìn)程接下去會(huì)對語句中涉及的表、索引、視圖等對象進(jìn)行解析，并對照數(shù)據(jù)字典檢查這些對象的名稱以及相關(guān)結(jié)構(gòu)，看看這些字段、表、視圖等是否在數(shù)據(jù)庫中。如果表名與列名不準(zhǔn)確的話，則數(shù)據(jù)庫會(huì)就會(huì)反饋錯(cuò)誤信息給客戶端。

所以，有時(shí)候我們寫select語句的時(shí)候，若語法與表名或者列名同時(shí)寫錯(cuò)的話，則系統(tǒng)是先提示說語法錯(cuò)誤，等到語法完全正確后再提示說列名或表名錯(cuò)誤。

4）獲得對象解析鎖（control structer）

當(dāng)語法、語義都正確后，系統(tǒng)就會(huì)對我們需要查詢的對象加鎖。這主要是為了保障數(shù)據(jù)的一致性，防止我們在查詢的過程中，其他用戶對這個(gè)對象的結(jié)構(gòu)發(fā)生改變。

5）數(shù)據(jù)訪問權(quán)限的核對（data dict cache）

當(dāng)語法、語義通過檢查之后，客戶端還不一定能夠取得數(shù)據(jù)，服務(wù)器進(jìn)程還會(huì)檢查連接用戶是否有這個(gè)數(shù)據(jù)訪問的權(quán)限。若用戶不具有數(shù)據(jù)訪問權(quán)限的話，則客戶端就不能夠取得這些數(shù)據(jù)。要注意的是數(shù)據(jù)庫服務(wù)器進(jìn)程先檢查語法與語義，然后才會(huì)檢查訪問權(quán)限。

6）確定最佳執(zhí)行計(jì)劃

當(dāng)語法與語義都沒有問題權(quán)限也匹配，服務(wù)器進(jìn)程還是不會(huì)直接對數(shù)據(jù)庫文件進(jìn)行查詢。服務(wù)器進(jìn)程會(huì)根據(jù)一定的規(guī)則，對這條語句進(jìn)行優(yōu)化。在執(zhí)行計(jì)劃開發(fā)之前會(huì)有一步查詢轉(zhuǎn)換，如：視圖合并、子查詢解嵌套、謂語前推及物化視圖重寫查詢等。為了確定采用哪個(gè)執(zhí)行計(jì)劃，Oracle還需要收集統(tǒng)計(jì)信息確定表的訪問聯(lián)結(jié)方法等，最終確定可能的最低成本的執(zhí)行計(jì)劃。

不過要注意，這個(gè)優(yōu)化是有限的。一般在應(yīng)用軟件開發(fā)的過程中，需要對數(shù)據(jù)庫的sql語句進(jìn)行優(yōu)化，這個(gè)優(yōu)化的作用要大大地大于服務(wù)器進(jìn)程的自我優(yōu)化。

當(dāng)服務(wù)器進(jìn)程的優(yōu)化器確定這條查詢語句的最佳執(zhí)行計(jì)劃后， 就會(huì)將這條SQL語句與執(zhí)行計(jì)劃保存到數(shù)據(jù)高速緩存（library cache）。如此，等以后還有這個(gè)查詢時(shí)，就會(huì)省略以上的語法、語義與權(quán)限檢查的步驟，而直接執(zhí)行SQL語句，提高SQL語句處理效率。

第三步：綁定變量賦值

如果SQL語句中使用了綁定變量，掃描綁定變量的聲明，給綁定變量賦值，將變量值帶入執(zhí)行計(jì)劃。若在解析的第一個(gè)步驟，SQL在高速緩沖中存在，則直接跳到該步驟

第四步：語句執(zhí)行

語句解析只是對SQL語句的語法進(jìn)行解析，以確保服務(wù)器能夠知道這條語句到底表達(dá)的是什么意思。等到語句解析完成之后，數(shù)據(jù)庫服務(wù)器進(jìn)程才會(huì)真正的執(zhí)行這條SQL語句。

對于SELECT語句：

1）首先服務(wù)器進(jìn)程要判斷所需數(shù)據(jù)是否在db buffer存在，如果存在且可用，則直接獲取該數(shù)據(jù)而不是從數(shù)據(jù)庫文件中去查詢數(shù)據(jù)，同時(shí)根據(jù)LRU 算法增加其訪問計(jì)數(shù)；

2）若數(shù)據(jù)不在緩沖區(qū)中，則服務(wù)器進(jìn)程將從數(shù)據(jù)庫文件中查詢相關(guān)數(shù)據(jù)，并把這些數(shù)據(jù)放入到數(shù)據(jù)緩沖區(qū)中（buffer cache）。

其中，若數(shù)據(jù)存在于db buffer，其可用性檢查方式為：查看db buffer塊的頭部是否有事務(wù)，如果有事務(wù)，則從回滾段中讀取數(shù)據(jù)；如果沒有事務(wù)，則比較select的scn和db buffer塊頭部的scn，如果前者小于后者，仍然要從回滾段中讀取數(shù)據(jù)；如果前者大于后者，說明這是一非臟緩存，可以直接讀取這個(gè)db buffer塊的中內(nèi)容。

對于DML語句（insert、delete、update）：

1）檢查所需的數(shù)據(jù)庫是否已經(jīng)被讀取到緩沖區(qū)緩存中。如果已經(jīng)存在緩沖區(qū)緩存，則直接執(zhí)行步驟3；

2）若所需的數(shù)據(jù)庫并不在緩沖區(qū)緩存中，則服務(wù)器將數(shù)據(jù)塊從數(shù)據(jù)文件讀取到緩沖區(qū)緩存中；

3）對想要修改的表取得的數(shù)據(jù)行鎖定（Row Exclusive Lock），之后對所需要修改的數(shù)據(jù)行取得獨(dú)占鎖；

4）將數(shù)據(jù)的Redo記錄復(fù)制到redo log buffer；

5）產(chǎn)生數(shù)據(jù)修改的undo數(shù)據(jù)；

6）修改db buffer；

7）dbwr將修改寫入數(shù)據(jù)文件；

其中，第2步，服務(wù)器將數(shù)據(jù)從數(shù)據(jù)文件讀取到db buffer經(jīng)經(jīng)歷以下步驟：

1）首先服務(wù)器進(jìn)程將在表頭部請求TM鎖（保證此事務(wù)執(zhí)行過程其他用戶不能修改表的結(jié)構(gòu)），如果成功加TM鎖，再請求一些行級(jí)鎖（TX鎖），如果TM、TX鎖都成功加鎖，那么才開始從數(shù)據(jù)文件讀數(shù)據(jù)。

2）在讀數(shù)據(jù)之前，要先為讀取的文件準(zhǔn)備好buffer空間。服務(wù)器進(jìn)程需要掃描LRU list尋找free db buffer，掃描的過程中，服務(wù)器進(jìn)程會(huì)把發(fā)現(xiàn)的所有已經(jīng)被修改過的db buffer注冊到dirty list中。如果free db buffer及非臟數(shù)據(jù)塊緩沖區(qū)不足時(shí)，會(huì)觸發(fā)dbwr將dirty buffer中指向的緩沖塊寫入數(shù)據(jù)文件，并且清洗掉這些緩沖區(qū)來騰出空間緩沖新讀入的數(shù)據(jù)。

3）找到了足夠的空閑buffer，服務(wù)器進(jìn)程將從數(shù)據(jù)文件中讀入這些行所在的每一個(gè)數(shù)據(jù)塊（db block）（DB BLOCK是ORACLE的最小操作單元，即使你想要的數(shù)據(jù)只是DB BLOCK中很多行中的一行或幾行，ORACLE也會(huì)把這個(gè)DB BLOCK中的所有行都讀入Oracle DB BUFFER中）放入db buffer的空閑的區(qū)域或者覆蓋已被擠出LRU list的非臟數(shù)據(jù)塊緩沖區(qū)，并且排列在LRU列表的頭部，也就是在數(shù)據(jù)塊放入db buffer之前也是要先申請db buffer中的鎖存器，成功加鎖后，才能讀數(shù)據(jù)到db buffer。

若數(shù)據(jù)塊已經(jīng)存在于db buffer cache（有時(shí)也稱db buffer或db cache），即使在db buffer中找到一個(gè)沒有事務(wù)，而且SCN比自己小的非臟緩存數(shù)據(jù)塊，服務(wù)器進(jìn)程仍然要到表的頭部對這條記錄申請加鎖，加鎖成功才能進(jìn)行后續(xù)動(dòng)作，如果不成功，則要等待前面的進(jìn)程解鎖后才能進(jìn)行動(dòng)作（這個(gè)時(shí)候阻塞是tx鎖阻塞）。

在記redo日志時(shí)，其具體步驟如下：

1）數(shù)據(jù)被讀入到db buffer后，服務(wù)器進(jìn)程將該語句所影響的并被讀入db buffer中的這些行數(shù)據(jù)的rowid及要更新的原值和新值及scn等信息從PGA逐條的寫入redo log buffer中。在寫入redo log buffer之前也要事先請求redo log buffer的鎖存器，成功加鎖后才開始寫入。

2）當(dāng)寫入達(dá)到redo log buffer大小的三分之一或?qū)懭肓窟_(dá)到1M或超過三秒后或發(fā)生檢查點(diǎn)時(shí)或者dbwr之前發(fā)生，都會(huì)觸發(fā)lgwr進(jìn)程把redo log buffer的數(shù)據(jù)寫入磁盤上的redo file文件中（這個(gè)時(shí)候會(huì)產(chǎn)生log file sync等待事件）。

3）已經(jīng)被寫入redo file的redo log buffer所持有的鎖存器會(huì)被釋放，并可被后來的寫入信息覆蓋，redo log buffer是循環(huán)使用的。Redo file也是循環(huán)使用的，當(dāng)一個(gè)redo file寫滿后，lgwr進(jìn)程會(huì)自動(dòng)切換到下一redo file（這個(gè)時(shí)候可能出現(xiàn)log file switch（check point complete）等待事件）。如果是歸檔模式，歸檔進(jìn)程還要將前一個(gè)寫滿的redo file文件的內(nèi)容寫到歸檔日志文件中（這個(gè)時(shí)候可能出現(xiàn)log file switch（archiving needed）。

在為事務(wù)建立undo信息時(shí)，其具體步驟如下：

1）在完成本事務(wù)所有相關(guān)的redo log buffer之后，服務(wù)器進(jìn)程開始改寫這個(gè)db buffer的塊頭部事務(wù)列表并寫入scn（一開始scn是寫在redo log buffer中的，并未寫在db buffer）。

2）然后copy包含這個(gè)塊的頭部事務(wù)列表及scn信息的數(shù)據(jù)副本放入回滾段中，將這時(shí)回滾段中的信息稱為數(shù)據(jù)塊的“前映像”，這個(gè)“前映像”用于以后的回滾、恢復(fù)和一致性讀。（回滾段可以存儲(chǔ)在專門的回滾表空間中，這個(gè)表空間由一個(gè)或多個(gè)物理文件組成，并專用于回滾表空間，回滾段也可在其它表空間中的數(shù)據(jù)文件中開辟）。

在修改信息寫入數(shù)據(jù)文件時(shí)，其具體步驟如下：

1）改寫db buffer塊的數(shù)據(jù)內(nèi)容，并在塊的頭部寫入回滾段的地址。

2）將db buffer指針放入dirty list。如果一個(gè)行數(shù)據(jù)多次update而未commit，則在回滾段中將會(huì)有多個(gè)“前映像”，除了第一個(gè)“前映像”含有scn信息外，其他每個(gè)"前映像"的頭部都有scn信息和"前前映像"回滾段地址。一個(gè)update只對應(yīng)一個(gè)scn，然后服務(wù)器進(jìn)程將在dirty list中建立一條指向此db buffer塊的指針（方便dbwr進(jìn)程可以找到dirty list的db buffer數(shù)據(jù)塊并寫入數(shù)據(jù)文件中）。接著服務(wù)器進(jìn)程會(huì)從數(shù)據(jù)文件中繼續(xù)讀入第二個(gè)數(shù)據(jù)塊，重復(fù)前一數(shù)據(jù)塊的動(dòng)作，數(shù)據(jù)塊的讀入、記日志、建立回滾段、修改數(shù)據(jù)塊、放入dirty list。

3）當(dāng)dirty queue的長度達(dá)到閥值（一般是25%），服務(wù)器進(jìn)程將通知dbwr把臟數(shù)據(jù)寫出，就是釋放db buffer上的鎖存器，騰出更多的free db buffer。前面一直都是在說明oracle一次讀一個(gè)數(shù)據(jù)塊，其實(shí)oracle可以一次讀入多個(gè)數(shù)據(jù)塊（db_file_multiblock_read_count來設(shè)置一次讀入塊的個(gè)數(shù)）

當(dāng)執(zhí)行commit時(shí)，具體步驟如下：

1）commit觸發(fā)lgwr進(jìn)程，但不強(qiáng)制dbwr立即釋放所有相應(yīng)db buffer塊的鎖。也就是說有可能雖然已經(jīng)commit了，但在隨后的一段時(shí)間內(nèi)dbwr還在寫這條sql語句所涉及的數(shù)據(jù)塊。表頭部的行鎖并不在commit之后立即釋放，而是要等dbwr進(jìn)程完成之后才釋放，這就可能會(huì)出現(xiàn)一個(gè)用戶請求另一用戶已經(jīng)commit的資源不成功的現(xiàn)象。

2）從Commit和dbwr進(jìn)程結(jié)束之間的時(shí)間很短，如果恰巧在commit之后，dbwr未結(jié)束之前斷電，因?yàn)閏ommit之后的數(shù)據(jù)已經(jīng)屬于數(shù)據(jù)文件的內(nèi)容，但這部分文件沒有完全寫入到數(shù)據(jù)文件中。所以需要前滾。由于commit已經(jīng)觸發(fā)lgwr，這些所有未來得及寫入數(shù)據(jù)文件的更改會(huì)在實(shí)例重啟后，由smon進(jìn)程根據(jù)重做日志文件來前滾，完成之前commit未完成的工作（即把更改寫入數(shù)據(jù)文件）。

3）如果未commit就斷電了，因?yàn)閿?shù)據(jù)已經(jīng)在db buffer更改了，沒有commit，說明這部分?jǐn)?shù)據(jù)不屬于數(shù)據(jù)文件。由于dbwr之前觸發(fā)lgwr也就是只要數(shù)據(jù)更改，（肯定要先有l(wèi)og）所有dbwr在數(shù)據(jù)文件上的修改都會(huì)被先一步記入重做日志文件，實(shí)例重啟后，SMON進(jìn)程再根據(jù)重做日志文件來回滾。

其實(shí)smon的前滾回滾是根據(jù)檢查點(diǎn)來完成的，當(dāng)一個(gè)全部檢查點(diǎn)發(fā)生的時(shí)候，首先讓LGWR進(jìn)程將redologbuffer中的所有緩沖（包含未提交的重做信息）寫入重做日志文件，然后讓dbwr進(jìn)程將dbbuffer已提交的緩沖寫入數(shù)據(jù)文件（不強(qiáng)制寫未提交的）。然后更新控制文件和數(shù)據(jù)文件頭部的SCN，表明當(dāng)前數(shù)據(jù)庫是一致的，在相鄰的兩個(gè)檢查點(diǎn)之間有很多事務(wù)，有提交和未提交的。

當(dāng)執(zhí)行rollback時(shí)，具體步驟如下：

服務(wù)器進(jìn)程會(huì)根據(jù)數(shù)據(jù)文件塊和db buffer中塊的頭部的事務(wù)列表和SCN以及回滾段地址找到回滾段中相應(yīng)的修改前的副本，并且用這些原值來還原當(dāng)前數(shù)據(jù)文件中已修改但未提交的改變。如果有多個(gè)”前映像“，服務(wù)器進(jìn)程會(huì)在一個(gè)“前映像”的頭部找到“前前映像”的回滾段地址，一直找到同一事務(wù)下的最早的一個(gè)“前映像”為止。一旦發(fā)出了commit，用戶就不能rollback，這使得commit后dbwr進(jìn)程還沒有全部完成的后續(xù)動(dòng)作得到了保障。

第五步：提取數(shù)據(jù)

當(dāng)語句執(zhí)行完成之后，查詢到的數(shù)據(jù)還是在服務(wù)器進(jìn)程中，還沒有被傳送到客戶端的用戶進(jìn)程。所以，在服務(wù)器端的進(jìn)程中，有一個(gè)專門負(fù)責(zé)數(shù)據(jù)提取的一段代碼。他的作用就是把查詢到的數(shù)據(jù)結(jié)果返回給用戶端進(jìn)程，從而完成整個(gè)查詢動(dòng)作。

看完上述內(nèi)容，你們掌握怎么解析Oracle SQL語句執(zhí)行流程與順序原理的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！