Using Oracle 10g ASMM

Using Oracle 10g ASMM

As with the other manageability features of Oracle Database 10g, ASMM requires you to set the STATISTICS_LEVEL parameter to at least TYPICAL (the default), so make sure this setting is correct before you try to enable ASMM. You can enable it in the following ways:
Set SGA_TARGET to a nonzero value in the initialization parameter file (pfile).
Use Oracle Enterprise Manager (or Oracle Grid Control) or ALTER SYSTEM to dynamically set a nonzero value for SGA_TARGET in the server parameter file (spfile).
To use the command line (ALTER SYSTEM), set a value for SGA_TARGET and then set the parameters for all of the autotuned pools to 0, as follows:


SQL> alter system set sga_target=160M scope=both;
System altered.

SQL> alter system set db_cache_size=0;
System altered.

SQL> alter system set shared_pool_size=0;
System altered.


If you don't set the values for each of the autotuned pools to zero after switching to ASMM, whatever value you had set for a parameter will function as a lower threshold value for that pool—ASMM won't go below a nonzero value, even if it needs the memory for another autotuned pool.