SHARED POOL中KGH: NOACCESS佔用大量記憶體的問題分析
接上篇的ORA-04031錯誤,知道整個SHARED POOL的元件中,ASM extent pointer array佔用很大的記憶體是顯示的一個BUG後,繼續分析各個元件佔用的記憶體大小,結果發現KGH: NO ACCESS元件也佔用了400多M,這個又是個什麼鬼東西呢?還是看看METALINK上怎麼說的吧。
[@more@]首先找到了下面一篇文章:
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1
This problem can occur on any platform.
Symptoms
Frequent ORA-4031 even after increasing SGA_TARGET, Shared_pool
Things observed:
1) ASMM
Sga_target is set
2) 'KGH: NO ACCESS' increasing in shared pool
SQL> select * from v$sgastat where pool = 'shared pool' and (name in ('free memory', 'sql area', 'library cache', 'miscellaneous', 'row cache', 'KGH: NO ACCESS') );
We can see High KGH: NOACCESS entries in the shared pool.
Cause
Bug 5045507 ASMM - FREQUENT RESIZING OF SHARED POOL & BUFFER CACHE
Symptoms:
1) The allocation between shared pool and default buffer cache is growing/shrinking regularly (every 1-5 minutes).
2) AWR report OR query on v$sgastat shows high value for " KGH: NOACCESS "
3) Disabling Automatic Shared Memory Management (ASMM) not causing the ORA-4031 errors
Solution
This is caused because of frequent allocation operation between shared pool and default buffer cache. Alllocation from buffer cache is being converted to "KGH: NOACCESS" in the shared pool.
This trashing from cache to shared pool was fixed in 10.2.0.2. Bug 4507532 (Unpublished) was used to fix it into 10.2.0.2
Patch for Bug 4507532 (Unpublished) cannot be provided on top of 10.2.0.1
So, the options to resolve this issue are
1) Upgrade to the latest patchset 10.2.0.3
OR
2) Disable ASMM as a workaround.
這篇文章上說的是從BUFFER CACHE收刮來的記憶體,被分配給SHARED POOL的時候,就是先被標識為KGH: NOACCESS,然後SHARED POOL的其他元件可以從這個KGH: NOACCESS元件中再來取自己需要的記憶體。那既然KGH: NOACCESS元件有500M,為啥還會報ORA-04031錯呢,又往下看,發現我的現象跟這裡說的一摸一樣,就是BUFFER CACHE和SHARED POOL兩個元件之間不停的在GROW和SHRINK,就那幾十M的記憶體在兩個池之間不停的搬過來搬過去,也不閒累,而且,在RAC環境中,只有其中一個節點有這樣的問題,這又是為啥呢?
還找到了另一篇文章:
Oracle Server - Enterprise Edition - Version: 10.1 to 10.2
Information in this document applies to any platform.
Goal
The value for the component 'KGH: NO ACCESS' in the Shared Pool is growing when ASMM is enabled by setting SGA_TARGET. What does this component signify? How to prevent the growth of this component?
Solution
'KGH: NO ACCESS' refers to granules that are in transit with ASMM i.e memory being reassigned from the Shared Pool to the Database Buffer Cache and vice-versa.
This memory component in the Shared Pool marked as 'KGH: NO ACCESS' is used by the Buffer Cache.
One could see the value for this component increasing when ASMM (Automatic Shared Memory Management) is enabled by setting the SGA_TARGET parameter.
When ASMM is enabled, Oracle will dynamically manage the memory allocations between the tunable pools based on the requirement.
Note that one can still specify a value for the each of the tunable parameters i.e Shared Pool, Database Buffer Cache, Large Pool, Java Pool and Streams Pool when ASMM is enabled. When minimum value for each of the tunable parameters is set, Oracle will make sure that the amount of memory being allocated for the corresponding pool will not shrink below the specified amount.
Thus by setting minimum value for the Shared Pool and Database Buffer Cache, we will be ensuring that the amount of memory available for reassignment between the two pools will be lesser thus preventing the growth of this 'KGH: NO ACCESS' component.
To prevent the growth of this component you can either
1> Disable ASMM
Or
2> Set the minimum values for the Shared Pool and the Database Buffer Cache
這上面又說SHARED POOL中的KGH: NO ACCESS是被BUFFER CACHE使用的,這個好像和前面的說法正好反過來了,是我E文太爛了還是ORACLE自己也搞糊塗了呢?
正過來反過去,反正這個東西就是遊離在BUFFER CACHE和SHARED POOL之間的一塊記憶體。另外一個問題就是,因為BUG的原因,使用ASMM會導致SHARED POOL和BUFFER CACHE會頻繁的被擴大和縮小。解決的辦法要麼就不要使用ASSM,要不就升級。
這裡暫時把SHARED POOL的最小值擴大了一些,讓他怎麼擴大縮小都不要導致ORA-04031就好
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25016/viewspace-1004637/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Windbg分析高記憶體佔用問題記憶體
- 利用Windbg分析高記憶體佔用問題記憶體
- shared pool記憶體結構記憶體
- Java中的CPU佔用高和記憶體佔用高的問題排查Java記憶體
- Oracle記憶體分配與使用小記(二)Shared Pool and Large PoolOracle記憶體
- 關於SQL Server的記憶體佔用問題SQLServer記憶體
- 使用dbms_shared_pool包將物件pin到記憶體中物件記憶體
- ORACLE記憶體管理 之五 SGA variable pool,shared_pool,large_pool,java_poolOracle記憶體Java
- 關於JProfiler監測記憶體佔用問題!!記憶體
- Win10系統中“系統和壓縮記憶體”佔用大量記憶體如何解決Win10記憶體
- Oracle記憶體結構(二)----Shared Pool的詳細資訊Oracle記憶體
- weblogic記憶體佔用等問題的解決方法Web記憶體
- 關於MSSQL佔用過多記憶體的問題 (轉)SQL記憶體
- SQL在shared pool中的解析過程問題SQL
- filebeat實踐-記憶體佔用-最大記憶體佔用記憶體
- 記一次記憶體佔用問題的調查過程記憶體
- SQLServer記憶體問題分析SQLServer記憶體
- ubuntu解決GPU視訊記憶體佔用問題UbuntuGPU記憶體
- 解決XP單擊右鍵佔用大量記憶體(轉)記憶體
- Oracle記憶體結構(二)----Shared Pool的詳細資訊(轉)Oracle記憶體
- 【.Net Core】分析.net core在linux下記憶體佔用過高問題Linux記憶體
- Java 程式佔用 VIRT 虛擬記憶體超高的問題研究Java記憶體
- Chrome 再次最佳化記憶體佔用問題,新增記憶體釋放開關Chrome記憶體
- 解決Apache長時間佔用記憶體大的問題,Apache 記憶體優化方法Apache記憶體優化
- 用 verbose GC 分析 IBM WebSphere Portal 的記憶體問題GCIBMWeb記憶體
- 分析並優化 Android 應用記憶體佔用優化Android記憶體
- 解決SQL Server資料庫佔用記憶體過多的問題SQLServer資料庫記憶體
- 使用Windbg快速分析應用記憶體洩露問題記憶體洩露
- 修改oracle記憶體佔用Oracle記憶體
- Android圖片載入記憶體佔用分析Android記憶體
- 使用 Chrome Dev tools 分析應用的記憶體洩漏問題Chromedev記憶體
- 除錯應用程式記憶體中的神祕問題除錯記憶體
- python物件的記憶體佔用Python物件記憶體
- Redis刪除大量key後,佔用的系統記憶體卻沒有釋放?Redis記憶體
- Shared Pool 的轉儲與分析
- 使用 Chrome 開發者工具分析記憶體問題Chrome記憶體
- Java記憶體問題 及 LeakCanary 原理分析Java記憶體
- oracle實驗記錄 (oracle 分析shared pool(1))Oracle