v$memory_resize_ops 记录内存变更的视图

v$memory_resize_ops 视图记录了近800次修改内存大小的操作 包括自动和手动修改的但是不包括 进程内部修改的
SQL> desc v$memory_resize_ops
 名称                                      
 -----------------------------------------
 COMPONENT           对象的名称 比如 pga targetshared pool                                
 OPER_TYPE           操作类型
                     static       
                     initializing  初始化即未修改过的
                     disabled      禁止的
                     grow          增大
                     shrink        减小
                     shrink_cancel
 OPER_MODE           操作方式manual 手动deferred 延迟  immediate 立即                                  
 PARAMETER           对象的参数比如 java pool 是 java_POOL_SIZE                              
 INITIAL_SIZE        初始值即开始操作时的大小                              
 TARGET_SIZE         需要调整到的目标大小                     
 FINAL_SIZE          最终的大小                              
 STATUS              操作的完成情况
                     INACTIVE
                     PENDING
                     COMPLETE 完成 
                     CANCELLED 取消
                     ERROR 错误              
 START_TIME          起始时间                             
 END_TIME            终止时间
下面通过实践来使用该视图。 首先查明当前组件的大小
SQL> SELECT  component, current_size, min_size, max_size
  2  FROM    v$memory_dynamic_components
  3  WHERE   current_size != 0;
COMPONENT                      CURRENT_SIZE   MIN_SIZE   MAX_SIZE
------------------------------ ------------ ---------- ----------
shared pool                       209715200  209715200  209715200
large pool                          4194304    4194304    4194304
java pool                          16777216   16777216   16777216
SGA Target                        536870912  536870912  536870912
DEFAULT buffer cache              297795584  297795584  297795584
PGA Target                        314572800  314572800  314572800   

--减小pga_aggregate_target.
SQL> alter system set pga_aggregate_target=100M scope=both;
系统已更改。
SQL> show parameter pool
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 0
olap_page_pool_size                  big integer 0
shared_pool_reserved_size            big integer 10M
shared_pool_size                     big integer 0
streams_pool_size                    big integer 0
--设置java_pool_size 的大小使其增大
SQL> alter system set java_pool_size=10M;
系统已更改。
SQL> select  COMPONENT,OPER_TYPE,OPER_MODE from v$memory_resize_ops;
COMPONENT                      OPER_TYPE     OPER_MODE
------------------------------ ------------- ---------
shared pool                    STATIC
large pool                     STATIC
java pool                      STATIC
streams pool                   STATIC
SGA Target                     STATIC
。。。。
PGA Target                     STATIC
ASM Buffer Cache               STATIC
DEFAULT buffer cache           INITIALIZING
PGA Target                     SHRINK        MANUAL --显示为缩小

已选择17行。

SQL> alter system set pga_aggregate_target=314572800;
系统已更改。
SQL> select  COMPONENT,OPER_TYPE,OPER_MODE from v$memory_resize_ops;
COMPONENT                      OPER_TYPE     OPER_MODE
------------------------------ ------------- ---------
shared pool                    STATIC
large pool                     STATIC
DEFAULT buffer cache           INITIALIZING
java pool                      STATIC
streams pool                   STATIC
SGA Target                     STATIC
DEFAULT buffer cache           STATIC
KEEP buffer cache              STATIC
。。。。
PGA Target                     STATIC
ASM Buffer Cache               STATIC
PGA Target                     SHRINK        MANUAL
PGA Target                     GROW          MANUAL  ---增大
已选择18行。
--实际上由此可以看出该视图是累计记录的。
SQL> alter system set streams_pool_size=16777216;
系统已更改。
SQL> select  COMPONENT,OPER_TYPE,OPER_MODE from v$memory_resize_ops;
COMPONENT                      OPER_TYPE     OPER_MODE
------------------------------ ------------- ---------
shared pool                    STATIC
large pool                     STATIC
DEFAULT buffer cache           INITIALIZING
ASM Buffer Cache               STATIC
PGA Target                     STATIC
PGA Target                     SHRINK        MANUAL
PGA Target                     GROW          MANUAL
DEFAULT buffer cache           SHRINK        MANUAL
streams pool                   GROW          MANUAL
已选择20行。
SQL> alter system set streams_pool_size=0;
系统已更改。
SQL> select  COMPONENT,OPER_TYPE,OPER_MODE from v$memory_resize_ops;
COMPONENT                      OPER_TYPE     OPER_MODE
------------------------------ ------------- ---------
shared pool                    STATIC
large pool                     STATIC
DEFAULT buffer cache           INITIALIZING
。。。。
PGA Target                     SHRINK        MANUAL
PGA Target                     GROW          MANUAL
DEFAULT buffer cache           SHRINK        MANUAL
streams pool                   GROW          MANUAL
已选择20行。

SQL>