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

---