V$MEMORY_TARGET_ADVICE

 

========================

 

11g中增加自动内存管理

使用memory_target和memory_max_target参数可以实现内存自动管理功能

 

memory_target是动态参数
memory_max_target是静态参数

 

如果设置了memory_max_target而没有设置memory_target
instance创建后memory_target默认为0
启动后可以手动调整其大小

 

与sga和pga自动管理类似
内存自动管理也同样有一个动态性能视图来建议内存分配

这个视图就是V$MEMORY_TARGET_ADVICE

 

 

 

SQL> desc v$memory_target_advice
 Name                       Type
 -------------------------- ----------------------------
 MEMORY_SIZE                NUMBER        --内存总大小
 MEMORY_SIZE_FACTOR         NUMBER        --内存值与实际值的比例
 ESTD_DB_TIME               NUMBER        --消耗的db_time
 ESTD_DB_TIME_FACTOR        NUMBER        --消耗的db_time与当前值的比例
 VERSION                    NUMBER        --版本
 

 

通过观察ESTD_DB_TIME的变化可以得到内存调整的建议
如果内存值增大ESTD_DB_TIME的值确实明显减少
那么增加memory_max_target的总量就是有意义的

我的测试系统是什么都没跑
所以查询出了如下的结果
也就是增加内存没有意义

 

 

 

 

SQL> select * from v$memory_target_advice;

 

 

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION
----------- ------------------ ------------ ------------------- ----------
        400                  1          126                   1          1
        500               1.25          126                   1          1
        600                1.5          126                   1          1
        700               1.75          126                   1          1
        800                  2          126                   1          1

 

从dict中查询出的其他类似的V$_POOL_ADVICE视图

 

 

V$MEMORY_TARGET_ADVICE         Synonym for V_$MEMORY_TARGET_ADVICE

V$SGA_TARGET_ADVICE            Synonym for V_$SGA_TARGET_ADVICE
V$PGA_TARGET_ADVICE            Synonym for V_$PGA_TARGET_ADVICE

V$DB_CACHE_ADVICE              Synonym for V_$DB_CACHE_ADVICE
V$SHARED_POOL_ADVICE           Synonym for V_$SHARED_POOL_ADVICE
V$STREAMS_POOL_ADVICE          Synonym for V_$STREAMS_POOL_ADVICE

V$MTTR_TARGET_ADVICE           Synonym for V_$MTTR_TARGET_ADVICE
V$PGA_TARGET_ADVICE_HISTOGRAM  Synonym for V_$PGA_TARGET_ADVICE_HISTOGRAM
V$PX_BUFFER_ADVICE             Synonym for V_$PX_BUFFER_ADVICE

 

 

-------------------------------------------------------------

 

 

v$memory_dynamic_components

 

 

=====================

 

11g内存自动管理时记录各个内存组件当前值
与v$sga_dynamic_components类似

SQL> desc v$memory_dynamic_components;
 Name                       Type
 -------------------------- ----------------------------
 COMPONENT                  VARCHAR2(64)          --组件名
 CURRENT_SIZE               NUMBER                --当前大小
 MIN_SIZE                   NUMBER                --最小值
 MAX_SIZE                   NUMBER                --最大值
 USER_SPECIFIED_SIZE        NUMBER                --用户分配值
 OPER_COUNT                 NUMBER                --操作次所
 LAST_OPER_TYPE             VARCHAR2(13)          --上次操作类型：增大、缩小、初始化、统计、关闭、缩小取消
 LAST_OPER_MODE             VARCHAR2(9)           --上次修改模式：自动（空）、手动
 LAST_OPER_TIME             DATE                  --上次修改时间
 GRANULE_SIZE               NUMBER                --粒度大小

 

SQL> select * from v$memory_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             184549376  184549376  184549376            54525952          0 STATIC                                 4194304
large pool                4194304    4194304    4194304                   0          0 STATIC                                 4194304
java pool                 4194304    4194304    4194304                   0          0 STATIC                                 4194304
streams pool                    0          0          0                   0          0 STATIC                                 4194304
SGA Target              314572800  314572800  314572800           314572800          1 GROW          MANUAL    09-NOV-11      4194304
DEFAULT buffer cache    113246208  113246208  113246208                   0          0 INITIALIZING                           4194304
KEEP buffer cache               0          0          0                   0          0 STATIC                                 4194304
RECYCLE buffer cache            0          0          0                   0          0 STATIC                                 4194304
DEFAULT 2K buffer ca            0          0          0                   0          0 STATIC                                 4194304
DEFAULT 4K buffer ca            0          0          0                   0          0 STATIC                                 4194304
DEFAULT 8K buffer ca            0          0          0                   0          0 STATIC                                 4194304
DEFAULT 16K buffer c            0          0          0                   0          0 STATIC                                 4194304
DEFAULT 32K buffer c            0          0          0                   0          0 STATIC                                 4194304
Shared IO Pool                  0          0          0                   0          0 STATIC                                 4194304
PGA Target              104857600  104857600  209715200            83886080          2 SHRINK        MANUAL    09-NOV-11      4194304
ASM Buffer Cache                0          0          0                   0          0 STATIC                                 4194304

16 rows selected.

 

-----------------------------------------------------------

 

V$MEMORY_RESIZE_OPS

 

====================

 

与v$sga_resize_ops类似
在使用11g自动内存管理时记录内存调整的情况

 

 

SQL> DESC V$MEMORY_RESIZE_OPS
 Name                 Type
 -------------------- ----------------------------
 COMPONENT            VARCHAR2(64)       --组件名
 OPER_TYPE            VARCHAR2(13)       --操作类型：增大、缩小、初始化、统计、关闭、缩小取消
 OPER_MODE            VARCHAR2(9)        --操作类型：自动（空）、手动
 PARAMETER            VARCHAR2(80)       --对应参数
 INITIAL_SIZE         NUMBER             --起始值
 TARGET_SIZE          NUMBER             --目标值
 FINAL_SIZE           NUMBER             --最终值
 STATUS               VARCHAR2(9)        --操作结果：非活动、等待、完成、取消、错误
 START_TIME           DATE               --开始时间
 END_TIME             DATE               --结束时间

 

 

SQL> l
  1* select * from V$MEMORY_RESIZE_OPS
SQL> /

COMPONENT            OPER_TYPE  OPER_MODE PARAMETER            INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS    START_TIM END_TIME
-------------------- ---------- --------- -------------------- ------------ ----------- ---------- --------- --------- ---------
shared pool          STATIC               shared_pool_size                0   184549376  184549376 COMPLETE  09-NOV-11 09-NOV-11
PGA Target           STATIC               pga_aggregate_target            0   209715200  209715200 COMPLETE  09-NOV-11 09-NOV-11
java pool            STATIC               java_pool_size                  0     4194304    4194304 COMPLETE  09-NOV-11 09-NOV-11
streams pool         STATIC               streams_pool_size               0           0          0 COMPLETE  09-NOV-11 09-NOV-11
large pool           STATIC               large_pool_size                 0     4194304    4194304 COMPLETE  09-NOV-11 09-NOV-11
RECYCLE buffer cache STATIC               db_recycle_cache_siz            0           0          0 COMPLETE  09-NOV-11 09-NOV-11
KEEP buffer cache    STATIC               db_keep_cache_size              0           0          0 COMPLETE  09-NOV-11 09-NOV-11
SGA Target           STATIC               sga_target                      0   314572800  314572800 COMPLETE  09-NOV-11 09-NOV-11
DEFAULT buffer cache INITIALIZI           db_cache_size           113246208   113246208  113246208 COMPLETE  09-NOV-11 09-NOV-11
ASM Buffer Cache     STATIC               db_cache_size                   0           0          0 COMPLETE  09-NOV-11 09-NOV-11
DEFAULT buffer cache STATIC               db_cache_size                   0   113246208  113246208 COMPLETE  09-NOV-11 09-NOV-11
DEFAULT 2K buffer ca STATIC               db_2k_cache_size                0           0          0 COMPLETE  09-NOV-11 09-NOV-11
DEFAULT 4K buffer ca STATIC               db_4k_cache_size                0           0          0 COMPLETE  09-NOV-11 09-NOV-11
DEFAULT 8K buffer ca STATIC               db_8k_cache_size                0           0          0 COMPLETE  09-NOV-11 09-NOV-11
DEFAULT 16K buffer c STATIC               db_16k_cache_size               0           0          0 COMPLETE  09-NOV-11 09-NOV-11
DEFAULT 32K buffer c STATIC               db_32k_cache_size               0           0          0 COMPLETE  09-NOV-11 09-NOV-11
PGA Target           SHRINK     MANUAL    pga_aggregate_target    209715200   104857600  104857600 COMPLETE  09-NOV-11 09-NOV-11
SGA Target           GROW       MANUAL    sga_target              314572800   314572800  314572800 COMPLETE  09-NOV-11 09-NOV-11
PGA Target           SHRINK     MANUAL    pga_aggregate_target    104857600    83886080  104857600 COMPLETE  09-NOV-11 09-NOV-11

19 rows selected.