Oracle automatic shared memory management (ASMM)

Oracle Auto-Shared Memory Management (ASMM) starts from Oracle 10 Gb. Oracle provides new features of Automatic SGA Management (ASMM for short, that is, Automatic Shared Memory Management. The so-called ASMM means that we no longer need to manually set the size of several memory pools such as shared pool and buffer pool, but to set a total size for SGA. Oracle 10 Gb database automatically adjusts the size of each component according to the system load changes, so that the memory can always flow to the place where it is most needed. Www.2cto.com, for example, if a system belongs to OLTP application during the day, a large amount of buffer cache is required. The system belongs to the DSS application at night. For DSS applications, many SQL statements perform full table scan, so they are completed in parallel. As we know, when the job is run, several subordinate processes are required to complete the job, and the subordinate processes are allocated from the large pool. Therefore, a large number of large pools are required at night. If we enable ASMM, the database will automatically adjust the memory size based on the load changes, so you do not need to manually adjust the DBA. Oracle 10 Gb provides a new initialization parameter: sga_target to start ASMM, which defines the total capacity of the entire SGA. At the same time, the initialization parameter statistics_level must be set to typical or all to start ASMM. Otherwise, if set to basic, ASMM is disabled. ASMM can only automatically adjust the size of five memory pools: shared pool, buffer cache, large pool, java pool, and stream pool. We no longer need to set the shared_pool_size, db_cache_size, large_pool_size, java_pool_size, and streams_pool_size initialization parameters. Other memory pools, such as log buffer and keep buffer cache, still need to be manually adjusted by DBAs. For example, if we set sga_target to 500 MB, the total SGA capacity is 500 MB. However, if we need to configure a 100 MB keep buffer cache, We must manually set the parameter db_keep_cache_size to MB. If the log_buffer parameter is set to 3 MB, the total capacity of the five parts that can be adjusted, such as the shared pool and buffer cache, is 397 MB (500-100-3 = 397 ). Oracle 10 Gb also provides another initialization parameter sga_max_size. The value of sga_target cannot exceed the value of sga_max_size. When sga_max_size is modified, the instance must be restarted to take effect. sga_target can be modified online and takes effect immediately without restarting the instance. To implement ASMM, Oracle introduces a new background process named MMAN (Memory Manager. The MMAN process starts every very short period of time, and then asks about various memory component consultants provided by Oracle, such as buffer cache consultants and shared pool consultants, based on the current load, these consultants return the recommended size and size of the five memory pools that can be automatically adjusted to MMAN. Therefore, the MMAN process sets each memory pool based on the returned value. At the same time, if spfile is used, the recommended values from these consultants will be written into the spfile. In this way, when you start the instance next time, you can directly use the recommended value from the consultant as the basis for starting the memory pool. If we enable ASMM and manually set the size of the memory pool that can be automatically adjusted, for example, when the shared_pool_size parameter is set to a non-0 value, what will happen? For Oracle 10 Gb, we set a value for the memory component that automatically adjusts the size, and the value we set will be used as the minimum value for automatic adjustment. That is to say, if sga_target is 4 GB and shared_pool_size is set to 600 MB, MMAN will never set the shared pool to below MB during automatic adjustment. In fact, in order to use ASMM, Oracle provides five more parameters to control the size of these five automatically adjustable components, starting with "_" (two underlines ). We export the current spfile to pfile. SQL> create pfile = '/u01/init. ora' from spfile; SQL>! Vi/u01/init. after ora opens the pfile, we will find that the first five lines of the file will display the following content (the specific values 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. The five initialization parameters start with "_", and the subsequent parameters are the same as those for manually setting the memory pool size. For example, _ db_cache_size corresponds to db_cache_size. Such parameters starting with "_" are called hidden parameters. Hidden parameters are parameters that are not described in the official documentation. This parameter is changed based on different versions. These five hidden parameters (for example, _ shared_pool_size) are modified by the MMAN process, and other parameters (for example, shared_pool_size) are set by the DBA. Therefore, when we start the database, the database kernel will compare the initialization parameter _ shared_pool_size and shared_pool_size. If shared_pool_size is not set, or it is set to 0, or the value is smaller than _ shared_pool_size, the size of the memory pool is set based on the value automatically adjusted by MMAN. Otherwise, set the size of the memory pool with the value set by DBA. What if we modify the size of a memory pool that can be automatically adjusted during database operation? If the value we set is greater than the value automatically adjusted by MMAN, the memory pool is immediately adjusted to the set value, at the same time, the value we set is the new and automatically adjusted minimum value of MMAN. Otherwise, if the set value is smaller than the automatically adjusted value of MMAN, the size of the memory pool will not change, and the value we set will only exist as the minimum value automatically adjusted. For example, the size of the shared pool automatically adjusted by MMAN is 150 MB, that is, the size of _ shared_pool_size is 150 MB, and the size of shared_pool_size is 60 MB. At this time, if we set the shared_pool_size parameter from 60 MB to 150 MB, the size of the shared pool is still MB, but the new MB will be used as the lower limit for automatic adjustment; if we set the shared_pool_size parameter from 60 MB to 200 MB, the shared pool will expand immediately, from 200 MB to MB, and MB will also be used as the new lower limit for automatic adjustment. Let's verify it. View v $ sga_dynamic_components records the size of each memory pool that can be dynamically adjusted. SQL> SELECT component, current_size/1024/1024 size_mb 2 FROM v $ sga_dynamic_components where comp; COMPONENT SIZE_MB size ------------ shared pool 80 the current shared pool size automatically adjusted by MMAN is 80 MB. SQL> alter system set shared_pool_size = 70 M; SQL> SELECT component, current_size/1024/1024 size_mb 2 FROM v $ sga_dynamic_components where comp; COMPONENT SIZE_MB ---------------------------------- ------------ shared pool 80 we set shared_pool_size to 70 MB, which is smaller than the automatically adjusted value. We can see that the shared pool does not have
It is still 80 MB. We will expand it from 80 MB to 100 MB. SQL> alter system set shared_pool_size = 100 M; SQL> SELECT component, current_size/1024/1024 size_mb 2 FROM v $ sga_dynamic_components where comp; COMPONENT SIZE_MB bytes ------------ shared pool 100 obviously, as long as the value we set is greater than the automatically adjusted value, it will take effect immediately. At the same time, if we have enabled ASMM, we have not specified a specific value for the five memory pool parameters that can be automatically adjusted. When data
When the database runs in ASMM for a period of time, we disable ASMM. What will happen? Let's take a look at the experiment below. SQL> select name, value from v $ parameter 2 where name in ('shared _ pool_size ', 'db _ cache_size', 'java_pool_size ', 'large _ pool_size ', 'streams_pool_size '); name value should -------------- shared_pool_size 96468992 bytes 0 java_pool_size 0 streams_pool_size 0 db_cache_size 0 you can see that besides the shared pool specified by DBA (because the memory is greater than 0)
All are specified by ASMM. SQL> select component, current_size FROM v $ sga_dynamic _ components 2 where component like '% pooled' or comp; COMPONENT SIZE_MB -------------------------------- ----------- shared pool 138412032 large pool 4194304 java pool 4194304 streams pool 0 DEF *** T buffer cache 373293056 we can see that ASMM is based on the current load, specify the size of the five memory pools. SQL> alter system set sga_target = 0; SQL> select name, value from v $ parameter 2 where name in ('shared _ pool_size ', 'db _ cache_size', 'java_pool_size ', 'large _ pool_size ', 'streams_pool_size'); name value -------------- bytes 138412032 bytes 4194304 java_pool_size 4194304 streams_pool_size 0 db_cache_size 373293056 when we set sga_target to 0 to disable ASMM, oracle automatically calculates the size of the current memory pool.
Assign the corresponding initialization parameters (shared_pool_size, db_cache_size, etc ). We can also note that,
The value of shared_pool_size is automatically adjusted by ASMM instead of 96468992 specified by DBA.
138412032.