Improve BLOB Writing Performance in Oracle 11g

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.

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.

StackExchange Database Administrators Q#8770, answer score: 5