Moving UGA to Large Pool in shared server environment
Hi,
In our production environment we've recently had a couple of issues with ORA-04031 - Unable to allocate shared memory (large pool) incidents bringing the webapps that use this DB to a halt for a few minutes.
Doing some reading (Configuring and Using Memory), I have a suspicion that this is partly down to the use of shared server architecture and a large turnover of connections (connection pool is badly configured) causing shared pool fragmentation. The linked document recommends specifically that if using shared servers, the large pool should be used instead of the shared pool for UGA:
Oracle Database recommends using the large pool to allocate the shared server-related User Global Area (UGA), rather than using the shared pool. This is because Oracle Database uses the shared pool to allocate System Global Area (SGA) memory for other purposes, such as shared SQL and PL/SQL procedures. Using the large pool instead of the shared pool decreases fragmentation of the shared pool.