How to know if Automatic Memory Management is your Friend?

Posted in: Technical Track

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,
Yury Velikanov

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.

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Yury is a nice person who enjoys meeting and working with people on challenging projects in the Oracle space. He started to work as an Oracle DBA 14 years ago (1997). For the past 10 years (since 2001) his main area of expertise is Oracle e-Business Suite. Yury is an OCP 7,8,9,10g and OCM 9i,10g. He is a frequent presenter at Oracle related conferences such as Hotsos, UKOUG and AUOUG. Yury is a socially active person. Apart from Social Media (Twitter, Bloging, Facebook) he is the primary organizer of Sydney Oracle Meetup group (250 people). So if you happen to be in Sydney (Australia) drop Yury a message and stop by at one of his Meetups.

4 Comments. Leave new

“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?


Yury Velikanov
October 5, 2011 10:57 am

Thank you Jason for reading and letting me know about the typo. I adjusted the post.

Thanks once again,

Hans Henrik Krohn
October 11, 2011 9:38 am

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.


Leave a Reply

Your email address will not be published. Required fields are marked *