asked on

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

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

ASKER CERTIFIED SOLUTION

paquicuba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

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.

murphji

ASKER

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);

slightwv (䄆 Netminder)

>>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?