AMM (Automatic Memory Management)

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-memory.html#GUID-BEABE53D-8058-4834-B99B-5C80A8AD5B06

정보

SGA만 관리하는 ASMM (Automatic Shared Memory Management) 에서 PGA도 추가해 관리하는 자동 메모리 관리 기법


설정 순서

  1. MEMORY_TARGET, MEMORY_MAX_TARGET 값 변경 후 재기동 
     --MEMORY_TARGET = SGA_TARGET + MAX(PGA_AGGREGATE_TARGET, MAXIMUM PGA ALLOCATED)
 SHOW PARAMETER SGA_TARGET;
 SHOW PARAMETER PGA_AGGREGATE_TARGET;
 SELECT VALUE FROM V$PGASTAT WHERE NAME='maximum PGA allocated';

 --MEMORY_MAX_TARGET = MEMORY_TARGET
 ALTER SYSTEM SET MEMORY_TARGET = XXX;
 ALTER SYSTEM SET MEMORY_MAX_TARGET = XXX;
  2. 재기동 후 이하의 파라미터 값 변경 
     ALTER SYSTEM SET SGA_TARGET = 0;
 ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;


SGA_TARGET, PGA_TARGET ADVICE 값 확인

--SGA
SELECT
    SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_DB_TIME_FACTOR, ESTD_PHYSICAL_READS, ESTD_BUFFER_CACHE_SIZE, ESTD_SHARED_POOL_SIZE
FROM
    V$SGA_TARGET_ADVICE
ORDER BY SGA_SIZE_FACTOR;
/*
21152	0.5	588167	1.2318	624216895	14592	2944
26440	0.625	534832	1.1201	436627546	21888	2944
31728	0.75	516353	1.0814	371724404	25536	2944
37016	0.875	488086	1.0222	272325312	32832	2944
42304	1	477486	1	234986032	36608	2944
47592	1.125	460822	0.9651	176310020	43776	2944
52880	1.25	453612	0.95	150955027	47424	2944
58168	1.375	438380	0.9181	97401710	54720	2944
63456	1.5	430310	0.9012	69109392	58368	2944
*/

--PGA
SELECT
    PGA_TARGET_FOR_ESTIMATE, PGA_TARGET_FACTOR, ADVICE_STATUS, ESTD_TIME, ESTD_EXTRA_BYTES_RW, ESTD_PGA_CACHE_HIT_PERCENTAGE, ESTD_OVERALLOC_COUNT
FROM
    V$PGA_TARGET_ADVICE
ORDER BY PGA_TARGET_FACTOR;
/*
4750705664	0.5	ON	5169703838	22786917376	100	1217
7126057984	0.75	ON	5164287582	5712260096	100	0
9501411328	1	ON	5162475595	0	100	0
11401693184	1.2	ON	5162475595	0	100	0
13301975040	1.4	ON	5162475595	0	100	0
*/


관련 내용