We help IT Professionals succeed at work.

ORA-04030 out of process memory: related to repeatedly initializing record type?

murphji
murphji asked
on
Am developing a Pl/SQL program and the program is quick and successful when run for a small number (1000) of rows but fails after a long run (30 minutes) if scaled up to 10,000 rows. It fails with an out of memory ORA-04030 error.

What it does:
Program processes records in a loop, calling several functions along the way. Many of these functions populate a record type like the following example:

Declare
rMyRec MyRecType; -- a type we defined

cursor c is select * from blah;

For r in c loop
  MyFunction(rMyRec);
  -- do something with current record "r" and data in rMyRec
end loop;

MyFunction does this

  rMyRec := MyRecType(null, null, null, null, null, null);
then reads a record and puts data into rMyRec

Question is: does initializing this over and over without ever releasing it cause it to use up memory, so that running with larger number of records results in memory error? That is, does it use new memory every time I do:
     rMyRec := MyRecType(null, null, null, null, null, null);

If this is the problem should I be destroying the initialized record every time, or initialize it one time in the calling routine, or something else?

Thanks,

Terry
Comment
Watch Question

use BULK COLLECT with the LIMIT clause if you can. You can also try to use the DELETE method to empty the collection

rMyRec.DELETE;
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I've not done a lot with in-memory pl/sql tables but I would imagine that it will continue to allocate memory every time you initialize a new instance of the table.

I can also easily see it not freeing up old versions until the process ends and releases allocated memory.

It is just good programming to manaully clean up structures you no longer need and not rely on the system to do it for you.

Author

Commented:
regarding: "It is just good programming to manaully clean up structures you no longer need and not rely on the system to do it for you."

I agree, not good to rely on system to clean up memory. How do you free up the memory after initializing a record after it is no longer needed? Set the object to nothing? Is there a destructor?

Repeating pseudo-code, with question on how to release memory:

Declare
rMyRec MyRecType; -- a type we defined

cursor c is select * from blah;

For r in c loop
  MyFunction(rMyRec);
  -- do something with current record "r" and data in rMyRec
  -- QUESTION: MyFunction initialized rMyRec and I am now through with it.
  -- How do I release its memory?
  -- Or, if more efficient to use it again, how can MyFunction tell if the object was initialize (see below)
end loop;

MyFunction does this

QUESTION --
-- Can the function test if rMyRec is initialized and do this initialization _ONLY_ if necessary,
-- otherwise using the existing already-initialized record?
  rMyRec := MyRecType(null, null, null, null, null, null);
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>How do you free up the memory after initializing a record after it is no longer needed?

paquicuba already posted it:  rMyRec.DELETE;

The doc link:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#CJAFGFIG


All the other questions are basically different ways of asking the same thing so I'll try to address it here:

You can do a COUNT to see how may entries are in a collection.

It is likely more efficient to delete and reinitialize the type each time in the function.  If the function creates it, it should destroy it.

What I'm not understanding with the code you posted, is if myRec is being passed in to the function, how is it created in the function?

Does the function need an in-memory table in the first place?

Explore More ContentExplore courses, solutions, and other research materials related to this topic.