記憶體分配問題處理

老w愛db發表於2024-02-05
記憶體

一．  故障 現 象 簡述

    1. 應用 簡訊報 **** 資料庫 例項 無 法 連 接，重 啟 ***** 資料庫 例項後 恢復正常。

二．  故障原 因分析

1. 資料 庫 告警 日誌 2023.11.8   記錄 如下異常 ：

Tue Nov  8 00:00:19 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:00:55 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:01:29 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:02:00 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:02:33 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:03:05 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:03:36 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:04:10 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:04:41 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:07:14 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:07:44 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:08:15 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:08:47 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:09:17 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:09:48 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:10:19 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:10:50 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:11:22 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:11:52 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:12:23 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:12:55 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:13:25 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:13:57 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:14:27 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:14:58 2023

WARNING: ran out of shared pool for GES enqueue object.

Tue Nov  8 00:15:03 2023

Trace dumping is performing id=[cdmp_20231108001503]

Tue Nov  8 00:15:06 2023

         2. 以 上資訊表明2023 .11.8 00:00 SHARED POOL 已 耗盡 ， 沒有 空間 處理 GES 。

 

Tue Nov  8 00:15:07 2023

Error occured while spawning process P003; error = 4031

Tue Nov  8 00:15:07 2023

ORA-04031: unable to allocate 3832 bytes of shared memory ("shared pool","unknown object","sga heap(2,0)","osp allocation")

3. 告警 日誌記錄 4031 錯誤 ，這個 是因為 SHARE POOL 沒有 足夠 空間 處理 新增 SQL 語句 。

 

Tue Nov  8 00:15:08 2023

Errors in file /oracle/ ***** .trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select 1 from aq$_schedules","sga heap(1,0)","kglsim heap")

Tue Nov  8 00:15:08 2023

Error occured while spawning process q003; error = 4031

Tue Nov  8 00:15:09 2023

Errors in file /oracle/ ***** .trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select count(*) from sys.aq_...","sga heap(2,0)","kglsim heap")

4. 告警 日誌 再 次記錄 4031 錯誤 ， 提示 SHARE POOL 已 沒有足夠空間處理 新增 SQL 語句 。

 

       5. 綜合 分析 以上 報錯資訊，可知 因 為 SHARE POOL 內 存不足導致異常 ，因 為 SHARE POOL 是 SGA 組 成部分， 也 就意味著 SGA 空間不 足或是分 配 不合理， 經 過資料 庫 SGA 檢查 ， SGA 總 大 為 2 G ，確認 原因是 SGA 空間 不足導致此故障 發生 ，建議增加 SGA 記憶體 空間。

三．  故障處理方案

1. 服務 器總內 存 量為 128G ， 共執行 14 套 資料庫 其使用 SGA 總 和 為 68G ，大 頁 內 存 總大小為 74G ，大 頁記憶體剩餘可用為 6G ，系統總 體 記憶體使用率少 於 85% ，系統可 用 記憶體數 為 20-30G 之間 ， GGVV 資料 庫當前SGA 大 小為 2G ， 綜合以上 因素建 議 統一 增 大 GGVV 資料 庫 兩 節點SG A 為 4 G ，資料 庫當前 SHARED_POOL_SIZE 引數 為 1G ，建議 將此引數值增大到1.5G ，此 更改不會影 響資料 庫 服務 器 穩定 執行。


來自 “ ITPUB部落格 ” ，連結：https://blog.itpub.net/70021989/viewspace-3006385/，如需轉載，請註明出處，否則將追究法律責任。

相關文章