콘텐츠로 이동

ASMM (Automatic Shared Memory Management)

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-memory.html#GUID-0E0EBCD5-6134-492B-9232-3F76D92B1900

정보

SGA를 자동으로 관리하는 자동 메모리 관리 기법


설정 순서

  1. SGA_TARGET 값 변경 

    ALTER SYSTEM SET SGA_TARGET = XXX;

--SGA_TARGET 값 계산
SELECT
    (SELECT SUM(value) FROM V$SGA) - (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY) AS "SGA_TARGET"
FROM DUAL;

  2. SGA_MAX_SIZE 값 변경 (옵션) 

    ALTER SYSTEM SET SGA_MAX_SIZE = XXX;

  3. 이하의 파라미터 값 변경 

    ALTER SYSTEM SET SHARED_POOL_SIZE = 0;
ALTER SYSTEM SET LARGE_POOL_SIZE = 0;
ALTER SYSTEM SET JAVA_POOL_SIZE = 0;
ALTER SYSTEM SET DB_CACHE_SIZE = 0;
ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;

  4. MEMORY_TARGET 값 변경 

    ALTER SYSTEM SET MEMORY_TARGET = 0;
    >해당 값이 0이면 AMM이 비활성화됨


SGA_TARGET ADVICE 값 확인

SELECT * FROM V$SGA_TARGET_ADVICE ORDER BY SGA_SIZE;
/*
SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_DB_TIME_FACTOR, ESTD_PHYSICAL_READS, ESTD_BUFFER_CACHE_SIZE, ESTD_SHARED_POOL_SIZE, CON_ID
824 0.5 187367  2.0304  1302706 160 560 0
1236    0.75    92327   1.0005  112696  480 672 0
1648    1   92281   1   101528  560 992 0
2060    1.25    92272   0.9999  99294   800 1120    0
2472    1.5 92272   0.9999  99203   1200    1120    0
2884    1.75    92272   0.9999  99203   1600    1120    0
3296    2   92272   0.9999  99203   2000    1120    0
*/


관련 내용