Oracle Memory Automatic Management

Starting with Oracle 10g, Oracle provides a new feature for automatic SGA management (ASMM, or automatic Shared Memory Management). The so-called asmm, means that we no longer need to manually set the shared pool, buffer pool and other memory pool size, but for the SGA set a total size can be. The Oracle 10g database automatically adjusts the size of each component based on changes in system load so that memory can always flow to where it is most needed.

For example, assuming that a system is an OLTP application during the day, it will require more buffer cache. And this system belongs to DSS application at night. For DSS applications, many SQL statements are done in parallel because they are all-table scans. As we know, it takes a few subordinate processes to do the work, and the subordinate processes are allocated from the large pool. As a result, the evening will require more large pool. If we enable ASMM, the database will automatically adjust the size of the memory according to the load changes, without the need for manual adjustment by the DBA.

Oracle 10g provides a new initialization parameter: Sga_target to start the ASMM, which defines the total capacity of the entire SGA. Also, the initialization parameter statistics_level must be set to typical or all to start the ASMM or, if set to Basic, close asmm.

ASMM can only automatically resize 5 memory pools: Shared pool, buffer cache, large pool, Java pool, and stream pool. We no longer need to set the five initialization parameters of Shared_pool_size, Db_cache_size, Large_pool_size, Java_pool_size, Streams_pool_size. Other memory pools, such as log buffer, keep buffer cache and so on, still require the DBA to manually adjust.

For example, suppose we set the Sga_target to 500MB, which means the total size of the SGA is 500MB. However, if we need to configure the 100MB keep buffer cache, you must manually set the parameter db_keep_cache_size to 100MB. At the same time, if the setting parameter Log_buffer is 3MB, then the total capacity of 5 parts that can be adjusted by shared pool, buffer cache and so on is 397MB (500-100-3=397).

Oracle 10g also provides another initialization parameter, Sga_max_size. Sga_target value can not exceed sga_max_size value, modify the sga_max_size, must restart the instance to take effect, and Sga_target can be modified online, immediately effective, without restarting the instance.

A background process named Mman (Memory Manager) was introduced to implement Asmm,oracle. At short intervals, the Mman process starts, and then asks the various memory component advisors provided by Oracle, such as the buffer cache advisor, and the shared pool consultant, which, depending on the current load, will be able to automatically adjust the 5 memory pools, Recommended size size, returned to Mman. The Mman process then sets the individual memory pools based on the value returned. At the same time, if we use SPFile, we will also write the proposed values of these consultants into SPFile. This way, the next time you start the instance, you can use the recommended value from the advisor directly as a basis for starting the memory pool.

What happens if we enable ASMM and manually set the size of the memory pool that can be automatically resized, such as setting the parameter shared_pool_size to a value other than 0? For Oracle 10g, we set a value for the automatically sized memory component, and the value we set is the minimum value that is automatically adjusted. That is, assuming that Sga_target is 4GB and we set the Shared_pool_size to 600MB, Mman will never set the shared pool to 600MB below when automatic adjustment is made.

In fact, in order to use Asmm,oracle for these 5 automatically adjustable components and provide 5 parameters to control their size size, "__" (two bottom line). We export the current spfile to the pfile.

sql> create pfile= '/u01/init.ora ' from SPFile;

Sql>!vi/u01/init.ora

After opening the pfile, we will find that the first 5 lines of the file will display the following (the exact value may be different):

ora10g.__db_cache_size=134217728

ora10g.__java_pool_size=4194304

ora10g.__large_pool_size=4194304

ora10g.__shared_pool_size=62914560

Ora10g.__streams_pool_size=0

As you can see, these 5 initialization parameters start with "__", followed by the same parameters that we manually set the size of the memory pool. such as __db_cache_size and db_cache_size corresponding. The arguments that begin with "_" are called Hidden parameters. The so-called hidden parameters, is not the official document to explain the meaning of the parameters. This parameter will change depending on the version. These 5 hidden parameters (such as __shared_pool_size) are modified by the Mman process, and the corresponding other parameters (such as Shared_pool_size) are set by the DBA. Therefore, when we start the database, the database kernel compares the initialization parameter __shared_pool_size with the shared_pool_size. If Shared_pool_size is not set, or is set to 0, or the value set is smaller than __shared_pool_size, the size of the memory pool is set by Mman automatically adjusted value. Otherwise, set the size of the memory pool with the value set by the DBA.