Oracle 內(nèi)存結(jié)構(gòu)概覽

oracle 體系結(jié)構(gòu)：

Oracle 內(nèi)存結(jié)構(gòu)：

• System Global Area （SGA，系統(tǒng)全局區(qū)）
  • Share Pool （共享池）
  • Database Buffer Cache （數(shù)據(jù)庫緩沖區(qū)緩存）
  • Redo Log Buffer （重做日志緩沖區(qū)）
  • Large Pool （大池）
  • Java Pool（Java池）
  • Streams Pool（流池）
  • Fixed SGA（固定SGA區(qū)域）
• Program Global Area (PGA，程序全局區(qū)域)
  • User Global Area（UGA，用戶全局區(qū)域）
    • Cursor Area（游標區(qū)）
    • User Session Data Storage_Area （用戶會話數(shù)據(jù)存儲區(qū)）
    • SQL Work Areas（SQL工作區(qū)）
      • 1). Sort Area（排序區(qū)）
      • 2). Hash Area（哈希區(qū)）
      • 3). Create Bitmap Area（位圖索引創(chuàng)建區(qū)）
      • 4). Bitmap Merge Area （位圖索引融合區(qū)）
  • Stack Space（堆?？臻g）

oracle參數(shù)配置概覽

重要參數(shù)（系統(tǒng)運行前配置）:

帶MAX的參數(shù)不能在線修改，需要重啟后才生效，所以一定要提前規(guī)劃好

1. 內(nèi)存管理參數(shù):
   MEMORY_MAX_TARGET（11g）: 定義了Oracle數(shù)據(jù)庫實例允許使用的最大內(nèi)存量，包括SGA和PGA。動態(tài)調(diào)整可用內(nèi)存，無需重啟實例。
   MEMORY_TARGET（11g）: 指定數(shù)據(jù)庫實例使用的內(nèi)存目標，包括SGA和PGA。與MEMORY_MAX_TARGET結(jié)合使用，實現(xiàn)動態(tài)內(nèi)存管理。

2. SGA參數(shù): SGA_TARGET（10g）: 自動管理SGA的總內(nèi)存大小，包括共享池、數(shù)據(jù)緩存等。通過自動調(diào)整子組件大小，適應變化的工作負載需求。

3. PGA參數(shù):PGA_AGGREGATE_TARGET（9i）: 指定PGA的目標大小，由系統(tǒng)動態(tài)調(diào)整，根據(jù)工作負載需求自動分配內(nèi)存給各個會話的PGA。

4. 數(shù)據(jù)緩存參數(shù):DB_CACHE_SIZE（10g）: 指定數(shù)據(jù)庫緩存的大小，用于緩存數(shù)據(jù)塊，影響數(shù)據(jù)的讀取性能。

5. 共享池參數(shù):SHARED_POOL_SIZE（10g）: 定義共享池的大小，用于緩存SQL語句、PL/SQL塊和執(zhí)行計劃。影響SQL執(zhí)行性能。

6. 連接和進程參數(shù):
   PROCESSES（10g）: 控制并發(fā)用戶連接的最大數(shù)量。
   OPEN_CURSORS（10g）: 設(shè)置一個會話可以打開的最大游標數(shù)。
   SESSION_CACHED_CURSORS（10g）: 緩存在會話中的游標數(shù)，減少游標重新解析的開銷。

次要參數(shù)（可在系統(tǒng)運行后再優(yōu)化調(diào)整）:

1. 大池參數(shù): LARGE_POOL_SIZE: 用于備份和并行操作的大型I/O的合并。適用于特定工作負載需求。

2. Java虛擬機參數(shù): JAVA_POOL_SIZE: 指定Java虛擬機使用的內(nèi)存大小。影響Java相關(guān)操作的性能。

3. 數(shù)據(jù)流參數(shù): STREAMS_POOL_SIZE: 為Oracle Streams提供的內(nèi)存池，用于處理數(shù)據(jù)流操作。

4. 保留緩存參數(shù): DB_KEEP_CACHE_SIZE: 為頻繁訪問的對象提供的保留緩存，提高這些對象的性能。

5. 循環(huán)緩存參數(shù):DB_RECYCLE_CACHE_SIZE: 為一次性或不經(jīng)常訪問的對象提供的循環(huán)緩存。

6. 日志緩沖區(qū)參數(shù): LOG_BUFFER: 控制數(shù)據(jù)庫日志緩沖區(qū)的大小，適應不同的日志寫入需求。

7. 結(jié)果緩存參數(shù): RESULT_CACHE_SIZE: 指定結(jié)果集緩存的大小，用于緩存SQL查詢結(jié)果，提高重復查詢性能。

8. 自動PGA管理: PGA_AGGREGATE_LIMIT（11g R2以后）：指定PGA的硬限制，防止PGA使用過多內(nèi)存。

9. 自動診斷和調(diào)整:
   ADDM（Automatic Database Diagnostic Monitor）：自動數(shù)據(jù)庫診斷監(jiān)控，提供關(guān)于性能問題和建議的報告。
   AWR（Automatic Workload Repository）：自動工作負載存儲庫，用于存儲性能統(tǒng)計信息。

10. SQL執(zhí)行計劃管理:
    OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES：啟用或禁用自動捕獲SQL執(zhí)行計劃基線的功能。
    OPTIMIZER_USE_SQL_PLAN_BASELINES：啟用或禁用使用SQL執(zhí)行計劃基線的功能。

11. 自動調(diào)整:ADAPTIVE_PLAN_MANAGEMENT：啟用或禁用自適應計劃管理，允許數(shù)據(jù)庫自動調(diào)整執(zhí)行計劃。

12. 分區(qū)表和分區(qū)索引:
    PARTITION_TABLE：用于啟用或禁用分區(qū)表。
    PARTITION_INDEX：用于啟用或禁用分區(qū)索引。

13. 數(shù)據(jù)壓縮: COMPATIBLE：指定數(shù)據(jù)庫版本，決定數(shù)據(jù)庫是否支持特定的功能，例如表級數(shù)據(jù)壓縮。

14. 連接管理:SHARED_SERVERS：指定共享服務(wù)器的數(shù)量，用于連接管理。

15. 并行執(zhí)行:
    PARALLEL_DEGREE_POLICY：用于設(shè)置并行度策略，可以是 MANUAL（手動）或 AUTO（自動）。
    PARALLEL_MIN_SERVERS 和 PARALLEL_MAX_SERVERS：用于設(shè)置最小和最大并行服務(wù)器的數(shù)量。

16. 數(shù)據(jù)庫緩存: DB_nK_CACHE_SIZE：根據(jù)不同塊大小設(shè)置數(shù)據(jù)庫緩存大小。

17. RAC (Real Application Clusters) 參數(shù):
    CLUSTER_DATABASE_INSTANCES：指定在RAC環(huán)境中的數(shù)據(jù)庫實例數(shù)量。
    CLUSTER_DATABASE：用于啟用或禁用RAC功能。

Oracle數(shù)據(jù)庫服務(wù)器參數(shù)如何調(diào)整

以 IBM3850 服務(wù)器為例，在安裝好oracle軟件且數(shù)據(jù)庫建好之后，投入使用之前需要調(diào)整好相關(guān)參數(shù)

• CPU數(shù)量：4顆
• 內(nèi)存容量：64G
  

OLTP內(nèi)存分配

1. 工作區(qū)（Work Area，排序、Hash Join、位圖連接）：6G
   在OLTP（聯(lián)機事務(wù)處理）環(huán)境下，數(shù)據(jù)庫工作區(qū)用于臨時存放排序、哈希連接、位圖連接等操作產(chǎn)生的中間數(shù)據(jù)。這里的6GB是指為這些操作預留的內(nèi)存空間，確保在處理復雜查詢時有足夠的內(nèi)存供運算使用，減少對磁盤I/O的需求，從而提高系統(tǒng)性能。

2. Oracle服務(wù)器進程：一個連接 5M~10M，processes參數(shù) * 10M，2000 * 10M=20G
   在Oracle數(shù)據(jù)庫中，每個客戶端連接都會對應一個服務(wù)器進程。這里假定每個服務(wù)器進程需要5MB至10MB的內(nèi)存。如果數(shù)據(jù)庫參數(shù)processes設(shè)置為可以同時接受2000個并發(fā)連接，那么所有服務(wù)器進程所需的總內(nèi)存大約在10M * 2000 = 20GB左右。這部分內(nèi)存是為了支持并發(fā)用戶的活動，包括處理SQL查詢、事務(wù)管理等操作。

3. OS系統(tǒng)自用：6G
   這里的6GB內(nèi)存是留給操作系統(tǒng)自己使用的，包括內(nèi)核內(nèi)存、緩存、以及其他系統(tǒng)組件的內(nèi)存需求。這部分內(nèi)存確保操作系統(tǒng)穩(wěn)定運行，并可以處理各種系統(tǒng)級別的任務(wù)。

4. SGA = 64 - 6G - 20G - 6G = 32G
   SGA（系統(tǒng)全局區(qū)）是Oracle數(shù)據(jù)庫中用于存儲共享數(shù)據(jù)結(jié)構(gòu)的一個內(nèi)存區(qū)域，包括數(shù)據(jù)緩沖區(qū)、重做日志緩沖區(qū)、共享池、大池等。根據(jù)給定的內(nèi)存分配，64GB總內(nèi)存減去工作區(qū)的6GB、服務(wù)器進程所需的20GB以及系統(tǒng)自用的6GB之后，剩余的32GB內(nèi)存會被分配給SGA，以確保數(shù)據(jù)庫的高效運作。

操作系統(tǒng)核心參數(shù)配置

cp /etc/sysctl.conf /etc/sysctl.conf.bak  # 將當前的sysctl配置文件做一個備份，以防萬一需要回滾MemTotal=`grep MemTotal /proc/meminfo | awk '{print $2}'`  # 從/proc/meminfo中提取系統(tǒng)總的內(nèi)存大小（以KB為單位）
PAGE_SIZE=`getconf PAGE_SIZE`  # 獲取操作系統(tǒng)中一頁內(nèi)存的大小（以字節(jié)為單位）shmmax=`expr $MemTotal \* 1024`  # 根據(jù)總內(nèi)存大小計算 shmmax 參數(shù)的最大值，此處將其設(shè)為總內(nèi)存的四倍（轉(zhuǎn)換為KB后再乘以1024轉(zhuǎn)為字節(jié)）
shmall=`expr $shmmax / $PAGE_SIZE`  # 計算 shmall 參數(shù)的值，表示系統(tǒng)范圍內(nèi)共享內(nèi)存段的總頁數(shù)上限，這里的計算方法是將 shmmax 值除以一頁的大小得出cat >> /etc/sysctl.conf << EOF  # 向sysctl配置文件追加以下配置項
fs.file-max = 6815744  # 設(shè)置系統(tǒng)允許的最大文件句柄數(shù)
kernel.sem = 10000 10240000 10000 1024  # 設(shè)置信號量參數(shù)，分別代表SEMMSL（一個信號集中的信號量最大數(shù)）、SEMMNS（系統(tǒng)信號量總數(shù)）、SEMOPM（一次操作中的信號量操作數(shù)）、SEMMNI（系統(tǒng)信號集總數(shù)）
kernel.shmmni = 4096  # 設(shè)置系統(tǒng)范圍內(nèi)共享內(nèi)存段標識符的最大數(shù)目
kernel.shmall = $shmall  # 設(shè)置共享內(nèi)存段頁數(shù)上限，參考前面計算的結(jié)果
kernel.shmmax = $shmmax  # 設(shè)置共享內(nèi)存段的最大大小，參考前面計算的結(jié)果
net.ipv4.ip_local_port_range = 9000 65500  # 設(shè)置本地TCP/UDP端口范圍
net.core.rmem_default = 16777216  # 設(shè)置接收緩沖區(qū)的默認大小
net.core.rmem_max = 16777216  # 設(shè)置接收緩沖區(qū)的最大大小
net.core.wmem_max = 16777216  # 設(shè)置發(fā)送緩沖區(qū)的最大大小
net.core.wmem_default = 16777216  # 設(shè)置發(fā)送緩沖區(qū)的默認大小
fs.aio-max-nr = 6194304  # 設(shè)置異步I/O請求數(shù)量上限
vm.dirty_ratio=20  # 設(shè)置臟頁比例閾值，當系統(tǒng)中臟頁占總內(nèi)存的比例超過這個值時，開始寫入磁盤
vm.dirty_background_ratio=3  # 設(shè)置后臺臟頁刷新比率，后臺線程開始刷新臟頁前，系統(tǒng)中臟頁占總內(nèi)存的比例
vm.dirty_writeback_centisecs=100  # 設(shè)置臟頁寫回時間間隔（以百分秒計）
vm.dirty_expire_centisecs=500  # 設(shè)置臟頁過期時間間隔（以百分秒計）
vm.swappiness=10  # 控制系統(tǒng)換出內(nèi)存到硬盤的速度，值越低，盡量避免換出，值越高，傾向于換出
vm.min_free_kbytes=524288  # 設(shè)置系統(tǒng)試圖保留的最小空閑內(nèi)存（以KB為單位）
net.core.netdev_max_backlog = 30000  # 設(shè)置網(wǎng)絡(luò)設(shè)備接收數(shù)據(jù)包的最大隊列長度
net.core.netdev_budget = 600  # 設(shè)置每次軟中斷處理的數(shù)據(jù)包數(shù)量
net.ipv4.conf.all.rp_filter = 2  # 開啟IPv4反向路徑過濾（RFC 3704），防止IP欺騙攻擊
net.ipv4.conf.default.rp_filter = 2  # 對新建的網(wǎng)絡(luò)接口開啟默認的反向路徑過濾# vm.nr_hugepages =16384 # 若物理內(nèi)存太小（<64G），則不需要開啟大頁，可以直接注釋掉該行
# 注意：此處暫時先注釋掉，后面啟動大頁會解釋，需要根據(jù)實際需求和內(nèi)存使用情況決定是否啟用和設(shè)置正確的 HugePages 數(shù)量
EOF# 配置生效
sysctl -p  # 執(zhí)行sysctl命令使得配置文件中所做的更改立即生效#kernel.shmall：系統(tǒng)任意時刻可以分配的所有共享內(nèi)存段的總和的最大值(以頁為單位)，其值應不小于shmmax/page_size，推薦設(shè)置為物理內(nèi)存大小除以分頁大小。
#kernel.shmmax：單個內(nèi)存段最大，設(shè)置為內(nèi)存大小

Disabling ASMM（禁用自動共享內(nèi)存管理）

• SGA_TARGET 設(shè)置為0時，將禁用自動調(diào)優(yōu)
• 自動調(diào)優(yōu)參數(shù)設(shè)置為當前大小
• SGA的大小作為一個整體不受影響

PGA（Program Global Area）

PGA是Oracle數(shù)據(jù)庫中每個服務(wù)器進程獨有的內(nèi)存區(qū)域，用于存儲會話數(shù)據(jù)、排序區(qū)、臨時變量以及其他與特定會話相關(guān)的數(shù)據(jù)結(jié)構(gòu)。

PGA_AGGREGATE_TARGET

• 在OLTP系統(tǒng)中，典型PGA內(nèi)存設(shè)置應該是總內(nèi)存（物理內(nèi)存）的較小部分（例如20%），剩下80%分配給SGA 。OLTP：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
• 在DSS系統(tǒng)中，由于會運行一些很大的查詢，典型的PGA內(nèi)存最多分配70%的內(nèi)存。DSS：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%
• 設(shè)置 PGA 自動分配

alter system set workarea_size_policy = auto scope = both;
alter system set pga_aggregate_target = 2048M scope = both;
show parameter pga
show parameter area
show parameter work

• 監(jiān)控 PGA

select * from v$pgastat;
# 看 over allocation count（排序時，PGA不夠用時需要換頁，這個值就是換頁次數(shù)）：如果=0 就正常 PGA夠用；若 >0 則PGA不夠用需要調(diào)整

PROCESSES

• 在Oracle數(shù)據(jù)庫中，設(shè)置參數(shù) PROCESSES 和 SESSIONS 是非常重要的，以確保系統(tǒng)能夠處理足夠的并發(fā)連接。這只是一個基本的起點，實際的設(shè)置應該根據(jù)你的具體應用、系統(tǒng)硬件和負載情況來調(diào)整。建議在設(shè)置參數(shù)之前先進行性能測試，確保系統(tǒng)能夠處理預期的并發(fā)連接數(shù)。
  典型的設(shè)置是：
  10g: sessions = processes + 10% * processes + 5
  11g+: sessions = processes + 15 % * processes + 22
• 查看內(nèi)存使用情況

select p.spid from v$session s,v$process p where p.addr=s.paddr and s.sid in (select sid from v$mystat where rownum=1);
select pga_used_mem,pga_alloc_mem,pga_freeable_mem from v$process where spid = &spid;

• Processes參數(shù)優(yōu)化

create pfile='/tmp/pfileORCL_20240307.ora' from SPFILE; --備份配置文件
alter system set processes = 200 scope = spfile; 

OPEN_CURSORS & SESSION_CACHED_CURSORS

• OPEN_CURSORS
  • 指定了保存（打開）用戶語句的專用區(qū)域的大小。如果獲得“ORA-01000:maximun open cursors exceeded” 錯誤，則可能需要增大該參數(shù)，但是要確保關(guān)閉不在需要的游標。
  • 如果 OPEN_CURSORS 設(shè)置得過高，則有時也會造成問題：ORA-4031錯誤（共享池）
• SESSION_CACHED_CURSORS
  • 不要將參數(shù) SESSION_CACHED_CURSORS 設(shè)置的和 OPEN_CURSORS 一樣高，否則就可能產(chǎn)生 ORA-4031 或 ORA-7445 錯誤（軟軟解析）

不用重新啟動而修改初始參數(shù)文件

1. spfile<SID>.ora （大部分不需要重啟）
2. spfile.ora （用的少）
3. init<SID>.ora （一定要重啟）
4. v$parameter 視圖
   ISSES_MODIFIABLE 表名擁有 ALTER SESSION 特權(quán)的用戶是否可以修改這個參數(shù)
   ISSYS_MODIFIABLE 表名擁有 ALTER SYSTEM 特權(quán)的用戶是否可以修改這個參數(shù)

啟動大頁（vm.nr_hugepages）

什么是大頁？
在操作系統(tǒng)層面，默認情況下內(nèi)存頁大小通常為4KB。大頁是一種內(nèi)存管理機制，將標準內(nèi)存頁尺寸增大，例如從4KB改為2MB。使用大頁可以減少TLB（Translation Lookaside Buffer）未命中的情況，從而降低頁交換頻率和I/O開銷，提高系統(tǒng)的性能，特別是在大規(guī)模內(nèi)存管理和Oracle數(shù)據(jù)庫等場景下。

什么時候需要啟動大頁？
當服務(wù)器物理內(nèi)存>=64GB，啟用大頁可以帶來顯著的性能提升。

如何啟動大頁？

1. 關(guān)閉Oracle Database中的AMM（Automatic Memory Management）
   如果尚未手動管理內(nèi)存，請確保禁用自動內(nèi)存管理功能。

2. 計算hugepages的大小
   根據(jù)SGA_MAX_SIZE的值來決定hugepages的數(shù)量。hugepages的總量應略大于SGA的需求量，同時也要注意避免過大導致內(nèi)存浪費。例如，如果SGA_MAX_SIZE為32G，而每個大頁是2MB，那么至少需要16384個大頁（32GB / 2MB）。

3. 設(shè)置hugepages
   編輯內(nèi)核參數(shù)文件/etc/sysctl.conf，加入以下行：

   vi /etc/sysctl.conf
   vm.nr_hugepages = 16384 #<計算得出的大頁數(shù)量>
   

   然后執(zhí)行 sysctl -p 命令使更改生效。

4. 設(shè)置用戶進程內(nèi)存鎖定限制
   在/etc/security/limits.conf文件中為Oracle數(shù)據(jù)庫用戶設(shè)置內(nèi)存鎖定限制，確保其能夠鎖定足夠大的內(nèi)存以支持大頁。

   [root@mydb ~]# vi /etc/security/limits.conf
   oracle soft memlock 2056000
   oracle hard memlock 2056000
   

5. 重啟數(shù)據(jù)庫
   重啟Oracle數(shù)據(jù)庫以便新的內(nèi)存設(shè)置生效。

6. 查看大頁使用情況
   使用如下命令實時監(jiān)控大頁的使用情況：

   [root@mydb ~]# watch -n1 'cat /proc/meminfo | grep -i HugePages'
   

7. 設(shè)置pre_page_sga參數(shù)
   需要在數(shù)據(jù)庫實例啟動前預分配并初始化SGA，以利用大頁優(yōu)化性能：

   alter system set pre_page_sga = true scope = spfile;
   

參考鏈接：

• Oracle 23c 官方文檔
• 【Oracle】內(nèi)存結(jié)構(gòu)詳解（萬字長文，建議收藏，蠶食為佳）