Automatic Shared Memory Management (ASMM)- Memory Structures

In all of my recent DBA experience (including databases in Oracle’s cloud), this is the most common method for managing the SGA memory. Under ASMM, the primary parameter for sizing the total auto-tuned components is SGA_TARGET, which may be dynamically sized while the database is up and running, up to the setting of the SGA_MAX_ SIZE parameter.

This defaults to be equal to the SGA_TARGET, so if you plan on increasing the SGA_TARGET, you must have set the SGA_MAX_SIZE larger before starting the database instance. If MEMORY_TARGET is currently in use, set it to zero.

And then set SGA_TARGET to a nonzero value, for example:

SQL> alter system set memory_target=0 scope=spfile; SQL> alter system set sga_target = 1600m scope=spfile;

After setting the SGA_TARGET, I’ll restart my database and verify the setting:

SQL> startup force;
SQL> show parameter sga_target

NAME TYPE VALUE

sga_target big integer 1600M

If SGA_TARGET is set, then the following memory components are automatically sized by Oracle:

•\ Buffer cache (DB_CACHE_SIZE)
•\ Shared pool (SHARED_POOL_SIZE)
•\ Large pool (LARGE_POOL_SIZE)
•\ Java pool (JAVA_POOL_SIZE)
•\ Streams pool (STREAMS_POOL_SIZE)
•\ Data transfer cache (DATA_TRANSFER_CACHE_SIZE)

If you set the preceding parameters to nonzero values (with SGA_TARGET set), then those values set minimal values for those memory areas. Typically, you would set nonzero values for these parameters if you knew the application using the database needed a large minimum value to perform properly.

The database will use the SGA_TARGET value, minus the size of any of the other manually sized components such as the DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, and so on, and use that amount of memory to size the default buffer pool, shared pool, large pool, and Java pool.

Dynamically at runtime, the instance will allocate and reallocate memory among those four memory areas as needed. Instead of returning an ORA-04031 “Unable to allocate N bytes of shared memory” error to a user when the shared pool runs out of memory, the instance could instead choose to shrink the buffer cache by some number of megabytes (a granule size) and increase the shared pool by that amount.

Note To use ASMM, the parameter STATISTICS_LEVEL must be set to TYPICAL (default value) or ALL. If statistics collection is not enabled, the database will not have the historical information needed to make the necessary sizing decisions.

Over time, as the memory needs of the instance are ascertained, the size of the various SGA components would become more or less fixed in size. The database also remembers the sizes of these four components across database startup and shutdown so that it doesn’t have to start all over again figuring out the right size for your instance each time. It does this via four double-underscore parameters: __db_cache_size,__java_pool_size, __large_pool_size, and __shared_pool_size.

During a normal or immediate shutdown, the database will record these values to the spfile and use them at startup to set the default sizes of each area. We can view those as follows:

SQL> create pfile=’/tmp/tmp.ora’ from spfile; SQL> !cat /tmp/tmp.ora

Here is a snippet of the output:

CDB.__data_transfer_cache_size=0
CDB.__db_cache_size=872415232
CDB.__inmemory_ext_roarea=0
CDB.__inmemory_ext_rwarea=0
CDB.__java_pool_size=16777216
CDB.__large_pool_size=33554432
CDB.__oracle_base=’/opt/oracle’#ORACLE_BASE set from environment
CDB.__pga_aggregate_target=318767104
CDB.__sga_target=1677721600
CDB.__shared_io_pool_size=83886080
CDB.__shared_pool_size=637534208
CDB.__streams_pool_size=0
CDB.__unified_pga_pool_size=0

Additionally, if you know you want a certain minimum value to be used for one of the five areas, you may set that parameter in addition to setting the SGA_TARGET. The instance will use your setting as the lower bound, or the smallest size that particular area may be.

Note The SGA_TARGET parameter is optional for pluggable databases. When this parameter is set for a pluggable database, it specifies the maximum SGA that the pluggable database can use at any time. When this parameter is not set at the pluggable database level, then it has no limit for the amount of SGA it can use, other than the root container database’s SGA size.

Leave a Reply

Your email address will not be published. Required fields are marked *