SQL & PL/SQL

I have a program that generates XML. We noticed that it was using an extrodinary amount of memory. Thinking that the problem involved clobs I started researching the code. As I was benchmarking items I noticed that the problem did not seem to lie in the clobs i was creating but the recursion I was performing. Below I have two procedures. They both create X number of clobs and append their results to a main clob. I ran both for 10,000 iterations and recorded the results. The recursive version took longer(pretty much as i suspected) and took almost 5x the memory(way more than I could ever expect). Is there any thing that can be done to decrease the memory overhead involved with recursion. The program I was benchmarking for traverses a tree structure and the trees can sometimes be very large. Recursion keeps the code very flexible and much easier to read(once you get past the recursion part, as it can be confusing at times). In one case the memory that the oracle service was using jumped from 147mb to 1.4gb. This is not even remotely acceptable and I really want to find a solution other than rewriting the core of the tree traversing logic.

Below is the sample code for my benchmarking and the results



CREATE OR REPLACE PROCEDURE make_clobs(v_num_clobs IN NUMBER)
IS
TYPE t_clob_ar IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
v_arr t_clob_ar;
v_main CLOB;
BEGIN
FOR i IN 1..v_num_clobs LOOP
v_arr(i) :='123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789-'||chr(13);
v_main := v_main||v_arr(i);
END LOOP;
dbms_output.put_line(dbms_lob.getlength(v_main));
END;



create or replace PROCEDURE recursion(p_start IN NUMBER, p_limit IN NUMBER, p_clob IN OUT CLOB)
is
begin
p_clob := p_clob||'123456789 123456789 123456789 123456789 123456789 123456789 123456789 123456789-'||chr(13);
IF(p_start <= p_limit)THEN
recursion(p_start + 1, p_limit, p_clob);
END IF;
end recursion;


RESULTS:

10,000 iterations
~80 character clobs(per iteration)

program start mem end mem total mem time total clob len
----------------------------------------------------------------------------------------------------
make_clobs(for loop) 103,740 112,684 8,944 445.781s 810000
recursion 103,532 147,284 43,752 1037.469s 810081