AMM Tuning
==============
- 11g에서 AMM관련 된 새로운 view가 소개 됨


?V$MEMORY_CURRENT_RESIZE_OPS
?V$MEMORY_DYNAMIC_COMPONENTS
?V$MEMORY_RESIZE_OPS
?V$MEMORY_TARGET_ADVICE


- 현재의 각 구성 요서별 할당 된 메모리 현황은 V$MEMORY_DYNAMIC_COMPONENTS에서 확인



COLUMN component FORMAT A30

SELECT component, current_size, min_size, max_size
FROM v$memory_dynamic_components
WHERE current_size != 0;


COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE
------------------------------ ------------ ---------- ----------
shared pool 171966464 163577856 171966464
large pool 4194304 4194304 4194304
java pool 4194304 4194304 4194304
streams pool 8388608 8388608 8388608
SGA Target 243269632 239075328 247463936
DEFAULT buffer cache 41943040 37748736 54525952
PGA Target 167772160 163577856 171966464


6 rows selected.


- V$MEMORY_CURRENT_RESIZE_OPS , V$MEMORY_RESIZE_OPS 는 현재 및 메모리 변경 이력에 대한 정보 확인

SQL> select * from V$MEMORY_RESIZE_OPS;

COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIM END_TIME
------------------------------ ------------- --------- ------------------------------ ------------ ----------- ---------- --------- --------- ---------
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 01-NOV-12 01-NOV-12
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 01-NOV-12 01-NOV-12
RECYCLE buffer cache STATIC db_recycle_cache_size 0 0 0 COMPLETE 01-NOV-12 01-NOV-12
shared pool STATIC shared_pool_size 0 310378496 310378496 COMPLETE 01-NOV-12 01-NOV-12
large pool STATIC large_pool_size 0 4194304 4194304 COMPLETE 01-NOV-12 01-NOV-12
java pool STATIC java_pool_size 0 4194304 4194304 COMPLETE 01-NOV-12 01-NOV-12
streams pool STATIC streams_pool_size 0 12582912 12582912 COMPLETE 01-NOV-12 01-NOV-12
SGA Target STATIC sga_target 0 524288000 524288000 COMPLETE 01-NOV-12 01-NOV-12
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 01-NOV-12 01-NOV-12
DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 01-NOV-12 01-NOV-12
PGA Target STATIC pga_aggregate_target 0 268435456 268435456 COMPLETE 01-NOV-12 01-NOV-12

COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIM END_TIME
------------------------------ ------------- --------- ------------------------------ ------------ ----------- ---------- --------- --------- ---------
DEFAULT buffer cache INITIALIZING db_cache_size 184549376 184549376 184549376 COMPLETE 01-NOV-12 01-NOV-12
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 01-NOV-12 01-NOV-12
DEFAULT buffer cache STATIC db_cache_size 0 184549376 184549376 COMPLETE 01-NOV-12 01-NOV-12
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 01-NOV-12 01-NOV-12
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 01-NOV-12 01-NOV-12
shared pool SHRINK DEFERRED shared_pool_size 310378496 297795584 297795584 COMPLETE 02-NOV-12 02-NOV-12
DEFAULT buffer cache GROW DEFERRED db_cache_size 184549376 197132288 197132288 COMPLETE 02-NOV-12 02-NOV-12
shared pool SHRINK DEFERRED shared_pool_size 297795584 285212672 285212672 COMPLETE 05-NOV-12 05-NOV-12
DEFAULT buffer cache GROW DEFERRED db_cache_size 197132288 209715200 209715200 COMPLETE 05-NOV-12 05-NOV-12
DEFAULT buffer cache SHRINK DEFERRED db_cache_size 209715200 197132288 197132288 COMPLETE 06-NOV-12 06-NOV-12
shared pool GROW DEFERRED shared_pool_size 285212672 297795584 297795584 COMPLETE 06-NOV-12 06-NOV-12

22 rows selected.

- V$MEMORY_TARGET_ADVICE 는 memory_target 파라미터를 조정하는데 필요한 정보를 제공
- memory_target 파라미터의 사이즈가 적절한지 확인 하기 위해서 해당 뷰를 사용


SQL> select * from v$memory_target_advice order by memory_size_factor;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ ------------------- ----------
503 .75 3068 1.0038 2
204 1 3056 1 2
105 1.25 3056 1 2
206 1.5 3056 1 2
107 1.75 3056 1 2
608 2 3056 1 2