Improve BLOB Writing Performance in Oracle 11g

Question

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.

• Gaius: Where does AWR say that your waits are for this?

Answer 1

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.

Answer 2

You can tune LOB performance by specifying certain options in the Create table DDL for the table containing the LOB.

e.g.

create table lob_data (id number not null, data lob)
LOB (data) STORE AS (
 TABLESPACE lob
 INDEX (TABLESPACE lob_indx)
 PCTVERSION 0
 DISABLE STORAGE IN ROW
 CACHE READS NOLOGGING
 CHUNK 8192
 STORAGE (INITIAL 5M NEXT 5M)
);

Here I've assigned separate tablespaces for the Lob and lob_index, and also specified the NOLOGGING option. Also specified the chunk size as 8k, and tuned the storage params.

======================================= EDIT - Based on comments from kubanczyk

Perhaps I should have elaborated a bit more, so here goes.

I assumed the question was specifically asked because the author was seeing performance problems with inserts for his dataset. So the default chunk size, the 'storage in row' were NOT giving him the performance he expected to begin with.

I agree that I should have warned about the NOLOGGING suggestion. But then again, I assumed that the author would research a bit more, before simply copy pasting my solution.

Usually BLOBs/CLOBs come from external sources, so the database is more often a storage space for them, but not the authoritative source. By that I mean that even with NOLOGGING turned on for the LOB, the assumption is that in case of data loss , the external sources can be re-processed and the data re-populated.

Again this is a application /design specific issue. If the data simply can't be regenerated in case of data loss, then NOLOGGING is out of the question. But to simply dismiss the 'NOLOGGING' option as careless is not knowing and weighing the pros/cons of ARCHIVELOG in my opinion.

As to the chunk size, the question said that the average blob size was 3K, and by that I assumed, that there were a good many BLOBs well over 3K. If the average is approaching the upper limit of the in-row storage (i.e. 4K), I think it is fair to assume that 'out of row' storage is going to give better performance. Also consider this, today the average might be 3K, what if an year from now the average is 5K, then you've bypassed the 4K limit, and you are really better off with the 'out of row' storage, and 8K chunk size, anticipating a future increase in data size.

So while it is very easy to down vote , I would still stand by my suggestions, given the fact that this is a performance related question and not a 'how to do XYZ ?' type of questions. Performance is a tricy thing, and you've to weigh in all the pros/cons of all the options you have. There is simply not a one solution fits all type of response you can give for performance related issues. You can suggest pointers, and it would be up to the original auther to see what best fits his setup.

• kubanczyk: I would downvote, if only my reputation allowed. Careless suggestion of NOLOGGING. Suggesting CHUNK 8k where the question explicitly mentions 3k size. And most of all DISABLE STORAGE IN ROW, which by any means would bring 3k blob writes to a crawl.
• Jack Douglas: @kubanczyk I've done it for you - excellent points and hopefully Bhaskar will update his answer.
• user5294: Edited my original comment, I still stand by my suggestions, but have elaborated the post a bit more to explain why