Oracle體系結構概述與SQL解析剖析

Oracle伺服器

是一個資料庫管理系統，它提供了一種全面、開放、整合的方法來管理資訊。

Oracle伺服器由Oracle資料庫和Oracle例項組成。

oracle資料庫軟體和Oracle資料庫軟體可以分開儲存。oracle資料庫軟體一般存放在oralce伺服器的本地硬碟上，而Oracle資料庫例項所對應的檔案（.ctl、.log、.dbf）一般存放在儲存伺服器上。

一個常用的冗餘結構如下圖所示：

​ 體系結構圖

​ 儲存伺服器

例項和資料庫的關係

一個Oracle資料庫可以對應多個資料庫例項。對於資料庫例項a、b，和資料庫db1，如果有1萬個使用者連線資料庫db1，則其中5千個使用者連線到例項a，5千個使用者連線到例項b（RAC技術）。

資料庫結構

Oracle記憶體結構

與Oracle相關聯的記憶體結構包括：

• 系統全域性區(SGA)：由所有伺服器程式後臺程式共享

• 程式全域性區（PGA）： 專用於每一個伺服器程式或後臺程式，每一個程式使用一個PGA。

SGA是包含了資料庫例項控制資訊和資料的記憶體區。

其中，我們需要著重關心的是共享池（Share Pool）和資料庫緩衝區快取記憶體（Database buffer cache）。

SGA包含的資料結構：

• 共享池：快取可在各個使用者之間共享的各個結構；（SQL語句以及對應的執行計劃）

• Streams 池：由Oracle Streams使用；

• 大型池：這是一個可選的區域，可為某些大型程式（如Oeacle的備份和恢復操作、I/O伺服器程式）提供大量記憶體分配；

• Java池：用於Java虛擬機器（JVM）中特定會話的所有Java程式碼和資料；

• 資料庫緩衝區快取記憶體：快取從資料庫檢索的資料塊

• 重做日誌緩衝區：快取記憶體重做資訊（用於例項恢復），直到可以將其寫入磁碟中儲存的物理重做日誌

Oracle程式結構

對於每一個使用者程式，oracle伺服器都會建立一個與之對應的伺服器程式。也就是說當前如果有100個使用者與oracle伺服器進行連線，那麼，oracle將建立100個伺服器程式來處理使用者連線。

Oracle例項管理

• 控制檔案：記錄了各檔案存放的位置以及當前的執行狀態；

• 資料檔案：存放資料；

• 重做日誌檔案：對資料檔案所有的修改記錄；

• 系統監視器： 出現故障後，在啟動例項時執行崩潰恢復任務，對共享池中存在的記憶體碎片進行整理。

• 程式監視器： 使用者程式失敗時，執行程式清理任務；對sql process進行整理。

• 資料庫寫程式：將資料庫緩衝區快取記憶體中修改後的的資料塊寫入資料檔案；

• 日誌寫程式：

  ​ 觸發條件：

  • 當某個事務提交時
  • 當重做日誌緩衝區中變化的記錄超過1MB時
  • 當重做日誌緩衝區中所存的記錄已超過緩衝區容量的1/3
  • 在DBWR將資料庫高速緩衝區中修改過的資料塊寫到資料檔案之前
  • 每3秒鐘

  歸檔程式： 發生日誌切換時將重做日誌檔案複製到歸檔儲存器

  檢查點： 通過更新所有資料檔案的和控制檔案指出新的檢查點

Oracle資料庫伺服器由Oracle資料庫和Oracle例項組成，Oracle例項由系統全域性區SGA的記憶體結構和後臺程式組成。這些後臺程式可以處理執行例項時所涉及到的大量後臺任務。

物理資料庫結構

SQL解析剖析

讀資料

1. 客戶端輸入SQL語句

2. SQL語句通過網路到達資料庫例項

3. server process接收SQL語句

   3.1 判斷SQL在Shared Pool中是否存在快取

   ​ a. 如果有，則在shared pool中找到對應的sql語句以及對應的執行計劃，然後再去執行

   ​ b. 執行3.1

   3.1 SQL解析成執行計劃，然後才能執行

   解析流程：

   ​ a. 判斷SQL語法是否存在問題

   ​ b. 判斷SQL對應的表、檢視是否存在

   ​ c. 判斷使用者是否有對應表、檢視的許可權

   ​ d. 判斷如何執行（挑出最優的執行計劃作為執行計劃，最費時間，耗費CPU，I/O資源）

   ​ e. 選擇最優執行方案生成執行計劃

   3.1 執行SQL

   SQL Process根據SQL語句對應的執行計劃，執行SQL，讀取快取記憶體，如果快取中存在資料，則將快取中的資料返回給使用者，否則讀取dbf中的資料並將資料存入快取，然後將資料返回給使用者。

   邏輯讀：從記憶體中讀取資料

   物理讀：從物理磁碟中讀取資料

快取命中率： 命中率低一定有問題，命中率高不一定沒問題。

比起快取命中率，我們更關心每秒物理讀。

寫資料

sql process 將表讀到記憶體中，然後再對錶進行修改。Oracle資料庫對dbf檔案的修改都會強日誌。

資料庫寫程式（DBWn）負責將修改後的資料寫入資料檔案dbf。

日誌寫程式（LGWR）將日誌寫入日誌檔案。

（單一職責原則）

軟解析和硬解析

軟解析： 從快取記憶體中獲取執行計劃

硬解析：對SQL process接收到的SQL進行解析，判斷sql語法，查詢的表是否存在，是否有許可權，判斷如何執行（挑出最優的執行計劃作為執行計劃，最費時間，耗費CPU，I/O資源）；

SQL Process執行sql後，會將資料放入快取記憶體，再由DBRn和LGER分別寫入資料檔案dbf和日誌檔案log。因此，對於使用者來說，資料庫響應快慢由SQL Process直接影響。哪怕DBRn和LOWR兩個程式再繁忙，使用者也是感受不到的。

伺服器程式和資料庫緩衝區高速緩衝

演算法 ：使用最近最少使用演算法管理緩衝區

高速緩衝區狀態

• 空閒或未使用：例項剛啟動，緩衝區為空
• 已連線： SQL Process正在對記憶體中的資料進行讀寫操作。其他會話正在等待
• 髒資料 ：緩衝中的資料和磁碟中的資料不一致（對緩衝中的資料進行了修改，但是還沒有寫入磁碟）。需要先將髒資料寫入磁碟才能騰出緩衝區。
• 乾淨的 ：緩衝區已解除連線。如果沒有再次使用當前內容（資料塊），現在可以考慮立即騰出緩衝區。此時，不是緩衝區中的資料快已經同磁碟中的資料保持同步，就是緩衝區中包含對資料快的讀取一致性快照。