I had a batch program chew up 16 gb of PGA and run for extended time doing updates to ( my only ) table that has a text index on it ( customer last name ) ... used for "soundex" lookups.
It choked eventually and terminated with ORA 4030 ... have not seen one of those in a really long time.
Apparently Oracle has had a fix
identified and available from August 2012 that ( somehow ) they have not seen as
important enough to include in last several rounds of database PSU's. Wow ...
thanks so much Oracle support!
Looks like the bug / 4030 will only hit
things that do large numbers of changes ( inserts also ? dunno ) to text index
column ... but still ... not happy here.
Testing of the fix one-off looks ok so far and again ... found a
while back ( p14367567_112030_Linux-x86-64.zip ).
The following are the
bugs fixed by this patch:
14367567: EXCESSIVE PGA MEMORY GROWTH LEADING TO
HIGH SWAPPING AND PERFORMANCE IMPACTS