v$memory_resize_ops 記錄記憶體變更的檢視

v$memory_resize_ops 檢視記錄了近800次修改記憶體大小的操作 包括自動和手動修改的，但是不包括 程式內部修改的
SQL> desc v$memory_resize_ops
 名稱                                      
 -----------------------------------------
 COMPONENT           物件的名稱 比如 pga target，shared 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>