En PGA automatique (pga_aggregate_target) il y a de nombreux paramètres qui limitent la PGA allouée par un process. La plus forte limite est _pga_max_size qui fait que par défaut un process n’allouera pas plus de 200M au total, et pas plus de 100M pour une opération de tri.
C’est très bien en fonctionnement normal, le but étant d’éviter de se retrouver avec de nombreux process allouant trop de mémoire et dépassant la mémoire physique. Un serveur Oracle ne doit pas swapper vu que justement la majorité de la mémoire utilisée est allouée pour éviter des accès disque.
Mais lorsqu’on maîtrise la charge globale du système (opération de maintenance offline, batch offline, voire même OLTP avec un pool de connexion limité), on peut se permettre d’avoir des process utilisant quelques centaines de Mo. Il peut être tentant de jouer sur ces paramètres cachés mais il y a tellement de calculs par défaut qui changent de version en version qu’on risque d’amener beaucoup de problèmes.
La bonne solution, c’est de passer en gestion manuelle et de définir les sort_area_size et hash_area_size souhaités.Par exemple, pour allouer 800M aux tris:
En parallel query, c’est chaque parallel process qui va allouer la taille spécifiée, d’où l’importance de maîtriser aussi le degré de parallélisme.
Un exemple dans la demo