When did it become normal for a DBA to discover new features by accident?  I am speaking of the new “feature” in 11gR2 where the database will automatically resize memory regions.   There is actually a bug filed, 12434622, to correct the documentation, since this new “feature” did not even make it to the documentation, this also appears to also be the new norm.  Here is an excerpt from the bug: “Customers need to be aware of this enhancement in 11.2.0.1 and they need to know how to disable this and therefore this has to be described by Oracle documentation.

 

A similar feature was introduced in 10g and is called Automatic Memory Management or AMM.  This feature would automatically “tune” the memory regions for best performance based on current workloads.  Although launched with much hype, almost all DBAs quickly realized that it was not appropriate for most database workloads.  Three of the biggest reasons why DBAs disable this feature is 1) no support for Linux hugepages and 2) large overhead required to reclaim memory from one part of the SGA to give it to another and 3) the database always favored shared pool over buffer cache so you always ended up with tiny buffer caches, even though it is quicker to parse than read from disk. (purposely not mentioning the thousands of bugs filed for it).

 

This new “feature” is not exactly AMM, it has a totally different purpose.  With this new “feature”, the database will automatically adjust the buffer cache down and add that memory to the shared pool, anytime a session receives an ORA-4031, "unable to allocate %s bytes of shared memory”,  error. In this case, the attempt is to protect the database from shared pool errors.

 

Now why would I keep describing this as a “feature”?  After all, I hate “air quotes”.  Unlike the many other undocumented features, like the one I describe in this blog:  Where Did that Fine Grained Striping Go?, this one is dangerous.  In many cases I have discovered, as well as my own experience, the database allocated 7.75 GB of my 8 GB default buffer pool to the shared pool.  Oh, thanks, now my, well tuned, database achieving 99% buffer cache hit ratio now has a 250 MB buffer cache.   My users are creating tickets in batch mode and there is no indication that anything has changed, just that I now have massive db file sequential and random read wait events.  Check the alert log, nope nothing there, bug 13340694 has been raised for that.  So this “feature” was a bad idea implemented in a bad way and documented badly.

 

The most important part of this blog, how to disable it: 

alter system set "_memory_imm_mode_without_autosga"=FALSE scope=both;

Keep in mind, if you are using init.ora files, you will have to manually make the change there as well.