Dans le coeur du fonctionnement de la PGA, de la 9i a la 12c et du parametre PGA_AGGREGATE_LIMIT (suite de la gestion de la memoire)

Suite à la demande de mon cher et valeureux ami et frère, Joël Mengolo a qui j’exprime toute ma gratitude, j’aimerais compléter cet article en parlant en profondeur de la gestion d’une zone de mémoire PGA et du nouveau paramètre introduit dans la version 12c, PGA_AGGREGATE_LIMIT.

Historique :

Il faut dire que le paramètre PGA_AGGREGATE_TARGET a été introduit avec la version Oracle Database 9i, remplaçant ainsi 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 allouent 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 puisque chaque processus utilisateur crée une zone PGA.

Dans le version Oracle Database 12c, une nouvelle fonctionnalité a été introduite avec l’introduction du nouveau paramètre PGA_AGGREGATE_LIMIT qui permet de contrôler la quantité maximale de la mémoire PGA, consommée par les processus utilisateurs.

Dans les versions précédentes, le seul control disponible est le paramètre masqué _pga_max_size, qui pose une limite de la mémoire utilisée par chaque processus de manière individuelle. La paramètre PGA_AGGREGATE_LIMIT fixe la limite AGREGEE de la PGA totale.

Lorsque la valeur de PGA_AGGREGATE_LIMIT est en dehors de ses limites, les sessions ou processus utilisant la mémoire PGA la plus élevée sont résiliées et l’erreur suivante est écrite dans le fichier des alertes :

PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT of 3072 MB
Immediate Kill Session#: 41, Serial#: 397
Immediate Kill Session: sess: 0xc13a5066  OS pid: 5233

Le paramètre PGA_AGGREGATE_LIMIT est dynamique et sa valeur peut être changée base ouverte. La base de données n’a donc pas besoin d’être redémarrée :

SQL> alter system set pga_aggregate_limit=60G scope=both;
System Altered.

Notez toute fois que vous ne pouvez pas réduire la valeur de PGA_AGRREGATE_LIMIT indéfiniment, selon la configuration de votre base de données. Si cette valeur est trop faible, vous recevez l’erreur suivante :

Le paramètre PGA

SQL> alter system set pga_aggregate_limit=100M scope=both;
alter system set pga_aggregate_limit=100M scope=both

*
ERROR at line 1:
ORA-02097: parameter cannot be modified specified value invalid
ORA-00093: pga_aggregate_limit must be between 1694M and 100000G

Pour les DBA (Database Administrator), il est important d’être informe du comportement de ce nouveau paramètre. Si vous travaillez dans une base de données Oracle 12c et qu’une session se termine brutalement, vous devriez regarder si ce n’est pas l’effet de ce paramètre.

Merci à vous et je reste ouvert à toute remarque constructive.

ORACLE DATABASE: GESTION DE LA MEMOIRE (couvre les versions 9i, 10g, 11g et 12c)

I-                     INTRODUCTION

La gestion de la mémoire a beaucoup évolué avec les différentes versions d’Oracle depuis la version 9i. Cet article est un aide-mémoire pour les DBA afin de cerner les subtilités introduites dès la version 9i et ceux, jusqu’à la version 12c.

Le tableau ci-dessous récapitule les différentes évolutions de la gestion de la mémoire :

Version	SGA	PGA
Oracle 9i	C’est la SGA dynamique. Le DBA doit dimensionner individuellement les différentes composantes de la SGA, mais ces composantes sont redimensionnables à chaud.	C’est la gestion automatique de la PGA. Le DBA alloue une quantité de mémoire totale nécessaire pour la PGA agrégée des processus serveurs et Oracle se charge de répartir cette mémoire automatiquement entre les différents les processus en fonction des besoins.
Oracle 10g	C’est la gestion automatique de la mémoire partagée. Le DBA alloue une quantité de mémoire totale nécessaire pour la SGA et Oracle répartit cette mémoire entre les processus de la SGA en fonction des besoins	Pareil que dans la 9i
Oracle 11g/12c	C’est la gestion automatique de la mémoire. Le DBA alloue une quantité de mémoire totale à l’instance et Oracle répartit cette mémoire automatiquement entre la SGA, ses différentes composantes et la PGA en fonction des besoins

Note : La gestion automatique de la mémoire ou la gestion automatique de la mémoire partagée requiert que le paramètre d’initialisation STATISTIC_LEVEL soit fixé à TYPICAL ou ALL.

II-                   GESTION AUTOMATIQUE DE LA MEMOIRE PARTAGEE

Pour activer la gestion automatique de la mémoire partagée (Automatic Shared memory Management, ASMM), il suffit de fixer une valeur non nulle au paramètre d’initialisation SGA_TARGET. Cette valeur spécifie la quantité de mémoire totale allouée à la SGA, et elle est toujours inférieure ou égale à la valeur de SGA_MAX_SIZE. Lorsque ASMM est activé, seuls les paramètres LOG_BUFFER, DB_nK_CACHE_SIZE, DB_KEEP_CACHE_SIZE et DB_RECYCLE_CACHE_SIZE doivent être dimensionnés manuellement. Tous les autres paramètres sont adaptés dynamiquement en fonction de la charge du système et sont fixés à zéro.

Dans ce mode de gestion, la SGA et la PGA sont gérés automatiquement mais c’est de la responsabilité du DBA de répartir les mémoires disponibles pour Oracle, entre ces deux structures (SGA_TARGET et PGA_AGGREGATE_TARGET).

La valeur zéro de SGA_TARGET désactive le réglage automatique.

III-                 GESTION AUTOMATIQUE DE LA MEMOIRE

La gestion automatique de la mémoire (Automatic Memory Management, AMM) a été introduite à la version Oracle 11g et contrairement à la version Oracle 10g, elle est répartie automatiquement entre la SGA et la PGA. Cette répartition est adaptée dynamiquement en fonction de la charge du système. Les paramètres d’initialisation MEMORY_TARGET et MEMORY_MAX_TARGET sont ajoutés pour assurer AMM. Pour activer la gestion automatique de la mémoire, fixer une valeur non nulle au paramètre MEMORY_TARGET ; MEMORY_MAX_TARGET désignant sa valeur maximale.

Si la gestion automatique de la mémoire est activée, tous les autres paramètres sont fixé zéro. Néanmoins, SGA_TARGET et/ou PGA_AGGREGATE_TARGET peuvent avoir des valeurs non nulles. Nous allons décrire tous les scénarios possibles :

·         Si MEMORY_TARGET porte une valeur non nulle,

o   Si SGA_TARGET et PGA_AGGREGATE_TARGET portent des valeurs non nulles, ces valeurs sont considérées comme étant les valeurs minimales de la taille de la SGA et de la PGA respectivement. Alors MEMORY_TARGET peut prendre une valeur comprise entre

SGA_TARGET+PGA_AGGREGATE_TARGET et MEMORY_MAX_TARGET.

o   Si SGA_TARGET porte une valeur non nulle et PGA_AGGREGATE_TARGET n’est pas défini ou est nulle, alors les deux paramètres sont en mode auto-réglables et PGA_AGGREGATE_TARGET peut prendre la valeur de MEMORY_TARGET-SGA_TARGET.

o   Si SGA_TARGET n’est pas défini ou porte une valeur nulle et PGA_AGGREGATE_TARGET porte une valeur non nulle, alors les deux paramètres sont en mode auto-réglables et SGA_TARGET peut prendre la valeur de MEMORY_TARGET-PGA_AGGREGATE_TARGET.

o   Si SGA_TARGET et PGA_AGGREGATE_TARGET ne sont pas définis ou portent des valeurs nulles, alors nous sommes en mode auto-réglage sans valeur minimale ou par défaut. De manière générale, la règle attribue 60% à la SGA et 40% à la PGA au démarrage de l’instance.

·         Si MEMORY_TARGET n’est pas défini ou porte une valeur nulle,

o   Si SGA_TARGET porte une valeur non nulle, alors les sous-composants de la SGA sont auto-réglables. La PGA est également auto-réglable, qu’elle porte une valeur ou pas, mais la globalité SGA (SGA_TARGET) et PGA(PGA_AGGREGATE_TARGET) n’est pas auto-réglable ; c’est-à-dire que leurs valeurs ne varient pas automatiquement.

o   Si aucun des deux paramètres SGA_TARGET et PGA_AGGREGATE_TARGET ne sont définis ou portent des valeurs nulles, la PGA sera auto-réglable mais la SGA ne le sera pas. Les sous-composants de la SGA doivent donc être fixés manuellement.

o   Si MEMORY_MAX_SIZE est non nulle et MEMORY_TARGET est nulle, le comportement est le même que dans la version Oracle 10g.

Calcul de Memory Target :

(Go de mémoire x 0,40) / Nombre de bases de données éventuelles = Go pour MEMORY_TARGET par base de données

Dans le fichier de paramètre, si vous omettez de fournir une valeur au paramètre MEMORY_MAX_TARGET et que vous donnez une valeur au paramètre MEMORY_TARGET, la base de données fixe automatiquement le paramètre MEMORY_MAX_TARGET à la valeur de MEMORY_TARGET. Si on a plutôt l’inverse, MEMORY_TARGET prendra la valeur par défaut zéro. Après le démarrage de l’instance, vous pourrez dynamiquement changer cette valeur, à condition qu’elle n’excède pas MEMORY_MAX_TARGET.