PGA_AGGREGATE_TARGET is not a hard limit. Lather, rinse, and repeat \ud83d\ude42<\/p>\n
I like to think of it as a tuning suggestion to Oracle as to how much it can allocate for things such as sorts and hash tables. Below is a test case that shows this.<\/p>\n
\r\nSQL> show parameter pga\r\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\npga_aggregate_target big integer 788M\r\nSQL> declare\r\n 2 type t_array is table of varchar2(4000);\r\n 3 l_array t_array := t_array();\r\n 4 l_mem number;\r\n 5 l_spid number;\r\n 6 begin\r\n 7 for i in 1..100000 loop\r\n 8 l_array.extend(1);\r\n 9 l_array(i) := rpad('.',3900,'.');\r\n10 end loop;\r\n11 dbms_lock.sleep(5);\r\n12 select pga_alloc_mem into l_mem from v$process where addr = (select paddr from v$session where sid = sys_context('userenv','sid'));\r\n13 dbms_output.put_line((trunc(l_mem \/ 1024 \/ 1024)) || 'MB used before clearing the array.');\r\n14 l_array.delete;\r\n15 dbms_session.free_unused_user_memory();\r\n16 dbms_lock.sleep(5);\r\n17 select pga_alloc_mem into l_mem from v$process where addr = (select paddr from v$session where sid = sys_context('userenv','sid'));\r\n18 dbms_output.put_line((trunc(l_mem \/ 1024 \/ 1024)) || 'MB used after clearing the array.');\r\n19 end;\r\n20 \/\r\n1393MB used before clearing the array.\r\n699MB used after clearing the array.\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> select trunc(pga_alloc_mem \/ 1024 \/ 1024) from v$process where addr = (select paddr from v$session where sid = sys_context('userenv','sid'));\r\n\r\nTRUNC(PGA_ALLOC_MEM\/1024\/1024)\r\n------------------------------\r\n 4\r\n\r\nSQL>\r\n<\/pre>\nNotice we allocated almost 1.4GB of memory, which almost doubled what has been configured as our target. Nothing prevents a developer from loading up a huge array and bringing your database server to its knees…other than testing, of course \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"
PGA_AGGREGATE_TARGET is not a hard limit. Lather, rinse, and repeat \ud83d\ude42 I like to think of it as a tuning suggestion to Oracle as to how much it can allocate for things such as sorts and hash tables. Below is…<\/p>\n