Kevin Closson's Blog: Platforms, Databases and Storage

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.