ORACLE 11G Memory Management method

The SGA contains components:

Component Name	Description	Parameters
Buffer cache	Storing copies of data read from a data file that can be shared among all users	Db_cache_sizedb_keep_cache_sizedb_recycle_cache_sizedb_nk_cache_size
Redo Log Buffer	Redo Data	Log_buffer
Shared pool	Repository Cache and data dictionary cache, result caching, parallel execution of message caching, and control structure information	shared_pool_sizeshared_pool_reserved_sizeresult_cache_max_size*
Large pool	Shared mode allocates memory for sessions, Oracle XA, parallel query cache, Rman	Large_pool_size
Java Pool	allocating memory for Java code	Java_pool_size
Streams Pool	for Oracle Stream	Streams_pool_size

1.AMM (Automatic Memory Management) – Unified management of the SGA and PGA
-memory_target
-memory_max_target

2.ASSM (Automatic Shared Memory Management) – Automatic management of SGA

Automatic management of the SGA (buffer cache, shared pool, large pool, Java pool, streams pool), tuning with $sga_target_advice

-sga_target: Dynamic Parameters

-sga_max_size

The following sections are not managed in ASSM:

-log Buffer

-other buffer caches (such as KEEP, RECYCLE, and other nondefault block size)

-fixed SGA and other internal allocations

Sql> select * from V$sga_target_advice; Sga_size sga_size_factor estd_db_time estd_db_time_factor estd_physical_reads-------------------------------------     --------------------------------------7264.25 1187001 4.5075 3994550959              10896.375 453075 1.7205 1350811286 14528.5 276980             1.0518 585978929 18160.625 263602 1.001 525730243 21792                   .75 263339 1 525730243 25424.875 263339           1 525730243 29056 1 263339 1 525730243 32688           1.125 263339 1 525730243 36320 1.25 263339 1 525730243 39952 1.375 263339 1 525730243 43584             1.5 263339 1 525730243 47216 1.625 263339           1 525730243 50848 1.75 218598.8301 325059009 54480           1.875 218492.8297 325059009 58112 2 218492.8297 32505900915 rows selected.  Sql>

3.manual Shared Memory management – manually manage the SGA
When you manually manage the SGA, you need to manually set the values for the following parameters:
-db_cache_size
-java_pool_size
-large_pool_size
-log_buffer
-shared_pool_size
Oracle allocates memory for these components in granules units. SGA<1G, granules in 4MB units; sga>1g, granules in 16MB

Some of the views involved in tuning:
V$memory_resize_ops

Sql> select * from V$memory_resize_ops; COMPONENT oper_type oper_mode PARAMETER initial_size target_size final_size STATUS start_tim end_time--------------------------------------------------------------------------------------- -------------------------------------------------------shared pool STATIC shared_ Pool_size 0 6576668672 6576668672 complete 26-dec-15 26-DEC-15PGA Target ST  atic pga_aggregate_target 0 1.0201E+10 1.0201E+10 complete 26-dec-15 26-dec-15java Pool STATIC java_pool_size 0 469762048 469762048 COMP                         Lete 26-dec-15 26-dec-15streams Pool STATIC streams_pool_size                0 134217728 134217728 Complete 26-dec-15 26-dec-15sga Target STATIC  Sga_target 0 3.0467E+10 3.0467E+10 complete 26-dec-15 26-dec-15default buffer cache INITIALIZING db_cache_size 2.2817E+10 2.2817E+10 2.2817E+10 complete 26-dec-15 26-d          Ec-15asm Buffer Cache STATIC db_cache_size 0 0                             0 Complete 26-dec-15 26-dec-15default buffer cache STATIC db_cache_size 0 2.2817E+10 2.2817E+10 Complete 26-dec-15 26-dec-15default 2K buffer cache STATIC db_2        K_cache_size 0 0 0 Complete 26-dec-15 26-dec-15default 4K buffer Cache STATIC db_4k_cache_size 0 0 0 Complete 26-dec-15 26-dec-15d Efault 8K buffer Cache STATIC db_8k_cache_size 0 0 0 C Omplete 26-dec-15 26-deC-15default 16K buffer Cache STATIC db_16k_cache_size 0 0                         0 Complete 26-dec-15 26-dec-15default 32K buffer cache STATIC db_32k_cache_size 0 0 0 Complete 26-dec-15 26-dec-15keep buffer cache STATIC Db_ke           Ep_cache_size 0 0 0 Complete 26-dec-15 26-dec-15recycle buffer cache STATIC db_recycle_cache_size 0 0 0 Complete 26-dec-15 26-dec-15la Rge pool STATIC large_pool_size 0 268435456 268435456 CO Mplete 26-dec-15 26-dec-1516 rows selected.  Sql>

V$memory_target_advice
V$sga_current_resize_ops
V$sga_resize_ops

Sql> select * from V$sga_resize_ops; COMPONENT oper_type oper_mode PARAMETER initial_size target_size final_size STATUS start_tim end_time--------------------------------------------------------------------------------------- -------------------------------------------------------shared pool STATIC shared_ Pool_size 0 6576668672 6576668672 complete 26-dec-15 26-dec-15large pool ST  atic large_pool_size 0 268435456 268435456 complete 26-dec-15 26-dec-15java Pool STATIC java_pool_size 0 469762048 469762048 COMP                         Lete 26-dec-15 26-dec-15streams Pool STATIC streams_pool_size 0 134217728 134217728 Complete 26-dec-15 26-dec-15default buffer cache INITIALIZING DB_cache_size 2.2817E+10 2.2817E+10 2.2817E+10 complete 26-dec-15 26-dec-15asm Buffer cache STATIC db_cache_size 0 0 0 Complete 26-dec-15 26-dec-          15RECYCLE buffer Cache STATIC db_recycle_cache_size 0 0                          0 Complete 26-dec-15 26-dec-15default 2K buffer cache STATIC db_2k_cache_size 0 0 0 Complete 26-dec-15 26-dec-15default 4K buffer cache STATIC Db_4k_c Ache_size 0 0 0 Complete 26-dec-15 26-dec-15default 8K buffer Cache ST atic db_8k_cache_size 0 0 0 Complete 26-dec-15 26-dec-15defa ULT 16K buffer Cache STATIC db_16k_cache_size 0 0 0 COMP Lete 26-dec-15 26-dec-15DEFAULT 32K buffer Cache STATIC db_32k_cache_size 0 0 0                        Complete 26-dec-15 26-dec-15keep buffer cache STATIC db_keep_cache_size 0 0 0 Complete 26-dec-15 26-dec-15default buffer cache STATIC Db_cache _size 0 2.2817E+10 2.2817E+10 complete 26-dec-15 26-dec-1514 rows selected.  Sql>

V$sga_dynamic_components

Sql> select * from V$sga_dynamic_components; COMPONENT current_size min_size max_size user_specified_size oper_count Last_oper_typ last_oper L Ast_oper granule_size------------------------------------------------------------------------------------------                   --------------------------------------------shared pool 6576668672 6576668672 6576668672 0 0 STATIC 67108864large pool 268435456 26843545                         6 268435456 0 0 STATIC 67108864java Pool 469762048 469762048 469762048 0 0 STATIC 67108864streams Poo                                L 134217728 134217728 134217728 0 0 STATIC         67108864DEFAULT buffer Cache 2.2817E+10 2.2817E+10 2.2817E+10 0 0 INITIALIZING 67108864KEEP Buffer cache 0 0 0          0 0 STATIC 67108864RECYCLE buffer cache 0                   0 0 0 0 STATIC 67108864DEFAULT 2K buffer Cache  0 0 0 0 0 STATIC 67108864DEFAULT 4K                                Buffer Cache 0 0 0 0 0 STATIC                                67108864DEFAULT 8K Buffer Cache 0 0 0 0 0 STATIC          67108864DEFAULT 16K Buffer Cache 0 0 0 0                   0 STATIC 67108864DEFAULT 32K buffer cache 0 0 0        0  0 STATIC 67108864Shared IO Pool 0 0 0          0 0 STATIC 67108864ASM Buffer Cache 0 0 0 0 0 STATIC 6710886414 rows selected.  Sql>

V$sga_dynamic_free_memory

Sql> select * from V$sga_dynamic_free_memory; Current_size     con_id----------------------    50331648          

ORACLE 11G Memory Management method