ORACLE 11G内存管理方式

 

SGA包含的组件：

组件名	说明	参数
buffer cache	存放从数据文件中读取的数据拷贝，所有用户之间是可以共享的	db_cache_size db_keep_cache_size db_recycle_cache_size db_nk_cache_size
redo log buffer	redo数据	log_buffer
shared pool	存放库缓存和数据字典缓存，结果缓存，并行执行消息缓存，以及控制结构信息	shared_pool_size shared_pool_reserved_size result_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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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           325059009   15 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

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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-15 PGA Target                     STATIC                  pga_aggregate_target                      0  1.0201E+10 1.0201E+10 COMPLETE  26-DEC-15 26-DEC-15 java pool                      STATIC                  java_pool_size                            0   469762048  469762048 COMPLETE  26-DEC-15 26-DEC-15 streams pool                   STATIC                  streams_pool_size                         0   134217728  134217728 COMPLETE  26-DEC-15 26-DEC-15 SGA Target                     STATIC                  sga_target                                0  3.0467E+10 3.0467E+10 COMPLETE  26-DEC-15 26-DEC-15 DEFAULT buffer cache           INITIALIZING            db_cache_size                    2.2817E+10  2.2817E+10 2.2817E+10 COMPLETE  26-DEC-15 26-DEC-15 ASM Buffer Cache               STATIC                  db_cache_size                             0           0          0 COMPLETE  26-DEC-15 26-DEC-15 DEFAULT buffer cache           STATIC                  db_cache_size                             0  2.2817E+10 2.2817E+10 COMPLETE  26-DEC-15 26-DEC-15 DEFAULT 2K buffer cache        STATIC                  db_2k_cache_size                          0           0          0 COMPLETE  26-DEC-15 26-DEC-15 DEFAULT 4K buffer cache        STATIC                  db_4k_cache_size                          0           0          0 COMPLETE  26-DEC-15 26-DEC-15 DEFAULT 8K buffer cache        STATIC                  db_8k_cache_size                          0           0          0 COMPLETE  26-DEC-15 26-DEC-15 DEFAULT 16K buffer cache       STATIC                  db_16k_cache_size                         0           0          0 COMPLETE  26-DEC-15 26-DEC-15 DEFAULT 32K buffer cache       STATIC                  db_32k_cache_size                         0           0          0 COMPLETE  26-DEC-15 26-DEC-15 KEEP buffer cache              STATIC                  db_keep_cache_size                        0           0          0 COMPLETE  26-DEC-15 26-DEC-15 RECYCLE buffer cache           STATIC                  db_recycle_cache_size                     0           0          0 COMPLETE  26-DEC-15 26-DEC-15 large pool                     STATIC                  large_pool_size                           0   268435456  268435456 COMPLETE  26-DEC-15 26-DEC-15   16 rows selected.   SQL>

v$memory_target_advice
v$sga_current_resize_ops
v$sga_resize_ops

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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-15 large pool                     STATIC                  large_pool_size                           0   268435456  268435456 COMPLETE  26-DEC-15 26-DEC-15 java pool                      STATIC                  java_pool_size                            0   469762048  469762048 COMPLETE  26-DEC-15 26-DEC-15 streams pool                   STATIC                  streams_pool_size                         0   134217728  134217728 COMPLETE  26-DEC-15 26-DEC-15 DEFAULT buffer cache           INITIALIZING            db_cache_size                    2.2817E+10  2.2817E+10 2.2817E+10 COMPLETE  26-DEC-15 26-DEC-15 ASM Buffer Cache               STATIC                  db_cache_size                             0           0          0 COMPLETE  26-DEC-15 26-DEC-15 RECYCLE buffer cache           STATIC                  db_recycle_cache_size                     0           0          0 COMPLETE  26-DEC-15 26-DEC-15 DEFAULT 2K buffer cache        STATIC                  db_2k_cache_size                          0           0          0 COMPLETE  26-DEC-15 26-DEC-15 DEFAULT 4K buffer cache        STATIC                  db_4k_cache_size                          0           0          0 COMPLETE  26-DEC-15 26-DEC-15 DEFAULT 8K buffer cache        STATIC                  db_8k_cache_size                          0           0          0 COMPLETE  26-DEC-15 26-DEC-15 DEFAULT 16K buffer cache       STATIC                  db_16k_cache_size                         0           0          0 COMPLETE  26-DEC-15 26-DEC-15 DEFAULT 32K buffer cache       STATIC                  db_32k_cache_size                         0           0          0 COMPLETE  26-DEC-15 26-DEC-15 KEEP buffer cache              STATIC                  db_keep_cache_size                        0           0          0 COMPLETE  26-DEC-15 26-DEC-15 DEFAULT buffer cache           STATIC                  db_cache_size                             0  2.2817E+10 2.2817E+10 COMPLETE  26-DEC-15 26-DEC-15   14 rows selected.   SQL>

v$sga_dynamic_components

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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                                67108864 large pool                        268435456  268435456  268435456                   0          0 STATIC                                67108864 java pool                         469762048  469762048  469762048                   0          0 STATIC                                67108864 streams pool                      134217728  134217728  134217728                   0          0 STATIC                                67108864 DEFAULT buffer cache             2.2817E+10 2.2817E+10 2.2817E+10                   0          0 INITIALIZING                          67108864 KEEP buffer cache                         0          0          0                   0          0 STATIC                                67108864 RECYCLE buffer cache                      0          0          0                   0          0 STATIC                                67108864 DEFAULT 2K buffer cache                   0          0          0                   0          0 STATIC                                67108864 DEFAULT 4K buffer cache                   0          0          0                   0          0 STATIC                                67108864 DEFAULT 8K buffer cache                   0          0          0                   0          0 STATIC                                67108864 DEFAULT 16K buffer cache                  0          0          0                   0          0 STATIC                                67108864 DEFAULT 32K buffer cache                  0          0          0                   0          0 STATIC                                67108864 Shared IO Pool                            0          0          0                   0          0 STATIC                                67108864 ASM Buffer Cache                          0          0          0                   0          0 STATIC                                67108864   14 rows selected.   SQL>

v$sga_dynamic_free_memory

+ View Code?

1 2 3 4 5 6 7

SQL> select * from v$sga_dynamic_free_memory;   CURRENT_SIZE     CON_ID ------------ ----------     50331648          0   SQL>