最近在工作上遇到Oracle 發生的系統效能問題，雖然有順利解決了，但是在解決的過程中似乎覺的自己對Oracle知識相當貧乏，於是在這邊筆記一下我學到的知識。
學習的過過程中難免會出現紀錄錯誤，而且目前是因為工作上需要而自學。因此內容有錯還請見諒。
Oracle Instance
Instance 是一個由各種設定檔組成的記憶體結構，基本上當資料庫連線到Oracle 時是連接到 Instance。Instance 與 Database 是可以互不相依的，也就是說 Instance 與 Database 可以單獨存在。
以下是Oracle Instance 的架構圖
Instance 有兩中配置方式:
- Single Instance
- Oracle RAC (Oracle Real Application Clusters)
Single 是單純一個Database 配置一個 Instance ，而 Oracle RAC 是一個 Database 配置多個 Instance。
在 Oracle 12c 之後可以將 Read/Write 與 Read-Only 兩個 不同的Instance 共存在 同一個 Database 中。在12c之前除非是 Standby database 都是 Read/Write。
Instance 如果需要改 Read/Write 或是 Read-Only 可以透過參數 INSTANCE_MODE 調整。
INSTANCE_MODE = { READ-WRITE | READ-ONLY | READ-MOSTLY }
Oracle資料庫結構概述
- 記憶體結構（Instance）：記憶體結構包含由一些記憶體空間及與處理程式(Process)所組合而成的，會隨著資料庫的開啟與關閉而消失。
- 實體結構（Database）：實體結構包含存在儲存媒體上的一些檔案，不會因為資料庫開啟關閉而消失。
Oracle 記憶體結構
- 共享記憶體（System Global Area；SGA）：主要是做資料交換的暫存記憶體空間。
- 背景處理程式（Background Process）：主要是Oracle記憶體結構與實體結構之間的溝通橋樑。
- 軟體程式碼區域（Software Code Area）：主要是存放Oracle軟體可執行程式的地方，同時軟體程式碼區域也是屬於Oracle Instance的一部份。
- PGA（Program Global Area；PGA）：PGA是一塊私有的記憶體區塊，包含了有server process正在執行的工作資訊。每一個server process都有一個自己的PGA。。
- UGA（User Global Area；UGA）：UGA主要是儲存使用者特定的連線狀態，UGA可能在SGA中分配，也可能在PGA中分配，這取決於Oracle的網路連線方式，看是使用多執行緒伺服器模式（Multi-Thread Server）還是專屬伺服器模式（Dedicated Server）。
- 其他處理程式（Other Process）：主要是使用者與Oracle記憶體結構之間的溝通橋樑。
Oracle 記憶體模組
Oracle 對於記憶體主要以兩個模組管理:KSM、KGH
KSM(Kernel Service Memory)
負責項目為
- Fix SGA
- Database Buffer Cache
- Log Buffer Cache
KGH(Kernel Generic Heap)
負責項目為
- Shared Pool
- Library cache
- PGA
可以由 X$KSMFS（Kernel Services Memory Fixed SGA ） 表取得資訊
select * from X$KSMFS
Oracle SGA
SGA 的全稱為System Global Area(共享記憶體)，是可以讓Oracle所在的作業系統上與所有使用者連線和程序來共用資料。SGA有下列幾個特性:
- 在記憶體中存放了使用者資訊與控制資訊
- 一個SGA 只能 服務於一個Instance
- SGA是共用的，當有多個使用者連線到了這個Instance，SGA中的資訊可以同時被所有使用者同時使用
- Oracle Process和一個SGA就可以構成了一個Oracle Instance。當Instance啟動時，Oracle會自動從作業系統中分配設定好的記憶體給SGA，而當Instance關閉時，作業系統會回收這些記憶體。下列是啟動資料庫時，系統自動分配的狀況
SQL> startup
ORACLE instance started.
Total System Global Area 6.8719E+10 bytes # 這裡
Fixed Size 37229800 bytes
Variable Size 8053063680 bytes
Database Buffers 6.0532E+10 bytes
Redo Buffers 96985088 bytes
Database mounted.
Database opened.
以下是針對上述的中文解釋:
- Total System Global Area：此訊息顯示目前此SGA的大小。
- Fixed Size：裡面儲存了SGA 各部分元件的相關資訊，主要是作為導引SGA架構的區域，當Instance被開啟時此塊區域就被固定住了不能做任何的變動，此塊區域也可稱為Fixed SGA。
- Variable Size：此資訊顯示Shared Pool、Java Pool、Large Pool和Streams Pool等的配置總和，由於這些記憶體區塊都是可動態分配的所以統稱Variable Size
- Database Buffers：此訊息顯示資料庫緩衝快取區的大小。
- Redo Log Buffers：此訊息顯示重作日誌緩衝區的大小。
SGA 區塊項目
- Database Buffer Cache(資料庫緩衝快取區)
- Redo Log Buffer (重作日誌緩衝區)
- Shared Pool (共用區)
- Java Pool (與Java區)
- Large pool (大型區)
- Stream Pool (串流區)
- Fix SGA (固定SGA)
SGA 參數
SGA_MAX_SIZE
SGA_MAX_SIZE 設定太小有可能會造成 I/O 過頻繁，因為會使用到硬碟空間作為虛擬記憶體。當資料庫instance啟動之後，各區域項目會依據最少需求啟動，後續資料庫會根據實際需求自動配置(這個行為跟PRE_PAGE_SGA設定有關係)，但是總和不會超過SGA_MAX_SIZE配置。
一般建議配置方式是為作業系統記憶體的二分之一，舉例如下:
- 系統記憶體 1G 時SGA_MAX_SIZE建議為500M
- 系統記憶體 2G 時SGA_MAX_SIZE建議為1G
- 系統記憶體 4G 時SGA_MAX_SIZE建議為2.5G
- 系統記憶體 8G 時SGA_MAX_SIZE建議為5G
- 系統記憶體 16G 時SGA_MAX_SIZE建議為10G
SGA_TARGET
SGA_TARGET 是 Oracle 10g 以後引進的參數。主要是解決人對配置記憶體時需要精心計算各區愧配置的問題。Target 是一個抽象概念，當 Target 設定一個數值時，Oracle 就會盡量滿足所設定的值。上述機制名稱為ASMM(Automatic Shared Memory Management,自動共享記憶體管理)。
可以在PLSQL檢視目前設定
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 20G
sga_min_size big integer 0
sga_target big integer 20G # 這裡
unified_audit_sga_queue_size integer 1048576
設定語法如下
alter system set sga_target=1024m;
設定注意事項:
- SGA_TARGET的限制是大小是不能超過SGA_MAX_SIZE
- SGA_TARGET參數後Oracle將會收集SGA相關的統計數據,並透過V$SGA_TARGET_ADVICE呈現出來,因此可以根據這些資SGA_TARGET做相關的調整,以達到最佳狀況,裡面欄位組成如下:
- SGA_SZIE:資料庫緩衝快取區的名稱（Default、Keep、Recycle）
- SGA_SIZE_FACTOR:SGA SZIE的估算因子
- ESTD_DB_TIME:預估DB處理時間在目前的SGA大小
- ESTD_DB_TIME_FACTOR:當SGA大小為SGA_SIZE時,將預估DB處理時間與實際DB處理時間的比例
- ESTD_PHYSICAL_READS:當SGA大小為SGA_SIZE時,SGA預測得實體讀取數。
檢視調整結果: v$SGA_TARGET_ADVICE
當設定了SGA_TARGET參數後Oracle將會收集SGA相關的統計數據,並透過V$SGA_TARGET_ADVICE呈現出來,因此可以根據這些資SGA_TARGET做相關的調整,以達到最佳狀況,裡面欄位組成如下:
- SGA_SZIE:資料庫緩衝快取區的名稱（Default、Keep、Recycle）
- SGA_SIZE_FACTOR:SGA SZIE的估算因子
- ESTD_DB_TIME:預估DB處理時間在目前的SGA大小
- ESTD_DB_TIME_FACTOR:當SGA大小為SGA_SIZE時,將預估DB處理時間與實際DB處理時間的比例
- ESTD_PHYSICAL_READS:當SGA大小為SGA_SIZE時,SGA預測得實體讀取數。
如果沒有辦法檢視時有可能下列需要調整
- STATISTICS_LEVEL 為
BASIC
動態調整方式為
SHOW PARAMETER statistics_level; --確認狀態為 BASIC
ALTER SYSTEM SET statistics_level=typical;
ALTER SESSION SET statistics_level=typical;
LOCK_SGA
保證SGA都被鎖定在實體記憶體中,而不必Page In/Out,可以透過LOCK_SGA此參數來控制,LOCK_SGA預設值為FALSE,當指定為TRUE時,可以將全部SGA都鎖定在實體記憶體中。
設定注意事項:
- LOCK_SGA此參數是無法動態修改所以必須先以ALTER SYSTEM…的語法寫到SPFILE中,或是直接修改PFILE,之後再重啟Instance,LOCK_SGA的新值才會生效。
- 有些作業系統不支援記憶體鎖定,因此這參數也就無效
- 作業系統的記憶體太小(小於128G)通常不會設定，這裡是DBA的配置經驗。
PRE_PAGE_SGA
這個值主要告訴Oracle 要不要將SGA通通分配到記憶體中。預設值為 False，當設定為True時會將SGA通通分配到記憶體。
設定語法如下:
SQL> ALTER SYSTEM SET PRE_PAGE_SGA=true SCOPE=SPFILE;
設定注意事項:
- 設定完需要重啟資料庫。
- 當值為True 時資料庫啟動時間會變長，因為需要將SGA資訊一次載入到實體記憶體中。
- PRE_PAGA_SGA只是在Instance啟動時將實體記憶體分配給SGA,但並意味著系統在以後的運作的過程不會將SGA中的某些Page置換到虛擬記憶體中,也就是說儘管設置了這個參數,還是可能出現Page In/Out的狀況。如果需要保障SGA不被Page In/Out,就需要使用另外一個參數LOCK_SGA來控制了。
疑問點：
- 什麼是
Page In/Out？ 出現時資料庫會有什麼狀況?
SGA 監控
- 查詢記憶體區塊還剩多少使用空間
SELECT
POOL --記憶體所屬的記憶體區塊
NAME, --SGA記憶體區塊的名稱
BYTES / 1024 / 1024 MB --記憶體區塊的大小
FROM V$SGASTAT WHERE NAME = 'FREE MEMORY';
SELECT
TO_NUMBER (V$PARAMETER.VALUE) VALUE,
V$SGASTAT.BYTES/1024/1024 "V$SGASTAT MB",
(V$SGASTAT.BYTES/V$PARAMETER.VALUE) * 100 "PERCENT FREE"
FROM V$SGASTAT, V$PARAMETER
WHERE V$SGASTAT.NAME =' free memory'
AND V$PARAMETER.NAME = 'shared_pool_size'
AND V$SGASTAT.POOL = 'shared pool';
select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,
(SELECT SUM (bytes/1024/1024) FROM V$SGAINFO WHERE NAME='Maximum SGA Size') MAX_ALLOCATED ,
(select sum(bytes/1024/1024) from v$sgastat where name='free memory')free
from dual
Program Global Area (PGA)
與AP連線有直接關係，當AP橫向擴展時，會增加PGA的資源要求。