A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).
All was going well when they hit the dreaded:
ORA-04030: out of process memory when trying to allocate 2032 bytes
They asked for my help.
The error occurred on this SELECT:
FROM header_tab trx
WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)
AND ((trx.column_ntab MULTISET
EXCEPT DISTINCT generated_ntab2) IS EMPTY)
The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality.
We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of work, revamping algorithms, ensuring correctness, you know the score.
Then my eyes snagged on the use of DISTINCT and IS EMPTY.
They simply needed to make sure that there was nothing in column_ntab that was not in generated_ntab2. Having two of something left was the same result as having one of something left - not empty.
So I suggested that they could drop the DISTINCT and see what happened.
What happened is that the ORA-04030 stopped occurring.
The lead developer also took a closer look at how generated_ntab2 was constructed and discovered that they were already ensuring that there were no duplicates.
The DISTINCT was definitely not needed and so the problem, at least for now, was resolved.
They are not 100% confident that the error still might not occur, especially as data volume increases. But at least they've bought themselves the time to fully analyze their algorithms and explore a 100% SQL solution.