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.
What happen if I start the instance setting both parameters as bellow?
*.memory_max_target=36G
*.memory_target=0
*.sga_max_size=29G
*.sga_target=0
Which model does the database is using ? AMM or ASMM ?