Oracle has introduced Automatic Shared Memory Management in Oracle 10g and thus allows automatic tuning of five important component of SGA [Shared Global Area].
Starting with Oracle 9i, Oracle has made some SGA components [Shared pool, default Buffer Cache, Large Pool] as Dynamic and allows DBA to alter them without bouncing the database. The maximum size of SGA was restricted by new initialization parameter SGA_MAX_SIZE.
In Oracle 10g, the ASMM [Automatic Shared Memory Management] has been introduced to relieve DBAs from sizing the following five main SGA components also called auto-tuned parameter :
1. Shared Pool - SHARED_POOL_SIZE
2. Large Pool - LARGE_POOL_SIZE
3. Java Pool - JAVA_POOL_SIZE
4. DB Cache - DB_CACHE_SIZE
5. Stream Pool - STREAM_POOL_SIZE
ASMM is driven by new initialization parameter SGA_TARGET and is managed by new background Process MMAN [Memory Manager]. MMAN will take regular memory snapshots and evaluate and adjust the auto tuned parameters of SGA based on the database usage.
If we are using ASMM and if you set non- zero values to any of these auto-tuned parameter, then those values are used as minimum value by ASMM.
There are still lots of SGA components that need to be adjusted manually but take the space is deducted from the total space available for SGA_TARGET itself. These parameters are also called as Manual parameters as shown below:
The SGA_TARGET will be limited by the SGA_MAX_SIZE value. The SGA_MAX_SIZE cannot be modified dynamically.
ASMM is enabled when sga_target is set to a value higher than 0 and when statistics_level is set to either typical or all
SQL> SHOW PARAMETER STATISTICS_LEVEL;
SQL> ALTER SYSTEM SET SGA_TARGET=156M SCOPE=BOTH;
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=0 SCOPE=BOTH;
SQL> ALTER SYSTEM SET LARGE_POOL_SIZE=0 SCOPE=BOTH;
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=0 SCOPE=BOTH;
SQL> ALTER SYSTEM SET STREAMS_POOL_SIZE=0 SCOPE=BOTH;
SQL> ALTER SYSTEM SET DB_CACHE_SIZE=0 SCOPE=SPFILE;
SQL> COLUMN COMPONENT FORMAT A25;
SQL> SELECT component,current_size,user_specified_size FROM v$sga_dynamic_components;