Performance problem when using temporary clob variables.

From: Anders Bengtsson <anders.kj.bengtsson_at_telia.com>
Date: Tue, 13 Apr 2004 10:58:10 +0200
Message-ID: <000001c42135$7223f940$9900a8c0@andersdell>

Hi,

I have created a little program for collecting data from approximately 15 tables in a database. I store the data in the filesystem using the utl_file package. It is little like xml using different keywords to get the position when a specific table start and so on. Then I have another routine that should go through the file and restore the data (normally to another schema). So the routine is for extracting data and than restore the data to another schema or another database.

Everything was fine until the users started to use xml-type columns in the database where data could be larger than 32k. So then I started to have problems because of the limitation in pl*sql when using string columns larger than 32k. I tried a lot of different solutions but in the end it was always something that didn't work out. Extracting the data and store it in a file was easy but when I tried to restore the data something always did go wrong.

One good thing with this approach was that the performance was good, even when the file was around 50 MB or so. This was of course before the large xml-type columns did appear.

After that I started to consider using temporary clob variables as a working area and than store the final data in a permanent clob column in the database. And I have managed to get the code working, but the performance is very, very bad.

I have read all the documents that I have found how to optimize using temporary clob-variables, and still I'm not happy about the performance.

So my main question is if I should continue working with the second approach, with clob - variables, in my mind I thought that this approach should be the faster one of the two, or if I should return to the first solution.

I don't expect that there is a simple solution to this but if someone could give me a hint of the best approach to this problem I would be grateful.

The database is an Oracle 9.2.0.4 on Windows 2000 Server.

Regards

Anders Bengtsson

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Tue Apr 13 2004 - 03:53:48 CDT