"Reading notes" ORACLE memory management

The 6th chapter of Administrator's Guide describes the memory management of Oracle instances, where Oracle uses automatic memory management (AMM) to manage the size of the SGA and PGA by default in 11g. In addition to the default methods, you can use the automatic shared memory management SGA (ASMM), manual shared memory management SGA, automatic PGA Memory management, and manual PGA memory management.

First, automatic memory management

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

1.1 Apply automatic memory management

1. Connect the Sql*plus with Sysdba's identity

2. Calculate the value of the Memory_target

1) show parameter target, view values for Sga_target and Pga_aggregate_target

2) View the value of the largest PGA allocated after instance startup

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 the Memory_target calculated in the second step, or set a value greater than calculated, as M.

4. You can use Memory_target or a value greater than memory_target as the value of Memory_max_target,Memory_max_target is Memory_ The maximum value that target can adjust to, recorded as N.

5. Modify the value of the Memory_max_target:

ALTER SYSTEM SET memory_max_target = NM SCOPE = SPFILE;

6. Restart the database

7. Modify the value of the 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 the SGA, and by setting the value of SGA_TARGRT, the database automatically manages the value of the SGA component. The portion of the SGA that can be automatically managed is the 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, 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_memo RY))/1024/1024,2) "Sga_target"

From DUAL;

2) Set the value of the Sga_target:

ALTER SYSTEM SET Sga_target=value [scope={spfile| memory| BOTH}]

Value can be a value calculated in 1), or it can be the sum of each part of the SGA to sga_max_size.

3) Specify Shared_pool_size, Large_pool_size, Java_pool_size, Db_cache_size, the minimum value of the streams_pool_size (which can be 0 or a non-0 value).

2. Switch from AMM to ASMM:

1) ALTER SYSTEM SET memory_target = 0;

2) Set the value of the Sga_target

3) Specify the minimum value of Shared_pool_size, Large_pool_size, Java_pool_size, Db_cache_size, and streams_pool_size (either 0 or non-0 values).

Third, manually shared memory management

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

To enable this mode, you need to disable the value of Memory_target, Sga_target.

IV. Automatic PGA Memory management

You need to set the value of Pga_aggregate_target.

V. Manual PGA Memory Management

the value of the Workarea_size_policy is set to Manual, you need to set Sort_area_size, Hash_area_size, Bitmap_merge_area_size, the value of the create_bitmap_area_size.

Oracle strongly recommends using the auto-manage PGA mode.

This article is from the "three countries Cold jokes" blog, please be sure to keep this source http://myhwj.blog.51cto.com/9763975/1827011

Reading notes ORACLE memory management