Oracle : dimensionner et surveiller la mémoire SGA et PGA
Rappels sur la SGA, la PGA, ASMM et AMM, requêtes de suivi, advisors, règles de dimensionnement et HugePages.
Aide-mémoire pour comprendre, mesurer et dimensionner la mémoire d'une instance Oracle (SGA, PGA, AMM/ASMM, HugePages).
Rappels : SGA et PGA
- SGA (System Global Area) : zone partagée entre les sessions. Toute donnée lue ou modifiée y transite (blocs en cache, zones SQL partagées...).
- PGA (Program Global Area) : mémoire privée d'un processus serveur ou d'arrière-plan (tris, hachages, variables PL/SQL). Chaque processus a la sienne.
| Zone de la SGA | Paramètre |
|---|---|
| Cache de tampons | DB_CACHE_SIZE |
| Tampon de journalisation (redo log buffer) | LOG_BUFFER |
| Shared pool | SHARED_POOL_SIZE |
| Java pool | JAVA_POOL_SIZE |
| Large pool | LARGE_POOL_SIZE |
| Streams pool | STREAMS_POOL_SIZE |
Modes de gestion de la mémoire
- Manuel (8i/9i) : chaque zone est dimensionnée à la main ; dépasser la taille prévue provoque une erreur.
- ASMM (10g) : si
SGA_TARGET> 0, Oracle répartit la mémoire entre les zones de la SGA. Si une zone a une valeur non nulle, elle sert de minimum.LOG_BUFFERn'en fait pas partie. La PGA reste gérée séparément parPGA_AGGREGATE_TARGET. - AMM (11g et +) : si
MEMORY_TARGET> 0, Oracle répartit la mémoire entre la SGA et la PGA (MEMORY_MAX_TARGETfixe le plafond).
Ne pas confondre ASMM (Automatic Shared Memory Management), AMM (Automatic Memory Management), ASM (Automatic Storage Management) et ASSM (Automatic Segment Space Management). AMM n'est pas compatible avec les HugePages.
SGA_MAX_SIZE est une borne haute pour la SGA uniquement : elle ne limite pas la mémoire des processus (tris, hachages, variables PL/SQL). SGA_MAX_SIZE >= SGA_TARGET. Oracle ne fait pas de swap, c'est le système d'exploitation qui le fait.
Connaître la configuration et l'usage courants
show parameter sga
show parameter pga
show parameter target
show parameter memory_target
show parameter pga_aggregate_limit
show parameter use_large_pages
-- Répartition de la SGA
select * from v$sgainfo;
select name, round(bytes/(1024*1024*1024),0) as "Size(GB)", round(bytes/(1024*1024),0) as "Size(MB)", resizeable
from v$sgainfo order by 2 desc;
-- Taille totale de la SGA
select sum(value) from v$sga;
-- Mémoire libre dans la SGA
set lines 200 pages 300
select round(sum(bytes)/1024/1024,0) "Total Free memory(MB)" from v$sgastat where name like '%free memory%';
select pool, round(bytes/1024/1024,0) "Free memory(MB)" from v$sgastat where name like '%free memory%';
-- PGA
select name, value/1024/1024 inMB from v$pgastat where name in ('total PGA allocated','maximum PGA allocated');
select name, value from v$pgastat where name like 'total PGA a%';
-- Usage courant (PGA en cours d'utilisation, taille de la SGA)
select (value/1024/1024) CURRENT_PGA_IN_USE_IN_MB from v$pgastat where name = 'total PGA inuse';
select sum(bytes/1024/1024) CURRENT_SGA_SIZE_IN_MB from v$sgastat;
Autres vues utiles : v$sgastat, v$memory_dynamic_components, v$sga_dynamic_free_memory.
Advisors
-- PGA
select pga_target_for_estimate, pga_target_factor, estd_extra_bytes_rw from v$pga_target_advice;
-- SGA
select sga_size, sga_size_factor, estd_db_time from v$sga_target_advice;
-- MEMORY_TARGET
select memory_size, memory_size_factor, estd_db_time from v$memory_target_advice;
Les rapports ADDM et AWR (@?/rdbms/admin/addmrpt et @?/rdbms/admin/awrrpt) complètent l'analyse.
Qui consomme de la PGA ?
-- Taille courante et maximale par session
SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
nvl(lower(ssn.machine), ins.host_name) "SESSION",
to_char(prc.spid, '999999999') "PID/THREAD",
to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE",
to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE"
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
v$instance ins, v$statname stat1, v$statname stat2
WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max'
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr (+)
AND ssn.paddr = prc.addr (+);
-- Historique ASH : requêtes ayant alloué le plus de PGA sur 2 jours
select sample_time, session_id sid, session_serial# serial#, sql_id, sql_plan_hash_value hash,
sql_plan_operation, event, pga_allocated
from dba_hist_active_sess_history
where sql_id is not null and sample_time > sysdate - 2
order by pga_allocated desc;
-- Cumul par sql_id (sessions > 200 Mo)
select sql_id, sum(pga_allocated)/1024/1024 as "pga_allocated MB", count(*)
from dba_hist_active_sess_history
where sql_id is not null and sample_time > sysdate - 2 and pga_allocated > (1024*1024)*200
group by sql_id;
Historique de la PGA (AWR, 60 derniers jours)
WITH
pgastat_denorm_1 AS (
SELECT /*+ MATERIALIZE NO_MERGE */
snap_id,
dbid,
instance_number,
SUM(CASE name WHEN 'PGA memory freed back to OS' THEN value ELSE 0 END) pga_mem_freed_to_os,
SUM(CASE name WHEN 'aggregate PGA auto target' THEN value ELSE 0 END) aggr_pga_auto_target,
SUM(CASE name WHEN 'aggregate PGA target parameter' THEN value ELSE 0 END) aggr_pga_target_param,
SUM(CASE name WHEN 'bytes processed' THEN value ELSE 0 END) bytes_processed,
SUM(CASE name WHEN 'extra bytes read/written' THEN value ELSE 0 END) extra_bytes_rw,
SUM(CASE name WHEN 'global memory bound' THEN value ELSE 0 END) global_memory_bound,
SUM(CASE name WHEN 'maximum PGA allocated' THEN value ELSE 0 END) max_pga_allocated,
SUM(CASE name WHEN 'maximum PGA used for auto workareas' THEN value ELSE 0 END) max_pga_used_aut_wa,
SUM(CASE name WHEN 'maximum PGA used for manual workareas' THEN value ELSE 0 END) max_pga_used_man_wa,
SUM(CASE name WHEN 'total PGA allocated' THEN value ELSE 0 END) tot_pga_allocated,
SUM(CASE name WHEN 'total PGA inuse' THEN value ELSE 0 END) tot_pga_inuse,
SUM(CASE name WHEN 'total PGA used for auto workareas' THEN value ELSE 0 END) tot_pga_used_aut_wa,
SUM(CASE name WHEN 'total PGA used for manual workareas' THEN value ELSE 0 END) tot_pga_used_man_wa,
SUM(CASE name WHEN 'total freeable PGA memory' THEN value ELSE 0 END) tot_freeable_pga_mem
FROM dba_hist_pgastat
WHERE name IN
('PGA memory freed back to OS'
,'aggregate PGA auto target'
,'aggregate PGA target parameter'
,'bytes processed'
,'extra bytes read/written'
,'global memory bound'
,'maximum PGA allocated'
,'maximum PGA used for auto workareas'
,'maximum PGA used for manual workareas'
,'total PGA allocated'
,'total PGA inuse'
,'total PGA used for auto workareas'
,'total PGA used for manual workareas'
,'total freeable PGA memory'
)
AND snap_id in (select snap_id from dba_hist_snapshot where begin_interval_time > sysdate -60)
GROUP BY
snap_id,
dbid,
instance_number
),
pgastat_denorm_2 AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.dbid,
h.instance_number,
s.startup_time,
MIN(h.pga_mem_freed_to_os) pga_mem_freed_to_os,
MIN(h.bytes_processed) bytes_processed,
MIN(h.extra_bytes_rw) extra_bytes_rw
FROM pgastat_denorm_1 h,
dba_hist_snapshot s
WHERE s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
GROUP BY
h.dbid,
h.instance_number,
s.startup_time
),
pgastat_delta AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h1.snap_id,
h1.dbid,
h1.instance_number,
s1.begin_interval_time,
s1.end_interval_time,
ROUND((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 24 * 60 * 60) interval_secs,
(h1.pga_mem_freed_to_os - h0.pga_mem_freed_to_os) pga_mem_freed_to_os,
h1.aggr_pga_auto_target,
h1.aggr_pga_target_param,
(h1.bytes_processed - h0.bytes_processed) bytes_processed,
(h1.extra_bytes_rw - h0.extra_bytes_rw) extra_bytes_rw,
h1.global_memory_bound,
h1.max_pga_allocated,
h1.max_pga_used_aut_wa,
h1.max_pga_used_man_wa,
h1.tot_pga_allocated,
h1.tot_pga_inuse,
h1.tot_pga_used_aut_wa,
h1.tot_pga_used_man_wa,
h1.tot_freeable_pga_mem
FROM pgastat_denorm_1 h0,
pgastat_denorm_1 h1,
dba_hist_snapshot s0,
dba_hist_snapshot s1,
pgastat_denorm_2 min /* to see cumulative use (replace h0 with min on select list above) */
WHERE h1.snap_id = h0.snap_id + 1
AND h1.dbid = h0.dbid
AND h1.instance_number = h0.instance_number
AND s0.snap_id = h0.snap_id
AND s0.dbid = h0.dbid
AND s0.instance_number = h0.instance_number
AND s1.snap_id = h1.snap_id
AND s1.dbid = h1.dbid
AND s1.instance_number = h1.instance_number
AND s1.snap_id = s0.snap_id + 1
AND s1.startup_time = s0.startup_time
AND s1.begin_interval_time > (s0.begin_interval_time + (1 / (24 * 60))) /* filter out snaps apart < 1 min */
AND min.dbid = s1.dbid
AND min.instance_number = s1.instance_number
AND min.startup_time = s1.startup_time
)
SELECT snap_id,
TO_CHAR(MIN(begin_interval_time), 'YYYY-MM-DD HH24:MI') begin_time,
TO_CHAR(MIN(end_interval_time), 'YYYY-MM-DD HH24:MI') end_time,
ROUND(SUM(pga_mem_freed_to_os) / POWER(2, 30), 3) pga_mem_freed_to_os,
ROUND(SUM(aggr_pga_auto_target) / POWER(2, 30), 3) aggr_pga_auto_target,
ROUND(SUM(aggr_pga_target_param) / POWER(2, 30), 3) aggr_pga_target_param,
ROUND(SUM(bytes_processed) / POWER(2, 30), 3) bytes_processed,
ROUND(SUM(extra_bytes_rw) / POWER(2, 30), 3) extra_bytes_rw,
ROUND(SUM(global_memory_bound) / POWER(2, 30), 3) global_memory_bound,
ROUND(SUM(max_pga_allocated) / POWER(2, 30), 3) max_pga_allocated,
ROUND(SUM(max_pga_used_aut_wa) / POWER(2, 30), 3) max_pga_used_aut_wa,
ROUND(SUM(max_pga_used_man_wa) / POWER(2, 30), 3) max_pga_used_man_wa,
ROUND(SUM(tot_pga_allocated) / POWER(2, 30), 3) tot_pga_allocated,
ROUND(SUM(tot_pga_inuse) / POWER(2, 30), 3) tot_pga_inuse,
ROUND(SUM(tot_pga_used_aut_wa) / POWER(2, 30), 3) tot_pga_used_aut_wa,
ROUND(SUM(tot_pga_used_man_wa) / POWER(2, 30), 3) tot_pga_used_man_wa,
ROUND(SUM(tot_freeable_pga_mem) / POWER(2, 30), 3) tot_freeable_pga_mem,
0 dummy_15
FROM pgastat_delta
GROUP BY
snap_id
ORDER BY
snap_id;
Ratios de cache et contention
-- Cache hit ratio global
select 1 - (phy.value / (cur.value + con.value)) "Cache Hit Ratio"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads';
-- Hit ratio par buffer pool
select name, (1 - physical_reads / (db_block_gets + consistent_gets)) "HIT RATIO"
from v$buffer_pool_statistics
where db_block_gets + consistent_gets > 0;
-- Contention : une valeur élevée est mauvais signe
select name, value from v$sysstat where name = 'free buffer inspected';
select event, total_waits
from v$system_event
where event in ('free buffer waits', 'buffer busy waits');
Règles de dimensionnement (indicatives)
- Environnement non parallèle :
PGA_AGGREGATE_TARGET = (RAM totale * 80 %) * 50 %. - Environnement parallèle :
PGA_AGGREGATE_TARGET = 100 Mo * parallel_max_servers. PGA_AGGREGATE_LIMIT: par exemple, pour un serveur de 32 Go avec 13 Go de SGA, on pourrait aller jusqu'à 25 Go (120 % de la RAM = 38 Go, moins la SGA).
alter system set sga_max_size = 7G scope=both;
alter system set sga_target = 7G scope=both;
alter system set pga_aggregate_limit = 15G scope=both;
Activer AMM
Exemple pour un serveur de 8 Go de RAM (environ 7 Go pour Oracle). Sauvegarder d'abord le pfile, tester la configuration, puis recréer le spfile.
-- Copie du pfile
create pfile='<ORACLE_HOME>/dbs/pfile_backup.ora' from spfile;
# Fichier de travail à éditer
cp pfile_backup.ora pfile_new.ora
vim pfile_new.ora
-- Test de la configuration
startup pfile='<ORACLE_HOME>/dbs/pfile_new.ora'
-- Si tout est correct, enregistrer les paramètres
create spfile from pfile='<ORACLE_HOME>/dbs/pfile_new.ora';
CONN / AS SYSDBA
-- Paramètre statique : garder une marge de croissance sans redémarrage.
ALTER SYSTEM SET MEMORY_MAX_TARGET=7G SCOPE=SPFILE;
-- Paramètres dynamiques (Oracle gère toute la mémoire).
ALTER SYSTEM SET MEMORY_TARGET=6G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;
-- Valeurs précédentes (pour retour arrière) :
-- MEMORY_MAX_TARGET=0, MEMORY_TARGET=0, PGA_AGGREGATE_TARGET=195M, SGA_TARGET=3G
-- Redémarrer l'instance
SHUTDOWN IMMEDIATE;
STARTUP;
Référence : Automatic Memory Management (Oracle-Base).
HugePages (Linux)
À utiliser avec ASMM (pas avec AMM). Le script de calcul est fourni par Oracle (My Oracle Support, note 401749.1).
# Etat actuel
grep Huge /proc/meminfo
# /etc/sysctl.conf
vm.nr_hugepages=12300
sysctl -p
# /etc/security/limits.d/ : memlock en Ko, soit vm.nr_hugepages * 2048
oracle soft memlock 12107776
oracle hard memlock 12107776
Vérification :
show parameter use_large_pages
grep Huge /proc/meminfo
ipcs -m
ipcs -m | awk '{ print $5}' | awk '{a+=$0}END{print a}'
Remarques
- Les valeurs numériques (tailles, nombre de HugePages) sont des exemples à adapter à la machine.
FAST_START_MTTR_TARGETpeut se fixer ainsi :ALTER SYSTEM SET FAST_START_MTTR_TARGET = 300 SCOPE=BOTH;.