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 값 변경 후 재기동 ```sql --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. 재기동 후 이하의 파라미터 값 변경 sql 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
*/

관련 내용

• ASMM
• HugePage