Using REPLACE on CLOB causes increase in CACHE_LOBS...

[slicendice]

I have a requirement to use the built in REPLACE function on a CLOB variable as part of a larger PL/SQL process. I'm using Oracle 11g R2 and the function works OK, in that it does the replace as required, but as the procedure runs (there are around 2.5 millions records to process), it slows down badly - as in:

first 20,000 records: ~12 minutes
second 20,000 records: ~24 minutes
third 20,000 records: ~37 minutes
fourth 20,000 records: ~52 minutes
etc...

Checking V$TEMPORARY_LOBS during operation shows that the value for CACHE_LOBS increases with every row processed - my assumption is that this implies that memory associated with LOBS (CLOBS in this case) is not getting released once it has been used...?

Stepping through the code using PL/SQL debugger reveals that the value for CACHE_LOBS increases by 2 for every call to the REPLACE function. The function calls are along the lines of:

clobRTFText         CLOB;
...
dbms_lob.createtemporary(clobRTFText, TRUE, dbms_lob.call);
...
clobRTFText := REPLACE(clobRTFText, '<CR>', '\par ');  <== Causes CACHE_LOBS to increase by 2
...
dbms_lob.freetemporary(clobRTFText); <== Doesn't seem to cause CACHE_LOBS to decrease

It's as though the third line of code above is creating further CLOB variables on the fly. Is that because there is some kind of implicit type conversion occurring due to the REPLACE function expecting a VARCHAR2 parameter? I've tried using dbms_lob.copy instead of REPLACE, but it actually was worse (CACHE_LOBS went up quicker). Whatever the reason, the call to dbms_lob.freetemporary doesn't seem to make any difference to the value of CACHE_LOBS.

I've gone through the PL/SQL Semantics for LOBs section of the Oracle documentation - it mentions the way CLOB and VARCHAR2 variables can be used in built-in functions but I can't find anything about doing so potentially causing extra memory usage.

Does anyone have any ideas why this is happening or how I could do it (i.e. use REPLACE with a CLOB) without it failing to releasing memory (assuming that is indeed what is happening)?

Thanks

 

[Thargy]

Slice,

When confronted with this sort of issue, I often try using SQL.
PL/SQL is a great language, and I use it almost daily, but for sheer flat out speed, you just can't beat SQL.
In the table in question have you considered creating a target column which is the replace of the source clob.
Then drop the source and rename the target to the original.

This may sound daft, but nothing is faster that SQL, because when all is said and done, it's the native language of oracle.
PL is a bolt-on goodie, and suffers accordingly (e.g. context switching).

Have you got representative data and can you do timing tests?
If yes, then try my suggestion out and see how it goes.

Regards

T