Performance problem when using temporary clob variables.
- From: "Anders Bengtsson" <anders.kj.bengtsson@xxxxxxxxx>
- To: <Oracle-L@xxxxxxxxxxxxx>
- Date: Tue, 13 Apr 2004 10:58:10 +0200
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
