Forum Stats

  • 3,836,791 Users
  • 2,262,191 Discussions
  • 7,900,111 Comments

Discussions

Opinions re: storing LOBs in a separate tablespace

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

Best Answer

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    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
    rcc50886 Member Posts: 484 Bronze Badge

    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
    JohnWatson2 Member Posts: 4,469 Silver Crown

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

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    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
    Jonathan Lewis Member Posts: 9,995 Blue Diamond

    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
    Jonathan Lewis Member Posts: 9,995 Blue Diamond
    Answer ✓

    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