“PGA memory operation”, “Acknowledge over PGA limit” et ORA-04036 après migration vers 12c

La situation :
Lors de migrations récentes, des problèmes de performances sont apparus, avec de nombreuses sessions en attente sur l’événement “PGA memory operation“, et l’erreur ORA-04036 était parfois observée dans l’alert_log des instances migrées.
Les Migrations consistaient à passer des versions 10g ou 11g vers 12.1, ou de la version 12.1 vers 12.2
Ces problèmes n’étaient pas observés lors de la phase de validation des bases 12c, mais l’étaient une fois que la migration était effectuée et qu’une forte activité en base survenait.

 

La PGA en 12c :
La zone de mémoire PGA est composée de petites espaces de mémoire, chacun dédié à une session, et est utilisée principalement pour les opérations de tris et la génération de plans d’exécution.
Avant la version 12.1, il était possible de “proposer” une taille maximale pour cette zone (paramètre pga_aggregate_target) mais pas de la limiter explicitement (indirectement via l’utilisation du paramètre caché “_pga_max_size“).
Cette absence de limitation pouvait occasionner des problèmes de surconsommation de mémoire, avec des impacts dramatiques sur les performances de traitements en base.

Le paramètre PGA_AGGREGATE_LIMIT existe depuis la version 12.1, et permet de limiter explicitement la taille de la PGA.

Lorsqu’une session demande de la mémoire supplémentaire pour sa propre PGA, et que la taille totale de la PGA atteint sa limite :
La session est placée en attente, sur l’événement “Acknowledge over PGA limit“,
L’instance “fait le ménage” dans les PGA des autres sessions,
Elle peut éventuellement terminer des sessions, qui rencontreront le code erreur ORA-04036

L’événement d’attente générique “PGA memory operation” indique simplement que la session cherche à allouer ou désallouer de la mémoire pour sa propre PGA.

 

Voilà pour le comportement, mais d’où vient le problème ?

Le paramètre PGA_AGGREGATE_LIMIT a une valeur par défaut de “la plus grande valeur entre : 2Go, 2 x PGA_AGGREGATE_TARGET et 3Mo x PROCESSES”. La valeur calculée peut être bien inférieure à l’utilisation réelle de la PGA, et donc occasionner les symptômes décrits plus haut.

 

Corriger le problème:
A court terme (uniquement) : valoriser PGA_AGGREGATE_LIMIT à 0, pour purement et simplement supprimer la limitation de la taille de la PGA.
Mais il est bon de limiter la mémoire totale utilisable par l’instance. Il s’agira donc de trouver une valeur correcte de la limite de la PGA, en se basant sur la taille maximale de la PGA observée (grâce, entre autres, à la requête SELECT VALUE FROM V$PGASTATS WHERE NAME=’maximum PGA allocated’).

Continuez votre lecture sur le blog :

• Limiter la PGA totale en 12c (Benjamin VESAN) [Oracle]
• MySQL et les tables temporaires internes (Benjamin VESAN) [MySQL]
• Texte SQL tronqué dans les vues performance_schema en 5.6 et 5.7, il faut migrer ! (David Baffaleuf) [MySQL]
• Mythe: SQL Server associe un thread à chaque connexion (David Baffaleuf) [SQL Server]
• Mythe : ASYNC_IO_COMPLETION indique-t-il toujours une attente sur une IO asynchrone ? (David Baffaleuf) [SQL Server]