Just before going to Oracle OpenWorld I would like to share with you a very good Oracle Support note on Automatic Memory Management (ASMM).
Common Cause for ORA-4031 in 10gR2, Excess “KGH: NO ACCESS” Memory Allocation [Video] [ID 801787.1]
Don’t be misled by the title of the note ( I would make it more generic if I was in charge). I think any Oracle DBA who switch on ASMM (init.ora parameter SGA_TARGET) should read though this carefully.
The note is full of significant technical details. I will leave it to you to read it carefully. In this blog post I would like to share with you the most important takeaway, from my point of view.The note gives us a guideline on how to evaluate if ASMM doesn’t “steal” memory and all SGA memory you allocate really is in use. End of the day the feature is good if it works well. There are several ASMM related bugs and the note helps you to find it quickly if your database is not affected. In my case we found that ASMM “wasted” 2GB out for 15GB allocated to SGA and our solution was to switch off ASMM.
The key information from my point of view is “KGH: NO ACCESS” memory area (memory in transition) should not be more than 64M in size. If it is significantly bigger on constant basis know the instance is in trouble.
I am not a big fan of ASMM. However if it works well there are no reasons why you shouldn’t use it for non-critical systems. And now there is a way you can check if doesn’t waste too much memory.
Looking forward to meet some of you at Oracle OpenWorld in few days,
2011.10.18 FOLLOW UP: It looks like the MOS note I was referencing to in this blog post may not 100% accurate. You may want to review the blog post from Tanel here. I am still not 100% sure where the truth is at this stage. I hope to have a detailed look in this area as time permits.