Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the knowledge points related to AMM and ASMM". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the knowledge points related to AMM and ASMM"?

1. Knowledge related to AMM:

1. Since oracle 11.1, oracle provides the function of automatic SGA and PGA automatic management through MEMORY_TARGET parameters. From this version, there is no need to explicitly set SGA_TARGET and PGA_AGGREGATE_TARGET, which is supported in linux, windows, solaris, hpux, aix.

two。 On linux machines that use the MEMORY_TARGET parameter, ORA-00845 is encountered during oracle startup because the size of / dev/shm is smaller than that of memory_target (df-k / dev/shm). If you set memory_max_target > memory_target, you need to ensure that / dev/shm is at least memory_max_target.

3. Starting from 11.1.0.6.0, ASM instances implement automatic management of SGA and PGA by default. (automatic memory management of ASM instances is disabled. Memory_target must be manually set to 0DBCA interface. This change will not be supported. This bug has been revised to 11.1.0.7.0)

The size allocated by shared pool in SGA before version 4.10.1 = the cost of internal SGA in shared_pool_size+, which is used to maintain the state of components in SGA. Starting from 10.1, shared_pool_size will include the cost of internal SGA.

5. In manual memory management mode, ORA-00371 is caused because shared_pool_size is too small to accommodate internal SGA overhead

6. SGA_MAX_SIZE is set to the maximum value in memory_target and memory_max_target by default on non-windows32 32-bit operating systems, and the virtual address space is available on sga_max_size=60%memory_target+60%memory_max_target+25% operating systems

7. Automatic memory management cannot be enabled when the lock_sga initialization parameter is set to true

2. Enable and configure AMM:

SQL > show parameter lock_sga-make sure lock_sga is false

SQL > show parameter target-determine the current SGA_TARGET and PGA_AGGREGATE_TARGET values

SQL > select value from v$pgastat where name='maximum PGA allocated';-determine the PGA of the largest instance since DB was last started

Memory_target = sga_target + max (pga_aggregate_target, maximum PGA allocated)

Determine a value as large as possible for MEMORY_MAX_TARGET, equal to or greater than SGA_TARGET

When DB uses spfile: ALTER SYSTEM SET MEMORY_MAX_TARGET = nM SCOPE = SPFILE; when DB uses pfile: edit pfile setting memory_max_target = nM memory_target = mM

Shut down and restart DB

ALTER SYSTEM SET MEMORY_TARGET = nM; ALTER SYSTEM SET SGA_TARGET = 0; ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0

-! If you set MEMORY_TARGET but not MEMORY_MAX_TARGET in pfile, the database automatically sets the MEMORY_MAX_ target value to the MEMORY_TARGET value

-! If you set MEMORY_MAX_TARGET but not MEMORY_TARGET in pfile, MEMORY_TARGET defaults to 0. After DB starts, you can dynamically change the value of MEMORY_TARGET not exceeding MEMORY_MAX_TARGET.

-! You can also set SGA_TARGET or PGA_AGGREGATE_TARGET to a value other than 0, which will be the minimum value assigned by SGA or PGA

Third, monitor and optimize AMM:

SQL > select * from v$memory_target_advice order by memory_size

-MEMORY_SIZE_FACTOR indicates the size of the assumed replacement MEMORY_TARGET

-ESTD_DB_TIME represents the DB time required under this estimated MEMORY_TARGET size

2. Knowledge related to ASMM:

1.SGA_MAX_SIZE setting: if not, oracle will select the sum or default value of all specified components during initialization. If the value set is smaller than the memory allocated to all components, the database will ignore the value of this setting.

2.SGA_TARGET setting: before setting, and in order to enable automatic shared memory management, STATISTICS_LEVEL must be set to TYPICAL (the default) or ALL. If this parameter is set, the size of the auto-resizing component must be set to 0 or minimum.

Automatic resizing components in 3.SGA: Fixed SGA, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, DB_CACHE_SIZE, STREAMS_POOL_SIZE

Components in 4.SGA that cannot be automatically resized (fixed): LOG_BUFFER, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, DB_nK_CACHE_SIZE

Unit of memory allocated in 5.SGA: the smallest unit that dynamically allocates the size of each component in SGA is particles. Generally speaking, on most platforms, if the size of SGA is 1G, the size of particles is 16m (if SGA > 1G on 32-bit NT, the size of particles is 8m).

6. Query V$SGAINFO or V$SGA_DYNAMIC_COMPONENTS to see the size of the particles. If the memory manually allocated to the component in the SGA is not a multiple of the particles, then the DB actually allocates (rounded to the minimum granularity multiple) to the component (rounded to the minimum granularity multiple) greater than the allocation value is the small value of the particle multiple.

7.SGA_MAX_SIZE cannot be modified dynamically

3. Enable and configure ASMM:

From MSMM to ASSM:

1. Get the value of SGA_TARGET SELECT ((SELECT SUM (value) FROM V$SGA)-(SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)) "SGA_TARGET" FROM DUAL

two。 Modify the value of sga_target in pfile and restart DB, or ALTER SYSTEM SET SGA_TARGET=value [SCOPE= {SPFILE | MEMORY | BOTH}]

3. By modifying the initialization parameters corresponding to the respective dynamic tuning components in the pfile to 0 or the minimum allocation, or the minimum expected value of alter system set% _ size=0-

From AMM to ASMM:

1.ALTER SYSTEM SET MEMORY_TARGET = 0

two。 By modifying the initialization parameters corresponding to the respective dynamic tuning components in the pfile to 0 or the minimum allocation, or the minimum expected value of alter system set% _ size=0-

-when ASMM is enabled, you can dynamically adjust the minimum value of the corresponding auto-adjusting component, which only limits the size of the component and cannot be reduced below this value, but does not limit the maximum value of the component.

-before dynamically setting a non-zero value of sga_target, that is, before enabling ASMM, it is best to restart DB, because the shared pool cannot be dynamically shrunk because it contains some open cursors, PL/SQL packages and the execution status of sql

-manually resized components can also be changed dynamically, but the corresponding values of increase or decrease are dynamically obtained or released from the automatic resizing component, and a precise value is set.

Fourth, monitor and optimize ASMM:

SQL > select * from v$sga_target_advice order by sga_size

5. Recommended reference (from Yong Huang):

Refer to the following when configuring the total size:

When there is plenty of memory in the operating system, shared pool is generally set to 1 to 3G initially, and the rest is allocated to buffer cache.

At least 2G of db_cache_size in 10gASMM

The use of ASMM in AMM will be disabled in 11g because large buffer pages can be used

Thank you for your reading, the above is the content of "what are the knowledge points related to AMM and ASMM". After the study of this article, I believe you have a deeper understanding of what are the knowledge points related to AMM and ASMM, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.