Sundar -- Thanks for the question regarding "Does SELECT ... BULK COLLECT overload PGA? ", version 9.2.0.7

You Asked

Hi Tom,
  I read from your other article that when using FETCH .. BULK collect, without using LIMIT, there is a chance of blowing off the PGA. What about using SELECT <column_list> BULK COLLECT INTO <variable_list>. There is no limit clause for this, so does Oracle take care of fetching it in certain limits, or is there a chance of overloading the PGA in this case too?

Thanks,
Sundar

and we said...

unless you use LIMIT - then Oracle will fetch ALL rows from the defining query into an in memory array.

If you have insufficient memory to do that, you will get an ora-4030.


So, use select ... bulk collect into ... from ..... ONLY when you are sure - really really sure - that the result set will be of a reasonable size.

Any chance that it is not, use an explicit cursor and fetch with the LIMIT clause.

Reviews

March 23, 2007 - 2pm Central time zone Bookmark | Bottom | Top Reviewer: A reader  How to be really sure the resultset will fit into the PGA? If I know the number of rows, and from the query the size of the column, could you demonstrate with a simple example (say a bulk load from T, perhaps) how to check we won't hit Ora-4030? Thanks Followup   March 26, 2007 - 7am Central time zone: you need no example, it is just a simple memory measurement here. If you have N rows and width is M, it'll be at least N*M in size - only bigger because it is a data structure in PLSQL (they have their bits and bytes they'll add). Just be reasonable and use between 100 and 500 rows at a time. You do not want an entire result set in memory, you want a slice of it, work on it, get rid of it, get next slice of it. alternative method   April 5, 2007 - 1am Central time zone Bookmark | Bottom | Top Reviewer: Connor from Perth or bulk collect into a varray. at least that way, when you grow to above what you were expecting, you'll get an error, not exhaust of all your memory

---

Write a Review