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.
“I am not a big fan of ASMM” – so am I :) . We have recently faced many bugs of ASMM. The last was instance (no RAC) hang, becuase MMAN held the Memory Manager Latch but didn’t succeed to shrink shared_pool.
ASMM is good for UAT environments (mostly you don’t have enough RAM there) and for not important databases. However, I am no more using it for mission critical databses, too.
And I definitely do not like AMM. Maybe becuase I do not understand how it is working. On Linux, it quite straightworward, since /dev/shm is used instead of old System V shared memory segmens and it could work. But how does it work in HP-UX and IBM? Oracle on HP-UX and IBM are still using System V shared memory when AMM takes place…
“The key information from my point of view is “KGH: NO ACCESS” memory area (memory in transition) should be more than 64M in size. If it is significantly bigger on constant basis know the instance is in trouble.”
Is there a typo in this sentence? Do you mean should be NO more than 64M?
Thank you Jason for reading and letting me know about the typo. I adjusted the post.
Thanks once again,
THANK YOU for this gem of a post. This was precisely what I needed to know to more deeply understand some “CURSOR: PIN S WAIT ON X” problems we had last week on one of our production databases.