La PGA (Program Area) a souvent posé problème parce qu’il était très difficile de limiter sa taille totale. De ce fait, la saturation de la mémoire sur une machine hébergeant une instance Oracle est un phénomène qui peut survenir, avec des conséquences désastreuses (au mieux problème de performance, au pire problème de stabilité du système).
Petit rappel du fonctionnement de la PGA:
Sous Oracle (en mode de connexion dédié, ce qui correspond à la quasi totalité du parc Oracle déployé), un processus est créé pour chaque session. Ce processus dispose d’un accès à la zone mémoire partagée, la SGA, dans laquelle sont entre autres stockés les blocs de données (Buffer Cache) et les plans d’exécution récents (Library Cache). Il dispose aussi d’une zone mémoire dédiée, qui va héberger tout ce qui est relatif à son contexte d’exécution, sa zone de compilation des ordres SQL, et sa zone de tri (qui représente la plus grosse portion de la PGA).
Donc, pour faire court, une session = (au moins) un processus = une zone mémoire en plus de la SGA. La mémoire consommée par une instance dépend donc du nombre de sessions ouvertes, et principalement de la somme des zones de tri allouées aux sessions ouvertes.
Les mécanismes de limitation de cette PGA sont les suivants:
Limitation de la zone de tri par processus, via SORT_AREA_SIZE
Limitation de la PGA par processus, via _PGA_MAX_SIZE
Ces deux paramètres (dont l’un est un paramètre caché…) ont pour portée un processus. Limiter la PGA totale est donc possible (il s’agit ici de “PROCESSES x _PGA_SIZE”), mais très restrictif. C’est bien entendu le nombre de sessions actives effectuant des tris qu’il faut quantifier, par le nombre de sessions maximales sur l’instance.
Il est possible d’essayer de borner la PGA globale via le paramètre PGA_AGGREGATE_TARGET (qui va concrètement gérer de manière dynamique SORT_AREA_SIZE). Il s’agit ici d’une cible, pas d’une valeur maximale. Vous pouvez donc considérer que la PGA ne dépassera pas “souvent” cette valeur.
A noter que la gestion automatique de la mémoire proposée en 11g (via MEMORY_TARGET et MEMORY_MAX_TARGET) consiste entre autres à gérer dynamiquement PGA_AGGREGATE_TARGET, elle ne protège donc pas d’un débordement de la PGA.
Avec la version 12c, il est enfin possible de poser une réelle limite à cette zone, via le paramètre PGA_AGGREGATE_LIMIT. Si la PGA globale atteint ce seuil, la session consommant le plus de PGA est terminée.
Plus de détails sur cette nouvelle fonctionnalité dans cet article de Peter Häusler, et un complément d’informations concernant _PGA_MAX_SIZE dans cet article de Christian Bilien.
Continuez votre lecture sur le blog :
- “PGA memory operation”, “Acknowledge over PGA limit” et ORA-04036 après migration vers 12c (Benjamin VESAN) [Oracle]
- Oracle : la gestion des “hugepages” depuis la version 19c (Emmanuel RAMI) [Oracle]
- Question bête: Qu’est-ce qu’une grosse table ? (Benjamin VESAN) [SQL Server]
- Texte SQL tronqué dans les vues performance_schema en 5.6 et 5.7, il faut migrer ! (David Baffaleuf) [MySQL]
- Oracle : SQL Plan Management en version Standard Edition SE2 (Emmanuel RAMI) [Oracle]