Oracle執行SQL語句的過程

標籤：處理   aqs   buffer   詞法分析   加鎖   文法分析   details   asc   number   

 

轉載至：http://blog.csdn.net/aqszhuaihuai/article/details/7024551

 

當我們提交一條sql語句時，Oracle會做哪些操作呢？

Oracle會為每個使用者進程分配一個伺服器處理序：service process（實際情況應該區分專用伺服器和共用伺服器），當service process接收到使用者進程提交的sql語句時，伺服器處理序會對sql語句進行文法和詞法分析。

名詞解釋：

文法分析：語句本身正確性。

詞法分析：對照資料字典中檢查表，索引，視圖和使用者權限。

檢查通過後，伺服器處理序會將sql語句轉變為ascii碼，並通過一個hash函數將ascii碼產生出一個hash值，伺服器處理序會到share pool中查詢此hash是否存在，如果存在，伺服器處理序會從share pool中讀取已經解析好的語句來執行；如果不存在，則需要做以下步驟：產生執行計畫和產生執行編碼（請理解何為執行計畫）。解析完成後，Oracle會將sql語句本身代碼、hash值、編譯代碼、執行計畫和所有與該語句相關的統計資料存放到share pool中。

 

注意：

1 盡量寫相同的sql語句，因為即使是from語句中table順序的變化、查詢欄位位置的變化，甚至只是大小寫不同，都會促使oracle重新做一次硬解析。

2 增大shared_pool_size可以保留更多的緩衝在記憶體中的sql語句執行計畫，也意味著共用sql的可能性的增大。

 

在產生編譯代碼後，service process會試圖從db_buffer中讀取是否存在相關的快取資料。

 

下面我們分兩種情況來說明：

1  db_buffer中不包含記憶體資料：service  process會首先在表的頭部請求一些行鎖，申請成功後，將這些行所在的第一個block讀入db_buffer。此時如果db_buffer空閑空間不足，則會觸發寫操作—DBWr。如果db_buffer剩餘的空間不夠儲存新資料，就會觸發DBWr進程，將db_buffer中髒資料寫入資料檔案。騰出來的空間寫入新資料。

注意： db_block是oracle最小的邏輯單位，即使我們所要求的資料只是一個block所包含的眾多行中的一行或幾行，我們仍然需要將整個block讀入db_buffer。db_block的大小可以設定為8k的整數倍，並且可以針對不同的資料表空間設定不同的db_block_size的大小，一般建議在select多的表上將db_block_size設定大一些，而dml操作多的表上設定的小一些。

2  DBWr是寫資料進程，觸發DBWr進程的事件除了db_buffer空間不夠外，ckpt進程也是觸發DBWr的事件。

 

補充：

1 段是oracle最小的拓展單位。

2 ckpt進程：檢查點進程。將scn寫入記錄檔，控制檔案，資料檔案頭，資料區塊頭部。觸發ckpt進程的事件有alter system checkpoint，alter  tablespace  offline/begin back up和正常shutdown資料庫。

3 scn：system change number或者使用system  commit  number。scn號是oracle的邏輯時鐘標誌，我們可以理解為在commit時才會發生變化。Scn號是鑑效組資料一致的重要標誌，oracle實現備份恢複的資料一致性就是通過scn來判斷。

block讀入db_buffer後，service  process會將這個塊頭部的SCN號和發生變更的行資料寫入復原段。當使用者或者oracle復原資料時就是通過復原段和當前資料區塊實現資料的往前復原。

 

解釋：

復原段是用來儲存修改資料的前映像資料的，作用是保持並行作業時的讀一致性，實現復原等。復原段過小會引發快照過舊錯誤。9i提供了專門的undo資料表空間，顯然如果資料表空間層級的調整大小要比調整復原段容易的多。

 

注意：

Insert操作：復原段只需要記錄rowid，如果回退，只需將該記錄根據rowid刪除即可；

Update操作：復原段只需要記錄發生變化的欄位的前映像值，復原時用前映像值覆蓋更新值即可；

Delete操作：復原段記錄整行的資料，復原時恢複整行資料；

做imp/exp或者大批量交易處理時，需要為當前事務建立一個大的復原段，並將其他復原段offline。

接著oracle會組建記錄檔，server process會將被修改的資料的rowid、修改前的值、修改後的值、scn資訊和復原段中的相關資訊寫入redo log buffer，當發生以下操作時，LGWr會將redo log buffer中的資料寫入磁碟上的online redo：時間超過3s、佔用redo log buffer空間超過1/3、檢查點進程、alter switch logfile和DBWr進程之前。

 

 

注意：

1  oracle中寫資料的順序為：1 讀入db_buffer；2 寫復原段；3 寫redo log  buffer；4 改寫db_buffer；5 寫記錄檔；6寫資料檔案；

commit並不會觸發DBWr進程，即不會寫入資料，commit只會觸發寫日誌操作和寫入scn號。但是任何的dml語句都會產生日誌。

當一個聯機記錄檔寫滿後，LGWr會寫入下一個聯機日誌，請記住聯機日誌是迴圈寫，而控制檔案是並發寫。如果設定了為歸檔模式，歸檔進程會將前一個聯機日誌寫入歸檔檔案。

 

2  db_buffer中包含記憶體資料：首先判斷使用者執行的操作類型。

Select操作：首先判斷db_buffer中的資料區塊頭部是否存在事務，如果有，則說明資料區塊中的資料正在被交易處理，復原段中儲存著該資料的前映像，server process利用復原段中的資料進行讀一致性重構；如果資料區塊頭部不存在事務，則有可能該資料已經被交易處理完畢但仍然留在db_buffer中，這時會比較select語句中scn號和db_buffer中的資料區塊頭部的scn號，前者小於後者則說明此資料已經發生更改，處理資料同上，如果前者大於等於後者，則該資料為非髒資料，直接讀取即可。

Update操作：無論資料區塊頭部是否存在事務，又或者scn號之間孰大孰小，都需要伺服器處理序到表的頭部申請行鎖，申請成功則繼續操作，不成功則等待加鎖直至成功。

Oracle執行SQL語句的過程