理解oracle執行sql語句的過程

簡單的過程理解如下：

使用者執行sql語句，然後使用者的user process把語句送給伺服器server process server process檢視shared pool是否有解析過的sql的解析，如果有就直接用，如果沒有就parse sql，表、列、許可權等檢驗，生成parse tree、執行計劃process 檢查要訪問的資料是否在data buffer cache中存在，如果不存在就訪問資料檔案，把對應的資料cache到data buffer cache，server process處理data buffer中的資料，commit後，寫redo buffer、redo logfile。chekpoint後寫dirty buffer 到data file中。

詳細的執行過程如下：

     當我們提交一條sql語句時，Oracle會為每個使用者程式分配一個伺服器程式：service process（該服務程式分為：專用伺服器和共享伺服器（排程程式）），當service process接收到使用者程式提交的sql語句時，伺服器程式會對sql語句進行語法和詞法分析。（語法分析：語句本身正確性。詞法分析：對照資料字典中檢查表，索引，檢視和使用者許可權。）檢查透過後，伺服器程式會將sql語句轉變為ascii碼，並透過一個hash函式將ascii碼生成出一個hash值，伺服器程式會到share pool中查詢此hash是否存在，如果存在，伺服器程式會從sharepool中讀取已經解析好的語句來執行；如果不存在，則需要做以下步驟：生成執行計劃和生成執行編碼（請理解何為執行計劃）。解析完成後，Oracle會將sql語句本身程式碼、hash值、編譯程式碼、執行計劃和所有與該語句相關的統計資料存放到sharepool中。
注意：
1、儘量寫相同的sql語句，因為即使是from語句中table順序的變化、查詢欄位位置的變化，甚至只是大小寫的不同，都會促使oracle重新做一次硬解析。
2、增大share_pool_size可以保留更多的快取在記憶體中的sql語句執行計劃，也意味著共享sql的可能性的增大。
在生成編譯程式碼後，service process會試圖從db_buffer中讀取是否存在相關的快取資料。下面我們分兩種情況來說明：
   1）db_buffer中不包含記憶體資料：service process會首先在表的頭部請求一些行鎖，申請成功後，將這些行所在的第一個block讀入db_buffer。此時如果db_buffer空閒空間不足，則會觸發寫操作—DBWr。如果db_buffer剩餘的空間不夠儲存新資料，就會觸發DBWr程式，將db_buffer中髒資料寫入資料檔案。騰出來的空間寫入新資料。
注意：
      1） 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：時間超過1s、佔用redo log buffer空間超過1/3、檢查點程式、alter switch logfile和DBWr程式之前。

注意：
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 price利用回滾段中的資料進行讀一致性重構；如果資料塊頭部不存在事務，則有可能該資料已經被事務處理完畢但仍然留在db_buffer中，這時會比較select語句中scn號和db_buffer中的資料塊頭部的scn號，前者小於後者則說明此資料已經發生更改，處理資料同上，如果前者大於等於後者，則該資料為非髒資料，直接讀取即可。
Update操作：無論資料塊頭部是否存在事務，又或者scn號之間孰大孰小，都需要伺服器程式到表的頭部申請行鎖，申請成功則繼續操作，不成功則等待加鎖直至成功。

參考站點：