Shulou(Shulou.com)06/01 Report--

Chapter 6 of "Administrator's Guide" introduces memory management for ORACLE instances. In 11g, ORACLE uses automatic memory management (AMM) by default to manage SGA and PGA sizes. In addition to the default method, you can also use automatic shared memory management SGA (ASMM), manual shared memory management SGA, automatic PGA memory management, and manual PGA memory management.

1. Automatic memory management

Automatic memory management is the most convenient way of memory management, only need to set the value of the parameter MEMORY_TARGET, ORACLE will allocate SGA and PGA according to the actual needs.

1.1 apply automatic memory management

1. Connect to SQL*PLUS with the identity of SYSDBA

two。 Calculate the value of MEMORY_TARGET

1) show parameter target, check the values of sga_target and pga_aggregate_target

2) check the value of the maximum pga assigned after the instance is started

Select round (value/1024/1024) "MB"

From v$pgastat

Where name='maximum PGA allocated'

3) MEMORY_TARGET=sga_target+max (pga_aggregate_target,maximum PGA allocated)

3. You can use the value of MEMORY_TARGET calculated in the second step, or set it to be greater than the calculated value, which is marked as m.

4. You can use MEMORY_TARGET or a value greater than MEMORY_TARGET as the value of MEMORY_MAX_TARGET, and MEMORY_MAX_TARGET is the maximum value that MEMORY_TARGET can adjust to, marked n.

5. Modify the value of MEMORY_MAX_TARGET:

ALTER SYSTEM SET MEMORY_MAX_TARGET = nM SCOPE = SPFILE

6. Restart the database

7. Modify the value of MEMORY_TARGET.

ALTER SYSTEM SET MEMORY_TARGET = nM

ALTER SYSTEM SET SGA_TARGET = 0

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0

1.2 Monitoring automatic memory management

Select * from v$memory_target_advice order by memory_size

Second, automatic shared memory management

Automatic shared memory Management (ASMM) simplifies the management of SGA, and by setting the value of SGA_TARGRT, the database automatically manages the value of the SGA component. The parts that SGA can be managed automatically are shared pool, large pool, Java pool, buffer cache, Streams pool, and the parts that need to be manually set are log buffer, keep and recycle buffer caches, and Nonstandard block size buffer caches.

The view that monitors ASMM memory management is v$sga_target_advice:

Select * from v$sga_target_advice order by sga_size

Apply automatic shared memory management:

1. Switch from manual shared memory management to automatic shared memory management

1) run the following SQL:

SELECT round (SELECT SUM (value) FROM V$SGA)-(SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)) / 1024pm 1024pr 2) "SGA_TARGET"

FROM DUAL

2) set the value of SGA_TARGET:

ALTER SYSTEM SET SGA_TARGET=value [SCOPE= {SPFILE | MEMORY | BOTH}]

The value of value can be the value calculated in 1), or the sum of the parts of SGA to the value in SGA_MAX_SIZE.

3) specify the minimum values of SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, DB_CACHE_SIZE and STREAMS_POOL_SIZE (can be 0 or non-0 values).

two。 Switch from AMM to ASMM:

1) ALTER SYSTEM SET MEMORY_TARGET = 0

2) set the value of SGA_TARGET

3) specify the minimum values of SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, DB_CACHE_SIZE and STREAMS_POOL_SIZE (can be 0 or non-0 values).

Third, manual shared memory management

In this mode, you need to set the values for each part of the SGA, and if not, the default values are used.

To enable this mode, you need to disable the values of MEMORY_TARGET and SGA_TARGET.

Automatic PGA memory management

The value of pga_aggregate_target needs to be set.

Fifth, manual PGA memory management

To set the value of workarea_size_policy to manual, you need to set SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, CREATE_BITMAP_AREA_SIZE. The value of.

Oracle strongly recommends that you use the mode of automatically managing PGA.

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.