Where is LOB data stored?

There was a question in Oracle-L about where is the LOB data actually stored (in the row or the LOB segments) and what are the exact conditions when a switch from one to another may happen. The documentation isn’t fully clear about this and the “4000 bytes” number may mislead people to think that you can store 4000 bytes of your data in a LOB item before it must move out-of-line.

I clarified this in my post in Oracle-L, but will post it here too. First check this diagram:

If you create the LOB column with DISABLE STORAGE IN ROW, then the LOB data is always stored out-of-line. LOB index is always used. Only the LOB ID is stored inline, and the ID is looked up in LOB index, where you’ll get the pointers to actual LOB chunk blocks.
If you create the LOB column with ENABLE STORAGE IN ROW, then the LOB data may be stored in-line or out-of-line.
If the total LOB data + overhead <= 4000 bytes, then the LOB item will be stored in-line. No LOB index is used, even if you modify the LOB later on as everything is stored in-line with the row and versioning/rollback is achieved with undo data.
If the total LOB data + overhead > 4000 bytes, then the LOB item will be stored out-of-line. If the LOB fits into 12 x LOB_chunk_size, then no LOB index entries are created, because the in-line LOB locator can store up to 12 pointers to the LOB chunk blocks for each lob item. So if your LOB chunk size is 8kB, you can store LOB items up to 96kB in size without inserting anything to LOB index. However if the LOB item is bigger, then no pointers are stored in-row and all pointers will be put to the LOB index.
Note that once you modify an existing LOB item (which is bigger than 4000 bytes with its overhead), but smaller than 12 x chunk_size, then LOB index will still be used after the first LOB change operation as pointers to the old LOB chunk versions have to be stored in it (LOB segments don’t rely on undo for rollback & consistency, but just use LOB chunk versioning managed by LOB index).
The “overhead” of an in-line LOB item is 36 bytes, so the actual LOB data must be 4000 – 36 = 3964 bytes or less in order to fully fit in-row. And note that we are talking about bytes here, not characters. With multibyte character sets a character in a CLOB may take multiple bytes.
I wrote a presentation about LOB internals quite some time ago, back when Oracle 10.1 came out I think – check it here:

Enjoy! :-)
P.S. The next Advanced Oracle Troubleshooting v2.0 class will start in 3 weeks! (I just added the online payment option back too for convenience:)

NB! After a 1.5 year break, this year’s only Advanced Oracle Troubleshooting training class (updated with Oracle 12c content) takes place on 16-20 November & 14-18 December 2015, so sign up now if you plan to attend this year!

This entry was posted in Oracle. Bookmark the permalink.

14 Responses to Where is LOB data stored?

  1. Bruno Lavoie says:

    Nice & Detailled information!!
    So, without these details in official docs, where do you find all these informations?
    Many thanks

  2. Kamus says:

    Thanks Tanel, your post always help us a lot. :-)

  3. Ron says:

    Thanks Tanel… the slides were VERY cool (if a little old).

    Is there a way to tell if the lob was stored in line or out of line (other than by looking at the size [using dbms_lob.getlength] and then guessing based on how close the size is to 4000 bytes)?

    I would like to select all rows from a table where the lob was stored out-of-line. Possible?

    • Tanel Poder says:

      Yeah I think the best way is to use DBMS_LOB.GETLENGTH and hope that the 3964 byte length assumption holds true :)

      I checked whether there’s an “ISINLINE” function in DBMS_LOB, like there’s ISSECUREFILE, ISTEMPORARY, ISOPEN … but there isn’t an ISINLINE one…

  4. Manish says:

    This is a great post Thanks. do you have any updated material about how clobs stored as securefiles?


  5. fabrooms says:

    So how can I check where a LOB is stored? It’s not possible to use the IN_ROW column from dba_lobs… Any ideas?

    • Tanel Poder says:

      Do you mean where an individual LOB item is stored? I don’t know of any direct function call which would examine the LOB locator and tell you that. But you can use DBMS_LOB.GETLENGTH on every LOB item and check if it’s bigger than 3964 bytes.

      Something like this (assuming that the LOB was created with inline storage enabled, otherwise all items would be in the separate LOB segment anyway):

      SQL> SELECT 
               CASE WHEN DBMS_LOB.GETLENGTH(a) > 3964 THEN 'Out-of-line' 
                    ELSE 'Inline' 
               END x, COUNT(*) 
      FROM tl 
      GROUP BY 
           CASE WHEN DBMS_LOB.GETLENGTH(a) > 3964 THEN 'Out-of-line' 
           ELSE 'Inline' 
      X             COUNT(*)
      ----------- ----------
      Inline            1000
      Out-of-line       3001
  6. fabrooms says:

    Thanks Tanel but I am actually looking for a way to find out if a LOB is in row even if it is below 3964 bytes. This has been bugging me for a while…

    • Tanel Poder says:

      Actually using GETLENGTH can be problematic when using multibyte charactersets … the length may be reported 2000 chars for a CLOB, but it actually might use 4000 chars, thus going out of line…

      The sure way would be to dump the block where the row is in and check the contents of the row itself, if it’s out of line, its just the lob locator there (up to 48 bytes I think).

      Another way is to check v$sesstat (with snapper for example) and select that LOB item by that row’s ID. And then see whether the “lob reads” metric went up or not. If the “lob reads” goes up, the LOB was out-of-line. If it doesn’t then the LOB item was inline. Note that you have to select that lob (or DBMS_LOB.SUBSTR), not just GETLENGTH as the length number will be taken from the LOB locator in the row. Also, use DBMS_LOB.SUBSTR, not just SUBSTR as apparently the SQL engine’s SUBSTR causes also a “lob write” – might be because it creates a temporary LOB “slice” from the original LOB item and then gets its length. Or maybe it’s just a bug.

  7. Vahid Sadeghi says:

    Thanks Tanel …
    You talked about important issue about LOB that i can’t found it in the oracle documents .

  8. K.P says:

    Dear Tanel Poder,

    In AWR of my production database, the event “direct path read” appear at 1st position of top 5 events. I found information about it.

    So, I wonder that whether LOB segment cached on SGA ? Or only PGA read/write it (LOB segment) directly from disk when needed.


    • Tanel Poder says:

      You’ll need to check DBA_LOBS if your system if you are using LOBs and make sure they have the CACHE or CACHE READS attribute set (instead of NOCACHE). This is assuming that you do want to cache them in your buffer cache. Before changing anything, you should verify by measuring what causes these waits. Direct LOB reads would increment the “physical reads direct (lob)” statistic for example. The other way would be to use an ASH report (or my ashtop.sql script) to find out the TOP SQL_IDs waiting for these wait events and seeing which execution plan lines cause these, which tables are involved etch (search for asqlmon.sql in my blog).

      Starting from 11g also serial full table scans (and fast full index scans) can use direct path reads, so my first bet would be on that. Read more about this here (http://blog.tanelpoder.com/2012/09/03/optimizer-statistics-driven-direct-path-read-decision-for-full-table-scans-_direct_read_decision_statistics_driven/).

      The key is to pick one of the sessions and measure what is going on – before changing anything.

Leave a Reply

Your email address will not be published. Required fields are marked *