記憶體分配問題處理

老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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章