LOBS – Storage, Redo and Performance Issues



  This is a short note on the internal storage of LOBs. The information

  here is intended to supplement the documentation and other notes

  which describe how to use LOBS. The focus is on the storage characteristics

  and configuration issues which can affect performance.

  There are 4 types of LOB:

   CLOB, BLOB, NCLOB stored internally to oracle

   BFILE stored externally

  The note mainly discusses the first 3 types of LOB which as stored INTERNALLY

  within the oracle DBMS. BFILE’s are pointers to external files and

  are only mentioned briefly.  

  Examples of handling LOBs can be found in Note 47740.1



  There are many attributes associated with LOB columns. The aim here

  is to cover the fundamental points about each of the main attributes.

  The attributes for each LOB column are specified using the

  “LOB (lobcolname) STORE AS …” syntax.

  A table containing LOBs (CLOB, NCLOB and BLOB) creates 2 additional

  disk segments per LOB column – a LOBINDEX and a LOBSEGMENT. These

  can be viewed, along with the LOB attributes, using the dictionary views:


  which give the columns:

OWNER              Table Owner

TABLE_NAME         Table name

COLUMN_NAME        Column name in the table

SEGMENT_NAME       Segment name of the LOBSEGMENT

INDEX_NAME         Segment name of the LOBINDEX

CHUNK              Chunk size (bytes)

PCTVERSION         PctVersion

CACHE              Cache option of the LOB Segment (yes/no)

LOGGING            Logging mode of the LOB segment (yes/no)

IN_ROW             Whether storage in row is allowed (yes/no)

Storage Parameters


  By default LOB segments are created in the same tablespace as the

  base table using the tablespaces default storage details. You can

  specify the storage attributes of the LOB segments thus:

  Eg: Create table DemoLob ( A number, B clob )


STORE AS lobsegname (

  TABLESPACE lobsegts

  STORAGE (lobsegment storage clause)

  INDEX lobindexname (


STORAGE ( lobindex storage clause )



TABLESPACE tables_ts

STORAGE( tables storage clause )


   In 8.0 the LOB INDEX can be stored separately from the lob segment.

   If a tablespace is specified for the LOB SEGMENT then the LOB INDEX

   will be placed in the same tablespace UNLESS a different tablespace

   is explicitly specified.

  Unless you specify names for the LOB segments system generated names

  are used.

In ROW Versus Out of ROW


  LOB columns can be allowed to store data within the row or not as detailed

  below. Whether in-line storage is allowed or not can ONLY be specified

  at creation time.

  “STORE AS ( enable storage in row )”

Allows LOB data to be stored in the TABLE segment provided

it is less than about 4000 bytes.  

The actual maximum in-line LOB is 3964 bytes.

If the lob value is greater than 3964 bytes then the LOB data is

stored in the LOB SEGMENT (ie: out of line). An out of line

LOB behaves as described under ‘disable storage in row’ except that

if its size shrinks to 3964 or less the LOB can again be stored


When a LOB is stored out-of-line in an ‘enable storage in row’

LOB column between 36 and 84 bytes of control data remain in-line

in the row piece.

In-line LOBS are subject to normal chaining and row migration

rules within oracle. Ie: If you store a 3900 byte LOB in a row

with a 2K block size then the row piece will be chained across

two or more blocks.

Both REDO and UNDO are written for in-line LOBS as they are part

of the normal row data.

  “STORE AS ( disable storage in row )”

This option prevents any size of LOB from being stored in-line.

Instead a 20 byte LOB locator is stored in the ROW which gives

a unique identifier for a LOB in the LOB segment for this column.

The Lob Locator actually gives a key into the LOB INDEX which

contains a list of all blocks (or pages) that make up the LOB.

The minimum storage allocation for an out of line LOB is 1 Database

BLOCK per LOB ITEM and may be more if CHUNK is larger than a

single block.

UNDO is only written for the column locator and LOB INDEX changes.

No UNDO is generated for pages in the LOB SEGMENT.

Consistent Read is achieved by using page versions.

Ie: When you update a page of a LOB the OLD page remains and a

    new page is created. This can appear to waste space but

    old pages can be reclaimed and reused.

CHUNK size


  “STORE AS ( CHUNK bytes ) ”

Can ONLY be specified at creation time.

In 8.0 values of CHUNK are in bytes and are rounded to the next

highest multiple of DB_BLOCK_SIZE without erroring.

Eg: If you specify a CHUNK of 3000 with a block size of 2K then

    CHUNK is set to 4096 bytes.

“bytes” / DB_BLOCK_SIZE determines the unit of allocation of

blocks to an ‘out of line’ LOB in the LOB segment.

Eg: if CHUNK is 32K and the LOB is ‘disable storage in row’  

    then even if the LOB is only 10 bytes long 32K will be

    allocated in the LOB SEGMENT.

CHUNK does NOT affect in-line LOBS.




PCTVERSION can be changed after creation using:

                Alter TABLE tabname MODIFY LOB (lobname) ( PCTVERSION n );

PCTVERSION affects the reclamation of old copies of LOB data.

This affects the ability to perform consistent read.

If a session is attempting to use an OLD version of a LOB

and that version gets overwritten (because PCTVERSION is too small)

then the user will typically see the errors:

orA-01555: snapshot too old:

rollback segment number  with name “” too small

orA-22924: snapshot too old

PCTVERSION can prevent OLD pages being used and force the segment

to extend instead.

Do not expect PCTVERSION to be an exact percentage of space as there

is an internal fudge factor applied.




This option can be changed after creation using:

Alter TABLE tabname MODIFY LOB (lobname) ( CACHE );


Alter TABLE tabname MODIFY LOB (lobname) ( NOCACHE );

With NOCACHE set (the default) reads from and writes to the

LOB SEGMENT occur using direct reads and writes. This means that

the blocks are never cached in the buffer cache and the the oracle

shadow process performs the reads/writes itself.

The reads / writes show up under the wait events “direct path read”

and “direct path write” and multiple blocks can be read/written at

a time (provided the caller is using a large enough buffer size).

When set the CACHE option causes the LOB SEGMENT blocks to

be read / written via the buffer cache . Reads show up as

“db file sequential read” but unlike a table scan the blocks are

placed at the most-recently-used end of the LRU chain.

The CACHE options for LOB columns is different to the CACHE

option for tables as CACHE_SIZE_THRESHOLD does not limit the

size of LOB read into the buffer cache. This means that extreme

caution is required otherwise the read of a long LOB can effectively

flush the cache.

In-line LOBS are not affected by the CACHE option as they reside

in the actual table block (which is typically accessed via the buffer

cache any way).

The cache option can affect the amount of REDO generated for

out of line LOBS. With NOCACHE blocks are direct loaded and

so entire block images are written to the REDO stream. If CHUNK

is also set then enough blocks to cover CHUNK are written to REDO.

If CACHE is set then the block changes are written to REDO.

Eg: In the extreme case  ‘DISABLE STORAGE IN ROW  NOCACHE  CHUNK 32K’

    would write redo for the whole 32K even if the LOB was only

    5 characters long. CACHE would write a redo record describing the

    5 byte change (taking about 100-200 bytes).




This option can be changed after creation but the LOGGING / NOLOGGING

attribute must be prefixed by the NOCACHE option. The CACHE option

  implicitly enables LOGGING.

The default for this option is LOGGING.

If a LOB is set to NOCACHE NOLOGGING then updates to the LOB SEGMENT

are not logged to the redo logs. However, updates to in-line LOBS

are still logged as normal. As NOCACHE operations use direct

block updates then all LOB segment operations are affected.

NOLOGGING of the LOB segment means that if you have to recover the

database then sections of the LOB segment will be marked as corrupt

during recovery.

Space required for updates


  If a LOB is out-of-line then updates to pages in the LOB cause new

  versions of those pages to be created. Rollback is achieved by reverting

  back to the pre-updated page versions. This has implications on the

  amount of space required when a LOB is being updated as the LOB SEGMENT

  needs enough space to hold both the OLD and NEW pages concurrently in case

  your transaction rolls back.

  Eg: Consider the following:

Insert a large LOB LOB SEGMENT extends take the new pages


Delete the above LOB The LOB pages are not yet free as

they will be needed in case of


Insert a new LOB Hence this insert may require more

space in the LOB SEGMENT

COMMIT; Only after this point could the

deleted pages be used.

Performance Issues


  Working with LOBs generally requires more than one round trip to the database.

  The application first has to obtain the locator and only then can perform

  operations against that locator. This is true for inline or out of line


  The buffer size used to read / write the LOB can have a significant

  impact on performance, as can the SQL*Net packet sizes.

  Eg: With OCILobRead() a buffer size is specified for handling the LOB.

      If this is small (say 2K) then there can be a round trip to the database

      for each 2K chunk of the LOB. To make the issue worse the server will

      only fetch the blocks needed to satisfy the current request so may

      perform single block reads against the LOB SEGMENT. If however a larger

      chunk size is used (say 32K) then the server can perform multiblock

      operations and pass the data back in larger chunks.

  There is a LOB buffering subsystem which can be used to help improve

  the transfer of LOBs between the client and server processes. See the

  documentation for details of this.



  BFILEs are quite different to internal LOBS as the only real storage

  issue is the space required for the inline locator. This is about 20 bytes

  PLUS the length of the directory and filename elements of the BFILENAME.

  The performance implications of the buffer size are the same as for internal



0 条评论。


注意 - 你可以用以下 HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>