Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Using REPLACE on CLOB causes increase in CACHE_LOBS...

Using REPLACE on CLOB causes increase in CACHE_LOBS...

Using REPLACE on CLOB causes increase in CACHE_LOBS...

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

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)?


RE: Using REPLACE on CLOB causes increase in CACHE_LOBS...


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.



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close