Oracle11g Automatic Memory Management Part III. Automatically Automatic?

Oracle Database 11g Automatic Memory Management is not automatic. Let me explain. The theory (my interpretation) behind such features as AMM is that omission of the relevant initialization parameters for the feature constitutes an implied disabling of the feature. I’m sure many of you are going to think I’m stupid for not knowing this, and indeed it is likely documented in bold 14 pitch font somewhere, but unless you set MEMORY_TARGET you don’t get AMM. I sort of presumed it would be the other way around. Here is a simple example.

I have a minimal init.ora and am running catalog.sql and catproc.sql only to hit an ORA-04031. Here is the init.ora:

control_files=("+SDATA/TEST/cntltest.dbf")

UNDO_MANAGEMENT=AUTO
db_block_size = 8192
db_files = 300
processes = 100

db_name = test

And, here is the ORA-04031:

SQL> grant select on ku$_fhtable_view to public
  2  /
grant select on ku$_fhtable_view to public
                *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 1044 bytes of shared memory ("shared
pool","select value$ from sys.props...","Typecheck","kggfaDoKghAlloc:1")

And here is all I had to add to the init.ora to fix it:

control_files=("+SDATA/TEST/cntltest.dbf")

UNDO_MANAGEMENT=AUTO
db_block_size = 8192
db_files = 300
processes = 100

db_name = test 

MEMORY_TARGET=1500M
MEMORY_MAX_TARGET=1500M 

As I pointed out in my blog entry entitled Oracle11g  Automatic Memory Management Part II – Automatically Stupid?, I have been pleasantly surprised by AMM in 11g. I suppose this simple catalog.sql/catproc.sql example is another-albeit very simplistic-example.

1 Response to “Oracle11g Automatic Memory Management Part III. Automatically Automatic?”



  1. 1 Ronny Egners Blog » MEMORY_TARGET (SGA_TARGET) or HugePages – which to choose? Trackback on March 31, 2010 at 9:15 am

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




DISCLAIMER

I work for Amazon Web Services. The opinions I share in this blog are my own. I'm *not* communicating as a spokesperson for Amazon. In other words, I work at Amazon, but this is my own opinion.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,944 other followers

Oracle ACE Program Status

Click It

website metrics

Fond Memories

Copyright

All content is © Kevin Closson and "Kevin Closson's Blog: Platforms, Databases, and Storage", 2006-2015. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Kevin Closson and Kevin Closson's Blog: Platforms, Databases, and Storage with appropriate and specific direction to the original content.

%d bloggers like this: