Opinions re: storing LOBs in a separate tablespace

User_KC6Y3

Oracle 21c on Windows 11 64-bit laptop.

In our application, we use LOBs to store data as follows:

1. Blobs - storing month-end statements, customer characters, usually containing bitmap images, company logos, etc.
2. Clobs - storing large text-only data

The data in these LOBs is usually very static after initial creation; if a change is required, we usually delete the existing LOB and create a new one.

Is it worthwhile to store these LOBs in a separate tablespace, wondering about additional I/O required to access these LOBS.

Opinions, please.

Thanks

Murray

Jonathan Lewis

https://community.oracle.com/tech/developers/discussion/comment/16824925#Comment_16824925

I wish I'd found that paper about 15 years ago, it would have saved me a little time working out a couple of the mechanism for myself. I wouldn't push it as a guideline any more, though, as it is only about basicfile LOBs and omits a number of things that could make a big difference to someone using securefile LOBs.

Regards

Jonathan Lewis

Mike Kutz

Is it worthwhile to store these LOBs in a separate tablespace, wondering about additional I/O required to access these LOBS.

Why would there be additional IO???

rcc50886

it doesn't matter if LOB SEGMENT is using the same tablespace as of table or not.

MAkesure you use SECUREFILE LOB for performance gains.

Also read following white paper to understand the parameters that impacts the performnace

lob_performance_guidelines (oracle.com)

JohnWatson2

There is a hypothesis that storing LOBs in 32KB block size tablespace improves performance. I am not convinced.

Mike Kutz

I am not convinced.

Oh, I'm pretty sure it is there. But, the performance gain is in the same ballpark of count(*) vs count(1). (in other words not much.)

Jonathan Lewis

You could argue for putting each LOB segment in its own tablespace on the basis that they make it difficult to do any re-organisation of the tablespace they're in; and while you hope that you won't have to rebuild indexes, move tables etc. It is a good idea to plan for accidents - and having a LOB mixed in with a load of other objects may become a great inconvenience.

There's also the point - depending on your application and the scale of your operation - that you might have a good reason for putting your LOBs on different storage from the rest of database.

And, as has been mentioned, you could have a good reason for using a different block size for your LOBs, which means a separate tablespace and a separate buffer cache. (There's an example here, though, where the client made a bad choice with a non-standard block size.)

Regards

Jonathan Lewis

Jonathan Lewis

https://community.oracle.com/tech/developers/discussion/comment/16824925#Comment_16824925

I wish I'd found that paper about 15 years ago, it would have saved me a little time working out a couple of the mechanism for myself. I wouldn't push it as a guideline any more, though, as it is only about basicfile LOBs and omits a number of things that could make a big difference to someone using securefile LOBs.

Regards

Jonathan Lewis