5

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

user2724502
  • 181
  • 2
  • 3
  • 13
  • 1
    Have you tried to not cache the lobs? as in `dbms_lob.createtemporary(clobRTFText, FALSE, dbms_lob.call);` – Vincent Malgrat Aug 28 '13 at 09:21
  • 2
    I'm not seeing the same thing with a couple of variations of looping over what you've shown; the `lob_cache` count does increase by 2 for the `replace`, but only the first time for the scope. I never see the value going above 3 or 4. Presumably I'm doing something different, so can you expand on how this is being called and what else it's doing? Ideally a complete cut-down version that shows the behaviour. – Alex Poole Aug 28 '13 at 09:41
  • 1
    Do you have multiple locators ? From the documentation of [DBMS_LOB](http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_lob.htm#i1015757): _A copy of a temporary LOB is created if the user modifies the temporary LOB while another locator is also pointing to it. The locator on which a modification was performed now points to a new copy of the temporary LOB._ – user272735 Aug 28 '13 at 11:46

1 Answers1

2

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.

Michael O'Neill
  • 946
  • 7
  • 22