Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Where does AWR say that your waits are for this? –  Gaius Dec 7 '11 at 13:01

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer
    
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. –  kubanczyk Jan 1 '12 at 10:04
    
@kubanczyk I've done it for you - excellent points and hopefully Bhaskar will update his answer. –  Jack Douglas Jan 1 '12 at 10:53
    
Edited my original comment, I still stand by my suggestions, but have elaborated the post a bit more to explain why –  Bhaskar Karambelkar Jan 3 '12 at 21:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.