Why does using REPLACE function on CLOB cause increase in CACHE_LOBS...?

Question

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 "clobRTFText := REPLACE...etc", but it actually was worse (i.e. CACHE_LOBS increased even 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

Answer 1

Why do this procedurally? It seems a declarative approach fulfills the requirements.

UPDATE clob_table SET clob_column = REPLACE(clob_column, '<CR>', '\par ');

You can supply whatever WHERE clause that suits you.