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
Les paramètres utilisés par workarea_size_policy=auto (pour vous dissuader de les bidouiller…):
| parameter | value | description | |||
|---|---|---|---|---|---|
| _pga_max_size | 200M | Maximum size of the PGA memory for one process | |||
| _smm_advice_enabled | TRUE | if TRUE, enable v$pga_advice | |||
| _smm_advice_log_size | 0 | overwrites default size of the PGA advice workarea history log | |||
| _smm_auto_cost_enabled | TRUE | if TRUE, use the AUTO size policy cost functions | |||
| _smm_auto_max_io_size | 248 | Maximum IO size (in KB) used by sort/hash-join in auto mode | |||
| _smm_auto_min_io_size | 56 | Minimum IO size (in KB) used by sort/hash-join in auto mode | |||
| _smm_bound | 0 | overwrites memory manager automatically computed bound | |||
| _smm_control | 0 | provides controls on the memory manager | |||
| _smm_freeable_retain | 5120 | value in KB of the instance freeable PGA memory to retain | |||
| _smm_isort_cap | 102400 | maximum work area for insertion sort(v1) | |||
| _smm_max_size | 40960 | maximum work area size in auto mode (serial) | |||
| _smm_max_size_static | 40960 | static maximum work area size in auto mode (serial) | |||
| _smm_min_size | 204 | minimum work area size in auto mode | |||
| _smm_px_max_size | 102400 | maximum work area size in auto mode (global) | |||
| _smm_px_max_size_static | 102400 | static maximum work area size in auto mode (global) | |||
| _smm_retain_size | 0 | work area retain size in SGA for shared server sessions (0 for AUTO) | |||
| _smm_trace | 0 | Turn on/off tracing for SQL memory manager |