Improve BLOB Writing Performance in Oracle 11g

oracleperformance

I have an application that writes a lot of rows into a table that contains a blob column. The average size of the blob is 3k bytes.

  • Is there any buffer that i can resize to improve inserts into this table?
  • Does the lob buffer still apply for Oracle 11g ?

Thx.

Best Answer

In Oracle, LOB (including BLOB) is stored as:

  • in-the-table LOB - if the LOB is smaller than 3900 bytes it can be stored inside the table row; by default this is enabled, unless you specify DISABLE STORAGE IN ROW
  • normal LOB - stored in a separate segment, outside of table, you may even put it in another tablespace; for these:
    • a minimum of CHUNK bytes are allocated and entirely redo-logged (even if LOB has only 1 byte)
    • there is an internal intermediate index behind a LOB column, which gets contentious on updates and may practically serialize them
    • access is multi-level and thus relatively slower
    • NOCACHE is the default
    • with CACHE option, the CACHE_SIZE_THRESHOLD is not taken into account, so a large LOB can waste your cache

The Metalink note ID 66431.1 describes this and may be of interest to you, if you have access there.

Related Question