進一步理解oracle11g之v$sga_resize_ops
結論
1,v$sga_resize_ops記錄已完成的近期850次SGA元件重定義大小的操作，注意是已經完成與之對應的是v$sga_current_resize_ops它僅記錄當前正在操作中的SGA元件重定義操作，注意是正在操作中的
2,由此引申出，ORACLE任何知識皆有正反性，對應即有對應的知識點，所以考慮問題要從自反和辯證方面分析，才會更進一步理解ORACLE
3,不要基於__db_cache_size(以及類似的SGA其它元件)顯式調整SGA對應元件的大小，它不會記錄到v$sga_resize_ops
4,可以隨意增減__db_cache_size(其它類似),ORACLE不會有任何報錯
5,如果僅是單方面調整了db_cache_size或其它元件大小（非隱含對應引數），不管成功或失敗，皆會記錄到v$sga_resize_ops
6,如果要增加一個SGA元件大小，先要減少其它SGA元件的大小，然後才能增加這個SGA元件的大小，所以這是2個連續的操作
7,並且最終要調整的SGA元件大小並非和要調整的大小一致，我想這取決於SGA元件的大小以及各個元件內部的儲存結構以入要調整元件的大小
測試
SQL> select * from v$version where rownum=1;BANNER
----------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> col oper_type for a15
SQL> col parameter for a20
SQL> col component for a30
SQL> set linesize 300
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> col oper_mode noprint
----未調整前的SGA元件重定義大小操作資訊，此檢視記錄已經近期完成的850次重定義操作記錄，如果是當前正在重定義操作，請到v$sga_current_resize_ops檢視，二檢視結構相同
SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;
COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
e
DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
13 rows selected.
SQL> show parameter sga_
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
sga_max_size big integer 548M
sga_target big integer 0
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 24M
large_pool_size big integer 0
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 24326963
shared_pool_size big integer 464M
streams_pool_size big integer 0
NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
__db_cache_size 50331648 Actual size of DEFAULT buffer pool for standard bl
ock size buffers
__shared_pool_size 486539264 Actual size in bytes of shared pool
SQL> select 50331648/1024/1024 as db_cache_size,486539264/1024/1024 as shared_pool_size from dual;
DB_CACHE_SIZE SHARED_POOL_SIZE
------------- ----------------
48 464
---我基於v$sga_resize_ops之前的測試，對於其列final_size，發現其與此__db_cache_size及__shared_pool_size,即和各個元件當前實際分配大小有關，所以我想看看直接手工調整共享池及DB CACHE大小，看不會
記錄到上述檢視中
SQL> alter system set "__shared_pool_size"=400m;
System altered.
SQL> alter system set "__db_cache_size"=112m;
System altered.
---可見雖然顯式調整了共享池以及DB CACHE的當前實際大小，sga仍未發生重定義大小操作
SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;
COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
e
DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
13 rows selected.
---可見db cache實際大小沒有調整，而共享池大小有調整
NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
__db_cache_size 50331648 Actual size of DEFAULT buffer pool for standard bl
ock size buffers
__shared_pool_size 419430400 Actual size in bytes of shared pool
SQL> alter system set "__shared_pool_size"=50m;
System altered.
---發現db cache可以隨意調整，即使調錯也不報錯
SQL> alter system set "__db_cache_size"=100g;
System altered.
SQL> show parameter db_cache
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 48M
SQL> show parameter shared_pool
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
shared_pool_reserved_size big integer 24326963
shared_pool_size big integer 464M
----可見如果把某個SGA元件調整過小，則會記錄SGA元件重定義操作
SQL> alter system set shared_pool_size=10m;
alter system set shared_pool_size=10m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04034: unable to shrink pool to specified size
--可見target_size為顯式要調整的值，final_size為基於SGA各元件當前實際情況最終要調整的值
SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;
COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
e
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool SHRINK shared_pool_size 486539264 12 268 ERROR 2015-12-15 06:57:38 2015-12-15 06:57:40 ---上述調整共享池操作
14 rows selected.
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
shared_pool_size big integer 268M
NAME_1 VALUE_1 DESC1
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
__shared_pool_size 281018368 Actual size in bytes of shared pool
SQL> select 281018368/1024/1024 mb from dual;
MB
----------
268
---增加DB CACHE的大小操作
SQL> alter system set db_cache_size=1g;
alter system set db_cache_size=1g
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;
COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
e
COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool SHRINK shared_pool_size 486539264 12 268 ERROR 2015-12-15 06:57:38 2015-12-15 06:57:40
DEFAULT buffer cache GROW db_cache_size 50331648 1024 244 ERROR 2015-12-15 07:05:05 2015-12-15 07:05:05 ---當前增加DB CACHE的操作
15 rows selected.
SQL> show parameter db_cache
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 244M
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
shared_pool_size big integer 268M
SQL> alter system set db_cache_size=100m;
System altered.
SQL> alter system set shared_pool_size=412m;
System altered.
SQL> show parameter db_cache
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 100M
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
shared_pool_size big integer 412M
SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;
COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
e
DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool SHRINK shared_pool_size 486539264 12 268 ERROR 2015-12-15 06:57:38 2015-12-15 06:57:40
DEFAULT buffer cache GROW db_cache_size 50331648 1024 244 ERROR 2015-12-15 07:05:05 2015-12-15 07:05:05
DEFAULT buffer cache SHRINK db_cache_size 255852544 100 100 COMPLETE 2015-12-15 07:12:29 2015-12-15 07:12:29 ---上述減少db cache
shared pool GROW shared_pool_size 281018368 412 412 COMPLETE 2015-12-15 07:13:03 2015-12-15 07:13:03 ---上述增加共享池
17 rows selected.
SQL> show parameter db_cache
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 100M
SQL> show parameter shared_pool
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
shared_pool_reserved_size big integer 24326963
shared_pool_size big integer 412M
SQL> alter system set shared_pool_size=300m;
System altered.
SQL> alter system set db_cache_size=242m;
alter system set db_cache_size=242m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
SQL> alter system set db_cache_size=212m;
System altered.
SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;
COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
e
streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool SHRINK shared_pool_size 486539264 12 268 ERROR 2015-12-15 06:57:38 2015-12-15 06:57:40
DEFAULT buffer cache GROW db_cache_size 50331648 1024 244 ERROR 2015-12-15 07:05:05 2015-12-15 07:05:05
DEFAULT buffer cache SHRINK db_cache_size 255852544 100 100 COMPLETE 2015-12-15 07:12:29 2015-12-15 07:12:29
shared pool GROW shared_pool_size 281018368 412 412 COMPLETE 2015-12-15 07:13:03 2015-12-15 07:13:03
shared pool SHRINK shared_pool_size 432013312 300 300 COMPLETE 2015-12-15 07:16:28 2015-12-15 07:16:28
DEFAULT buffer cache GROW db_cache_size 104857600 244 212 ERROR 2015-12-15 07:16:42 2015-12-15 07:16:42
19 rows selected.
SQL> alter system set db_cache_size=170m;
System altered.
SQL> select component,oper_type,oper_mode,parameter,initial_size,target_size/1024/1024 as target_size,final_size/1024/1024 as final_size,status,start_time,end_time from v$sga_resize_ops;
COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
RECYCLE buffer cache STATIC db_recycle_cache_siz 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
e
streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT buffer cache STATIC db_cache_size 0 48 48 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
------------------------------ --------------- -------------------- ------------ ----------- ---------- ------------------ ------------------- -------------------
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
large pool STATIC large_pool_size 0 0 0 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
java pool STATIC java_pool_size 0 24 24 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool STATIC shared_pool_size 0 464 464 COMPLETE 2015-12-15 04:04:29 2015-12-15 04:04:29
shared pool SHRINK shared_pool_size 486539264 12 268 ERROR 2015-12-15 06:57:38 2015-12-15 06:57:40
DEFAULT buffer cache GROW db_cache_size 50331648 1024 244 ERROR 2015-12-15 07:05:05 2015-12-15 07:05:05
DEFAULT buffer cache SHRINK db_cache_size 255852544 100 100 COMPLETE 2015-12-15 07:12:29 2015-12-15 07:12:29
shared pool GROW shared_pool_size 281018368 412 412 COMPLETE 2015-12-15 07:13:03 2015-12-15 07:13:03
shared pool SHRINK shared_pool_size 432013312 300 300 COMPLETE 2015-12-15 07:16:28 2015-12-15 07:16:28
DEFAULT buffer cache GROW db_cache_size 104857600 244 212 ERROR 2015-12-15 07:16:42 2015-12-15 07:16:42
DEFAULT buffer cache SHRINK db_cache_size 222298112 172 172 COMPLETE 2015-12-15 07:39:31 2015-12-15 07:39:31
20 rows selected.
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-2090631/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 對Node.js非同步的進一步理解Node.js非同步
- 一步一步理解命令模式模式
- 關於 一合相 的再進一步的理解。
- 一步一步理解Generator函式的原理函式
- 進一步理解！linux下bus,device,driver三者關係Linuxdev
- oracle11g v$sql_v$sqlarea_version_count測試OracleSQL
- oracle不同的事務transaction隔離級別isolation level進一步理解Oracle
- NLP（二十九）一步一步，理解Self-Attention
- 一步一步演進RESTful API版本 - frankelRESTAPI
- 一步一步分析vue之observeVue
- oracle11g官方v$statname的中文註解Oracle
- 進一步詳解Wsgi
- 一步一步分析vue之$mount(1)Vue
- Oracle11G安裝之後Oracle
- Oracle11g新特性之editionOracle
- 一步一步分析vue之_data屬性Vue
- 發力SQL on Hadoop：甲骨文進一步完善大資料管理解決方案SQLHadoop大資料
- 理解生成對抗網路，一步一步推理得到GANs（二）
- 理解生成對抗網路，一步一步推理得到GANs（一）
- oracle11g之v$libcache_locks處理library cache lock及library cache pinOracle
- 進階MAC大神，只差這一步之遙！一起來學吧！Mac
- 越南距中止加密貨幣採礦機進口僅一步之遙加密
- 基於引數shared_pool_reserved_size進一步理解共享池shared pool原理
- 理解V$OPEN_CURSOR, V$SESSION_CACHED_CURSORSession
- v$undostat的一點理解
- 一步一步學DataGuard(5)物理standby之建立示例
- 一步一步學DataGuard(14)邏輯standby之switchover
- 深入理解之V8引擎的垃圾回收機制
- 一步一步理解 Java 企業級應用的可擴充套件性Java套件
- 『cs231n』卷積神經網路的視覺化與進一步理解卷積神經網路視覺化
- 深入理解ES6之《改進的陣列功能》陣列
- JavaScript 進階之深入理解資料雙向繫結JavaScript
- 關於v$process與v$session中process的理解Session
- [zt] 理解V$OPEN_CURSOR, V$SESSION_CACHED_CURSORSession
- oracle11g v$檢視view與字典dictionary官方連結OracleView
- 一步一步學ROP之Android ARM 32位篇Android
- 一步一步學ROP之linux_x64篇Linux
- 一步一步學ROP之linux_x86篇Linux