LOB Performance Guidelines

Use the following guidelines to achieve maximum performance with LOBs:

• When Possible, Read/Write Large Data Chunks at a Time: Since LOBs are big, you can obtain the best performance by reading and writing large chunks of a LOB value at a time. This helps in several respects:
  1. If accessing the LOB from the client side and the client is at a different node than the server, large reads/writes reduce network overhead.

  2. If using the 'NOCACHE' option, each small read/write incurs an I/O. Reading/writing large quantities of data reduces the I/O.

  3. Writing to the LOB creates a new version of the LOB CHUNK. Therefore, writing small amounts at a time will incur the cost of a new version for each small write. If logging is on, the CHUNK is also stored in the redo log.

• Use LOB Buffering to Read/Write Small Chunks of Data: If you need to read/write small pieces of LOB data on the client, use LOB buffering -- see OCILobEnableBuffering(), OCILobDisableBuffering(), OCILobFlushBuffer(), OCILobWrite(), OCILobRead(). Basically, turn on LOB buffering before reading/writing small pieces of LOB data.

  See Also: Chapter 5, "Large Objects: Advanced Topics", "LOB Buffering Subsystem" for more information on LOB buffering.

• Use OCILobRead() and OCILobWrite() with Callback: So that data is streamed to and from the LOB. Ensure the length of the entire write is set in the 'amount' parameter on input. Whenever possible, read and write in multiples of the LOB chunk size.

• Use a Checkout/Checkin Model for LOBs: LOBs are optimized for the following operations:
  • SQL UPDATE which replaces the entire LOB value

  • Copy the entire LOB data to the client, modify the LOB data on the client side, copy the entire LOB data back to the database. This can be done using OCILobRead() and OCILobWrite() with streaming.

• Try to commit changes frequently.

  See Also: Chapter 7, "Modeling and Design", "Performance Attributes When Using SQL Semantics with LOBs" , for information about performance issues when using SQL semantics with LOBs

Some Performance Numbers

Table 9-1 lists the results of a performance test that enqueued 500 messages using a chunk size of 8KB for the LOB part of the payload. This performance test used Oracle8i Release 3 (8.1.7), and a DB_BLOCKSIZE = 8192 (8K), identical to the operating system block size.

Table 9-1 Response Time When Enqueueing 500 Messages With and Without CACHE and LOGGING

CHUNK SIZE	CACHE (Y/N)	LOGGING (Y/N)	MESSAGE_SIZE	EVENT 10359	RESPONSE TIME
8k	NOCACHE	NOLOGGING	3900 bytes	not set	01:33 sec
8k	NOCACHE	NOLOGGING	4000 bytes	not set	06:19 sec
8k	NOCACHE	NOLOGGING	4000 bytes	set	04:36 sec
8k	CACHE		3900 bytes	not set	01:22 sec
8k	CACHE		4000 bytes	not set	01:22 sec
8k	CACHE		4000 bytes	set	01:83 sec
8k	CACHE		20000 bytes	set	02:33 sec

Previous response times using a 16k chunksize, NOCACHE, and NOLOGGING for a message of 4000 bytes was 12:28 sec.

These results indicate that the CACHE parameter is the parameter giving the best performance improvement.