Re: export to XML file - poor performance

stefan.dyakov_at_gmail.com wrote:
> Hi all,
> I'm trying to create PL/SQL procedure which will export data to xml
> file.
>
> the result structure should look like this
>
> order
> - action
> - action
> - action
> ....
> order
> order
>
> On first stage I export the data to 2 temporary tables, and on second
> I loop from the data and create the output file concatenating the
> values for each row.
>
> The procedure runs very slow. If i run it over 300 rowsit takes about 3
> minutes.
> 5 seconds for filling the temp tables and all the the rest for looping
> trought the results and concatenating the text.
>
> if i try to run on a real data (about 22 000 rows) it can't finish at
> all. the temporary tablespace just grows up to the disk free space and
> the system hangs.
>
>
> Any suggestions about improving the performance?
>
> ----------------------
>
> The xml document is CLOB and the add_node just returns the value
> enclosed in tags.
>
> FOR order_rec IN get_orders_from_temp_table
> LOOP
> v_row := '<order>' || add_node(order_rec.pon, 'pon') ||
> add_node(order_rec.ver, 'ver') || add_node(order_rec.verid,
> 'verid') || add_node(order_rec.div, 'div') ||
> add_node(order_rec.status, 'status') ||..... '</order>' || CHR(10);
> xmldoc := xmldoc || v_row;
> END LOOP;
>

What version of Oracle

If 9i or higher don't use a cursor loop: Use BULK COLLECT and tune the LIMIT clause

What is ADD_NODE?

Have you run DBMS_PROFILER? If not www.psoug.org, click on Morgan's Library and look up a demo.

Why do you think the code snip you posted relates to the TEMP tablespace filling up?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org