On BULK COLLECT

Overview

The most important thing to remember when one learns about and start to take advantage of features such as BULK COLLECT is that there is no free lunch. There is almost always a trade-off to be made somewhere. The tradeoff with BULK COLLECT, like so many other performance-enhancing features, is "Run faster but consume more memory." Specifically, memory for collections is stored in the Program Global Area (PGA), not the System Global Area (SGA). SGA memory is shared by all sessions connected to Oracle Database, but PGA memory is allocated for each session.