Управление памятью (AMM, ASMM)

AMM (Automatic Memory Management)

В 12с можно установить значение параметру инициализации:

MEMORY_TARGET

Тогда экземпляр при запуске затребует у ОС это количество памяти и автоматически выделит размеры различным пулам.

Это динамический параметр и его можно изменять без перезапуска экземпляра.

alter system set memory_target     = 13120M     scope=spfile sid='*';

Чтобы случайно не получилось установить memory_target слишком большим, можно для него установить верхний предел:

alter system set memory_max_target = 13120M     scope=spfile sid='*';

Это статический параметр и его изменения потребуют остановки экземпляра.


При использовании AMM экземпляр перераспределяет память между SGA и PGA автоматически по мере изменения нагрузки.
При создании БД с помощью DBCA по умолчанию БД будет использовать AMM.



Руками включить использование AMM можно так:

alter system set memory_target          = 13120M scope=spfile sid='*';
alter system set sga_target             = 0      scope=spfile sid='*';
alter system set pga_aggregate_target = 0      scope=spfile sid='*';


И дополнительно можно установить ограничение сверху:

alter system set memory_max_target = 13120M     scope=spfile sid='*';


Если MEMORY_TARGET не задан, то AMM не будет использоваться, даже если будет установлен MEMORY_MAX_TARGET.


Если параметр MEMORY_MAX_TARGET не задан то считается что он равен значению MEMORY_TARGET.



Нижние границы областей памяти:

Параметр SGA_TARGET необязателен при использовании AMM.

Если значение SGA_TARGET задано и значение MEMORY_TARGET также задано,
то величина SGA_TARGET становится минимально возможной величиной памяти, которая может быть выделена SGA автоматически.


Параметр PGA_AGGREGATE_TARGET также необязателен при использовании AMM.

Если значение PGA_AGGREGATE_TARGET задано и значение MEMORY_TARGET также задано,
то величина PGA_AGGREGATE_TARGET становится минимально возможной величиной памяти, которая может быть выделена PGA автоматически.


MEMORY_TARGET и SGA_TARGET - нижние границы областей памяти в режиме AMM

MEMORY_MAX_TARGET и SGA_MAX_SIZE - это верхние границы.


Для того чтобы PGA не уменьшалось за счет роста SGA необходимо чтобы верхняя граница области SGA равнялась нижней границе,
т.е. необходимо выполнение правила:

SGA_TARGET должно быть равно SGA_MAX_SIZE

Допускается установка нижних границ (минимально необходимых) общих буферных областей SGA:

alter system set db_cache_size     = 512M     scope=spfile sid='*';
alter system set shared_pool_size  = 512M     scope=spfile sid='*';
alter system set java_pool_size    = 128M     scope=spfile sid='*';
alter system set large_pool_size   =  64M     scope=spfile sid='*';
alter system set stream_pool_size  =  64M     scope=spfile sid='*'; 


ASMM  (Automatic Shared Memory Management)
С помощью параметра инициализации SGA_TARGET задается общий объем памяти SGA.
Экземпляр автоматически распределит эту память между различными частями SGA.
При использовании ASMM некоторые компоненты SGA нужно задавать вручную:

LOG_BUFFER
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_nK_CACHE_SIZE

Вся память, выделенная вручную этим компонентам, вычитается из величины SGA_TARGET,
а остаток распределяется между компонентами, размер которых управляется автоматически:

SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
DB_CACHE_SIZE
STREAM_POOL_SIZE


Механизмы AMM и ASMM используют статистики, поэтому параметр инициализации STATISTIC_LEVEL должен быть установлен в значение TYPICAL или ALL.


Распределение памяти SGA и PGA (С использованием технологии AMM)
Note: 1392549.1 и 443746.1

Для OLTP приложения:

60% SGA_TARGET
40% PGA_AGGREGATE_TARGET

Для DWH приложения:

40% SGA_TARGET
60% PGA_AGGREGATE_TARGET

Пример установки параметров памяти:

Текущие параметры такие:

select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'memory_max_target'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'memory_target'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'sga_max_size'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'sga_target'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'pga_aggregate_target'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'db_cache_size'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'shared_pool_size'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'java_pool_size'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'large_pool_size'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'streams_pool_size';



'ALTERSYSTEMSET'||NAME||'='||VALUE/1024/1024||'MSCOPE=SPFILESID=''*'''||';'                                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter system set memory_max_target = 0M scope=spfile sid='*';
alter system set memory_target = 0M scope=spfile sid='*';
alter system set sga_max_size = 7376M scope=spfile sid='*';
alter system set sga_target = 7376M scope=spfile sid='*';
alter system set pga_aggregate_target = 2458M scope=spfile sid='*';
alter system set db_cache_size = 0M scope=spfile sid='*';
alter system set shared_pool_size = 0M scope=spfile sid='*';
alter system set java_pool_size = 0M scope=spfile sid='*';
alter system set large_pool_size = 0M scope=spfile sid='*';
alter system set streams_pool_size = 0M scope=spfile sid='*';

10 rows selected.


Скрипт для расчета параметров памяти в зависимости от размера OS Memory на сервере.

Расчёт производится для OLTP приложения:
60% SGA_TARGET
40% PGA_AGGREGATE_TARGET

Для DWH приложения меняем местами значения sga_target (sga_max_size) и значение pga_aggregate_target


DECLARE
    os_memory            number;
    granule_size         number;
    memory_target        number;
    sga_target           number;
    pga_aggregate_target number;
    iname                varchar2(16);
BEGIN
 
    SELECT VALUE/1024/1024/1024 into os_memory
    FROM   v$osstat
    WHERE  stat_name = 'PHYSICAL_MEMORY_BYTES';
    os_memory := ceil(os_memory) * 1024;
      
    -- for os_memory  <  10GB set only memory_target  
    -- os_memory :=  10*1024;   --  10GB
    -- os_memory :=  12*1024;   --  12GB
    -- os_memory :=  16*1024;   --  16GB
    -- os_memory :=  18*1024;   --  18GB
    -- os_memory :=  20*1024;   --  20GB
    -- os_memory :=  24*1024;   --  24GB
    -- os_memory :=  32*1024;   --  32GB
    -- os_memory :=  48*1024;   --  48GB
    -- os_memory :=  64*1024;   --  64GB
    -- os_memory := 100*1024;   -- 100GB
    -- os_memory := 128*1024;   -- 128GB
    -- os_memory := 192*1024;   -- 192GB
    -- os_memory := 256*1024;   -- 256GB

    select granule_size/1024/1024 into granule_size
    from v$sga_dynamic_components
    where component='DEFAULT buffer cache';
  
    select instance_name into iname from v$instance;

    if os_memory > 16384 then
       memory_target := os_memory - 4096;
    else
       memory_target := os_memory * 0.8;
    end if;

    sga_target := (memory_target * 0.6);
    sga_target := ceil(sga_target/granule_size)*granule_size;

    memory_target := ceil(memory_target/granule_size)*granule_size;

    pga_aggregate_target := memory_target - sga_target;

    DBMS_OUTPUT.PUT_LINE( '--'||os_memory/1024||'GB' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__db_cache_size"         scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__java_pool_size"        scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__large_pool_size"       scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__pga_aggregate_target"  scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__sga_target"            scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__shared_io_pool_size"   scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__shared_pool_size"      scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__streams_pool_size"     scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system set memory_max_target    = ' ||memory_target||'M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system set memory_target        = ' ||memory_target||'M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system set sga_max_size         = ' ||sga_target||'M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system set sga_target           = ' ||sga_target||'M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system set pga_aggregate_target = ' ||pga_aggregate_target||'M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( '--alter system reset pga_aggregate_target scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( '--alter system set db_cache_size      = 512M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( '--alter system set shared_pool_size   = 512M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( '--alter system set java_pool_size     = 128M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( '--alter system set large_pool_size    =  64M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( '--alter system set streams_pool_size  =  64M scope=spfile sid=''*'';' );
END;
/



--Для 24GB OS Memory на сервере будет предложено установить следующие значения:

alter system reset "__db_cache_size"         scope=spfile sid='testdb';
alter system reset "__java_pool_size"        scope=spfile sid='testdb';
alter system reset "__large_pool_size"       scope=spfile sid='testdb';
alter system reset "__pga_aggregate_target"  scope=spfile sid='testdb';
alter system reset "__sga_target"            scope=spfile sid='testdb';
alter system reset "__shared_io_pool_size"   scope=spfile sid='testdb';
alter system reset "__shared_pool_size"      scope=spfile sid='testdb';
alter system reset "__streams_pool_size"     scope=spfile sid='testdb';
alter system set memory_max_target    = 20480M scope=spfile sid='*';
alter system set memory_target        = 20480M scope=spfile sid='*';
alter system set sga_max_size         = 12288M scope=spfile sid='*';
alter system set sga_target           = 12288M scope=spfile sid='*';
alter system set pga_aggregate_target = 8192M scope=spfile sid='*';
--alter system reset pga_aggregate_target scope=spfile sid='*';
--alter system set db_cache_size      = 512M scope=spfile sid='*';
--alter system set shared_pool_size   = 512M scope=spfile sid='*';
--alter system set java_pool_size     = 128M scope=spfile sid='*';
--alter system set large_pool_size    =  64M scope=spfile sid='*';
--alter system set streams_pool_size  =  64M scope=spfile sid='*';


--А для 256GB OS Memory на сервере будет предложено установить такие значения:

alter system reset "__db_cache_size"         scope=spfile sid='testdb';
alter system reset "__java_pool_size"        scope=spfile sid='testdb';
alter system reset "__large_pool_size"       scope=spfile sid='testdb';
alter system reset "__pga_aggregate_target"  scope=spfile sid='testdb';
alter system reset "__sga_target"            scope=spfile sid='testdb';
alter system reset "__shared_io_pool_size"   scope=spfile sid='testdb';
alter system reset "__shared_pool_size"      scope=spfile sid='testdb';
alter system reset "__streams_pool_size"     scope=spfile sid='testdb';
alter system set memory_max_target    = 258048M scope=spfile sid='*';
alter system set memory_target        = 258048M scope=spfile sid='*';
alter system set sga_max_size         = 154832M scope=spfile sid='*';
alter system set sga_target           = 154832M scope=spfile sid='*';
alter system set pga_aggregate_target = 103216M scope=spfile sid='*';
--alter system reset pga_aggregate_target scope=spfile sid='*';
--alter system set db_cache_size      = 512M scope=spfile sid='*';
--alter system set shared_pool_size   = 512M scope=spfile sid='*';
--alter system set java_pool_size     = 128M scope=spfile sid='*';
--alter system set large_pool_size    =  64M scope=spfile sid='*';
--alter system set streams_pool_size  =  64M scope=spfile sid='*';


Смотрим значения параметров:

select     name || '=' || decode(type, 2, '''') || value
    || decode(type, 2, '''') parameter
from    v$parameter
where    isdefault = 'FALSE'
and    value is not null
order    by name
/


Распределение памяти экземпляра в режиме AMM:

col component format a20
break on report
compute sum of current_size_mb on report
select component, round(current_size/1024/1024,1) as current_size_mb
from v$memory_dynamic_components
where component like '%Target%';


Потребление памяти на уровне сессии

select *
from (
select
    p.spid,
    p.pid,
    s.sid,
    s.serial#,
    substr(trim(s.machine),1,32) machine,
    s.username,
    s.osuser,
    round(p.pga_used_mem/1024/1024,2) pga_used_mb,
    round(p.pga_alloc_mem/1024/1024,2) pga_alloc_mb,
    round(p.pga_max_mem/1024/1024,2) pga_max_mb,
    row_number() over (order by round(p.pga_max_mem/1024/1024,2) desc) as rn
from
   v$process p,
   v$session s
where p.addr = s.paddr
) where rn < 21
order by pga_max_mb desc;


Чтобы понять, является ли текущий размер SGA оптимальным, изучите следующие представления:

select * from V$SGA_DYNAMIC_COMPONENTS
select * from V$SGA_CURRENT_RESIZE_OPS
select * from V$SGA_RESIZE_OPS
select * from V$SGA_DYNAMIC_FREE_MEMORY


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


Для точной настройки значений механизма AMM можно использовать:

select * from V$MEMORY_TARGET_ADVICE

SELECT * FROM v$memory_target_advice ORDER BY memory_size;


Для настройки значений параметров SGA и PGA:

select * from V$SGA_TARGET_ADVICE
select * from V$PGA_TARGET_ADVICE

Примечание для OS Solaris:

Настройка AMM или ASMM на ОС Solaris заставит ОС использовать Dynamic Intimate Shared Memory (DISM).
Это позволяет динамически изменять размер shared memory segments.
Динамические свойства SGA используют DISM при включенном AMM или ASMM.
Начиная с Oracle 9i и выше, если SGA_MAX_SIZE  >  SGA_TARGET (или суммы sga compenents), тогда используется DISM.
В 11g DISM также используется, если установлен MEMORY_TARGET или MEMORY_MAX_TARGET.
В противном случае используется ISM (если DISM не включен).
DISM (в отличие от ISM) требует резервирования swap для всех страниц, независимо от того, выделены они или нет.
Это означает, что вам нужно будет настроить swap размером не менее чем сумма размеров SGA для экземпляров, использующих DISM.
Иначе получим memory errors при старте экземпляра.

Используется ли DISM для экземпляра Oracle, можно узнать так:

$ ps -aef | grep dis

должен быть процесс:
ora_dism_$ORACLE_SID

Избежать использования DISM и большого размера swap можно используя manual memory management.