Automatic Shared Memory Management (ASMM)

To use automatic shared memory management, the value of statistics_level must be set to “typical” or “all”. The parameter can be changed without restarting the database.

The following views shows information on SGA resize operations:
V$SGA_RESIZE_OPS – the view contains information about the last 400 completed SGA resize operations.
V$SGA_DYNAMIC_COMPONENTS – Shows current size, minimum size, maximum size, last operation (LAST_OPER_TYPE value: GROW, SHRINK, STATIC), OPER_COUNT (# of times SGA operations performed, at startup the value is 0) during the instance started of SGA components.
V$SGA_DYNAMIC_FREE_MEMORY – Shows free memory available for future SGA memory resize.
V$SGA_CURRENT_RESIZE_OPS – Currently SGA resize operations in progress.
V$SGAINFO – has usage and free size informaton on SGA size of different components.
V$SGASTAT – detail information on usage of SGA
V$SGA_DYNAMIC_COMPONENTS – information on dynamic components of SGA
V$SGA_TARGET_ADVICE – gives information on tuning of SGA_TARGET

Automatic memory management has it’s own background process (mman). It monitors the instance to find the best memory allocation for SGA.

If the database is iin automatic shared memory enabled the value of statistics_level cannot be changed to basic. If the value of sga_target is not set or if the value is 0 automatic shared memory management, statistics_level can be set to basic which means automatic memory management is turned off.

SQL> alter system set statistics_level=basic;
alter system set statistics_level=basic
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled

If SGA_MAX_SIZE is not set or it is less than SGA_TARGET then Oracle will SGA_MAX_SIZE to be the same value as SGA_TARGET. If one tries to increase SGA_TARGET to more than SGA_MAX_SIZE then you will get ORA-00823 so you may want to set SGA_MAX_SIZE which will allow one to increase SGA with restart of the instance.

SQL> show parameter sga;

———————————— ———– ——————————
sga_max_size big integer 160M
sga_target big integer 160M

SQL> alter system set sga_target=170M;
alter system set sga_target=170M
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size