Depuis la version 9i d'Oracle, la gestion de la mémoire peut se faire de manière automatique.
Le paramètre PGA_AGGREGATE_TARGET remplacant les paramètres SORT_AREA_SIZE et HASH_AREA_SIZE utilisé en 8i.
Il faut rappeler que la PGA est une zone mémoire privée où les processus allouent de la mémoire pour les opérations de tris, de hash ou de merge. De ce fait la zone de PGA est séparée de la SGA (System Global Area). Une troisième zone de mémoire, la UGA (User Global Area), maintient l'information sur l'état des sessions et des curseurs. En mode dédié, les processus alloue la zone UGA dans la PGA alors qu'en mode partagé la zone UGA est allouée dans la SGA (dans la LARGE POOL plus exactement).
Le paramètre PGA_AGGREGATE_TARGET, comme son nom l'indique est un objectif et non une limite absolue. Cela signifie que suivant la charge la somme de mémoire allouée à la PGA peut dépasser cette limite. La PGA est allouée à la demande.
Avec la gestion automatique de la SGA/PGA, on aurait pu espérer que quelques soit les opérations sur la base celles-ci se fassent le plus en mémoire possible. Cependant dans certains cas (Opération de maintenance, calculs des stats, rebuild d'index ou requête d'extration de type DSS), et bien qu'avec une charge limité, on observe que la mémoire n'est pas totallement utilisée pour ces opérations. Pourquoi ?
Voila de quoi nous éclairer...
Il existent des paramètres "masqués" d'Oracle qui influe sur la gestion de la mémoire et plus particulièrement sur la mémoire PGA :
_pga_max_size : Taille maximale de la mémoire PGA pour un process
_smm_max_size : Taille maximale de la zone de travail en mode auto (serial)
_smm_px_max_size : Taille maximale de la zone de travail en mode auto (global)
Alors à quoi correspondent et servent ces paramètres ?
Ces trois paramètres permettent de poser des restrictions sur la consommation mémoire des processus.
Le paramètre _pga_max_size sert de limite haute pour la somme des espaces mémoires de travail (work area).
La taille d'un espace mémoire unitaire est limité par le paramètre _smm_max_size à la fois pour les opérations sequentielle (normale) que celle executée en parallèle. Lorsqu'une execution en mode parallel est faite, une restriction supplémentaire est appliquée sur la taille total de tous les espaces de travail d'une même opération (join, sort, merge...). Cette limite est controlée par _smm_px_max_size.
Dans certaines limites ces paramètres sont recalculés lorsqu'on modifie le paramètre PGA_AGGREGATE_TARGET. Cependant il est possible d'overrider ces paramètres manuellement afin d'accélérer certains traitements sur la base.
Avec Oracle 9i par exemple le paramètre _pga_max_size est limité à 200 Mo, quelque soit la valeur donnée à PGA_AGGREGATE_TARGET (PAT)
En 10g _pga_max_size est limité à 480 Mo au maximum avec la gestion automatique.
En 11g _pga_max_size est limité à 2Go
Le calcul des statistiques sur de grande tables ainsi que la création d'index peut-être fortement améliorée par une modification de ces paramètres.
Pour qu'un tri soit entièrement réalisé en mémoire il faut que :
- Le volume de données par processus PX doit être inférieur à _smm_max_size
- Le volume de données par processus PX doit également être inférieur à _smm_px_max_size/DOP
Avec DOP comme Degree Of Parallelism
On pourrait envisager par exemple deux mode de fonctionnement pour une base de type infocentre :
- Un mode normal utilisé en journée avec les paramètres par défaut dans le spfile n°1
- Un mode batch utilisé la nuit avec des paramètres mémoire _pga_max_size, _smm_max_size et _smm_px_max_size étendu dans un spfile n°2 pour des opérations de chargement/recalcul d'index plus rapide.