The PGA memory is a memory allocated for each session.
It typically contains a:
- sort area (to be able to sort),
Specifically, memory for collections is stored in the program global area (PGA), not the system global area (SGA).
Thus, if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, in addition to the memory allocated to the SGA.
How to know how much PGA memory I am using ?
CREATE OR REPLACE PROCEDURE show_pga_memory ( context_in IN VARCHAR2 DEFAULT NULL ) IS l_memory NUMBER; BEGIN SELECT st.VALUE INTO l_memory FROM SYS.v_$session se, SYS.v_$sesstat st, SYS.v_$statname nm WHERE se.audsid = USERENV ('SESSIONID') AND st.statistic# = nm.statistic# AND se.SID = st.SID AND nm.NAME = 'session pga memory'; DBMS_OUTPUT.put_line ( CASE WHEN context_in IS NULL THEN NULL ELSE context_in || ' - ' END || 'PGA memory used in session = ' || TO_CHAR (l_memory) ); END show_pga_memory;