ORACLE 11G記憶體管理方式

標籤：

 

SGA包含的組件：

組件名	說明	參數
buffer cache	存放從資料檔案中讀取的資料拷貝，所有使用者之間是可以共用的	db_cache_sizedb_keep_cache_sizedb_recycle_cache_sizedb_nk_cache_size
redo log buffer	redo資料	log_buffer
shared pool	存放庫緩衝和資料字典緩衝，結果緩衝，並存執行訊息緩衝，以及控制結構資訊	shared_pool_sizeshared_pool_reserved_sizeresult_cache_max_size*
large pool	共用模式下為會話分配記憶體，oracle xa，並行查詢快取，rman	large_pool_size
java pool	為java代碼分配記憶體	java_pool_size
streams pool	用於oracle stream	streams_pool_size

 

1.AMM(Automatic Memory Management) – 統一管理SGA和PGA
 -memory_target
 -memory_max_target

2.ASSM(Automatic Shared Memory Management) – 自動管理SGA  

自動管理SGA(buffer cache、shared pool、large pool、java pool、streams pool)，使用$sga_target_advice進行調優  

-sga_target：動態參數  

-sga_max_size

 

在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 – 手動管理SGA
手工管理SGA的時候，需要手動設定以下參數的值：
 -db_cache_size
 -java_pool_size
 -large_pool_size
 -log_buffer
 -shared_pool_size
oracle在為這些組件分配記憶體的時候，以granules為單位。SGA<1G時，granules以4mb為單位；SGA>1G時，granules以16mb為單位

調優涉及的部分視圖：
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                     STATIC                  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 COMPLETE  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-DEC-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_2k_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-15DEFAULT 8K buffer cache        STATIC                  db_8k_cache_size                          0           0          0 COMPLETE  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_keep_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-15large pool                     STATIC                  large_pool_size                           0   268435456  268435456 COMPLETE  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                     STATIC                  large_pool_size                           0   268435456  268435456 COMPLETE  26-DEC-15 26-DEC-15java pool                      STATIC                  java_pool_size                            0   469762048  469762048 COMPLETE  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_cache_size                          0           0          0 COMPLETE  26-DEC-15 26-DEC-15DEFAULT 8K buffer cache        STATIC                  db_8k_cache_size                          0           0          0 COMPLETE  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_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 LAST_OPER GRANULE_SIZE------------------------------ ------------ ---------- ---------- ------------------- ---------- ------------- --------- --------- ------------shared pool                      6576668672 6576668672 6576668672                   0          0 STATIC                                67108864large pool                        268435456  268435456  268435456                   0          0 STATIC                                67108864java pool                         469762048  469762048  469762048                   0          0 STATIC                                67108864streams pool                      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          0SQL> 

 

ORACLE 11G記憶體管理方式