Skip to Main Content
  • Questions
  • When to use or not to use the cache in the dbms_lob.createtemporary function


Question and Answer

Connor McDonald

Thanks for the question, Dieter.

Asked: May 25, 2022 - 4:14 am UTC

Last updated: May 31, 2022 - 1:55 am UTC

Version: 19

Viewed 1000+ times

You Asked

I‘m never sure on how to use the cache parameter to true when creating a new temporary lob using the dbms_lob.createtemporary function?
There seems to be trade-off between the improved speed when caching and the possible impact of filling up the buffer cache with lobs.
I currently cache the lob, when the performance seems critical and else not, but I have been wondering for a long time, if there are better rules to observe?

and Connor said...

For me, I'll typically apply two criteria on using the cache parameter.

1) Am I going "to and fro" on this lob. If in the process of constructing this lob, I'll be making multiple "trips" to it, then caching is going to make sense. If its "one and done", then I don't really get benefit so why pollute my buffer cache?

2) How big is my lob going to be. Caching megabytes is fine...gigabytes no :-)


  (1 rating)


Thank you

Dieter, May 30, 2022 - 10:11 am UTC

ad 1) This makes sense and is pretty much what I'm trying to do, but it is sometimes quite difficult to guess when temporary lobs are handed down to many levers of procedural code. I typically cache the LOB when I can not be sure if it will be "worked with".

ad 2) This is what I'm constantly struggling with because even if the original LOB is reasonably small it might grow and completely pollute my buffer cache.

Connor McDonald
May 31, 2022 - 1:55 am UTC

Even with a large lob, the buffer cache algorithms are pretty sophisticated, so we're pretty good at ensuring we don't whack the entire cache.

So I wouldn't lose too much sleep over it

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library