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