Depuis quelques version d’Oracle, la gestion de la mémoire a été simplifiée, comprenez que le nombre de paramètres minimal liés à sa gestion a été considérablement réduit. En clair, il suffirait de positionner le volume global de mémoire à allouer à l’instance par le truchement du paramètre memory_target et par le biais de réallocations dynamiques oracle serait capable de se débrouiller. On constate bien vite que c’est faux notamment pour le tuning sga.
Comme d’habitue lors de ce genre de cas, si on laisse l’initiative à Oracle, au bout d’un moment la mémoire finit par fragmenter et malgré une taille globale suffisante on rencontre de vilaines erreurs ORA-4031. Et plus généralement ce mode d’allocation (via memory_target donc) n’est pas compatible avec l’utilisation de huge pages. Il est donc recommandé de tuner les différentes sous-sections de mémoire, la pga au travers de pga_aggregate_target et la sga au travers de sga_target. Ces deux paramètres seuls ne suffisent cependant encore pas et il faut positionner des valeurs pour les divers pools et caches de la SGA qui seront considérées comme des minimums.
Pour commencer il est utile de positionner le cache, la sga et la large_pool et éventuellement d’autres zones sachant qu’idéalement la somme des minimas ne devrait pas dépasser 70 à 80% du total de la sga_target. Le paramétrage n’a donc pas la simplicité décrite par Oracle et je laisse ici quelques pistes, quelques outils, pour arriver à un paramétrage idéal.
Dans un premier temps allouer à votre instance entre 1/8ème et 1/6ème de la volumétrie de données pour une base relationnelle à la SGA ( la pga ne sera pas taitée ici ). Ensuite, il faut effectuer un métrologie de la sga au cours de son utilisation est éventuellement repositionner les paramètres en fonctions des recommandations qui peuvent être recommandées.
La requête suivante va sortir les recommandations
select Component
, max(MAX_SIZE*0.7 /1024/1024) PCT70
from gv$sga_dynamic_components
where MAX_SIZE > 0
group by component
order by component;
On va obtenir ce type de résultat
COMPONENT PCT70 ---------------------------------------------------------------- --------------- DEFAULT buffer cache 2,128.00 KEEP buffer cache 560.00 Shared IO Pool 134.40 java pool 44.80 large pool 1,635.20 shared pool 1,803.20 streams pool 22.40
C’est ben mignon d’avoir des reocmmandation à 70 du max utilisé, mais pour une activité un peu variée ou une nécessité de réallouer fréquemment les zones on peut avoir la somme des sous parties supérieure au total, et donc il convient de vérifier la valeur intéressante pour sga_target avec le script suivant :
select max_sga_mb
, total_reco
, total_reco/0.8 min_total_sga
, total_reco/0.7 avg_total_sga
, total_reco/0.6 max_total_sga
from ( select max_sga_mb
, sum(reco) total_reco
from ( select min(value)/1024/1024 max_sga_mb
from gv$parameter
where name='sga_max_size'
group by name )
, ( select component
, max(MAX_SIZE*0.7 /1024/1024) reco
from gv$sga_dynamic_components
where MAX_SIZE > 0
group by component)
group by max_sga_mb )
Le résultat sera le suivant:
| MAX_SGA_MB | TOTAL_RECO | MIN_TOTAL_SGA | AVG_TOTAL_SGA | MAX_TOTAL_SGA |
|---|---|---|---|---|
| 5120 | 6328 | 7910 | 9040 | 10546.6667 |
On s’aperçoit ici que le total recommandé comme minimum est trop important pour garder une marge raisonnable.
Le tuning consistera, en fonction de la mémoire restant sur l’OS, a agrandir la SGA target et les zones minimales en fonctions des recommandations jusqu’à ce que les recommandations soient en adéquation avec le paramétrage.