How to Select BLOB Chunk Size

Chunk size is parameter you set on a BLOB columns only.  Setting this parameter correctly can improve BLOB reading performance and/or storage utilized by BLOB segment.

Here are some scenarios to help you determine which size is best for you:

If your BLOB size is less than 4K and you are using inline option (which you should always use anyway regardless of your BLOB size), then chunk size parameter doesn’t apply. “LOGSEGMENT” and “LOGINDEX” size will not change because data is saved within data block.

If your BLOB size is bigger than 4k and less or equal 8k then set chunk size to 8k. Best of both worlds, you should get the best performance and space utilization.

If your BLOB size is bigger than 8k and you care about good performance of BLOB (read/write) over space utilization, then set chunk size to 32K.

If your BLOB size is bigger than 8k and you care about space utilization over performance, then set chunk size to 8K.

If your BLOB size is unknown and you only care about performance (read/write), then set chunk size to 32K.

If your BLOB size is unknown  and you only care about saving space, then set chunk size to 8K.

 

Note: You might think why not aim for speed over storage, after all  storage is cheap. I  agree with this premise, but, if you are storing  TBs of BLOBs like we are, and it is not accessed as much, you actually might start considering  saving storage over performance gains.

 

These finding were based on tests we ran in a 10.2.0.5, 64-bit environment.

 

Reading BLOBs from Database Tests

First test case is reading 35 MB BLOB from the database, notice about  40% increase in speed with 32K block size over 8K block.

Second test is reading 4 MB BLOB from the database, again notice about 60% increase in speed with 32K  block size over 8K block.

Read Performance Tests 32k vs 8k

BLOB Size

Chunk Size

Number of I/O Reads

Time in MSEC

35 MB

32K

1074

300

 

8K

4295

500

 

 

 

 

4 MB

32k

128

0.047

 

8K

509

0.078

 

 

Writing BLOBs to Database Test

Writing test is inserting in the database a 10K BLOB, repeated 10 times. Now notice LOB segment size is about 2.5 time larger with 32K than 8K.

Space Utilization Test 32k vs 8k

BLOB Size

Chunk Size

LOB Segment Size

10K, 10 times

32K

655360

 

8k

262144

 

Hazem Ameen

Senior Oracle DBA

About these ads

2 Comments on “How to Select BLOB Chunk Size”

  1. Sandro says:

    Hi Hazem,
    I think your missing test checks on write performance.

    Thanks,

    Sandro

  2. Camilo Tan says:

    Can you publish the DDL statements of your tests. I am interested to know if you’re using cache or nocahce (default).


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.