Best Performance Practices

Using SQL Loader

You can use SQL*Loader to bulk load LOBs.

See: Chapter 4, "Managing LOBs", "Using SQL Loader to Load LOBs", for a description of SQL*Loader Oracle8i Utilities for a more comprehensive description of SQL*Loader

Guidelines for Best Performance

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, "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:
  1. SQL UPDATE which replaces the entire LOB value

  2. 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.

Moving Data to LOB in Threaded Environment

Incorrect procedure

The following sequence, requires a new connection when using a threaded environment, adversely affects performance, and is inaccurate:

1. Create an empty (non-NULL) LOB

2. INSERT using the empty LOB

3. SELECT-FOR-UPDATE of the row just entered

4. Move data into the LOB

5. COMMIT. This releases the SELECT-FOR-UPDATE locks and makes the LOB data persistent.

The Correct Procedure

Note the following:

• There is no need to 'create' an empty LOB.

• You can use the RETURNING clause as part of the insert/update statement to return a locked LOB locator. This eliminates the need for doing a select for update as mentioned in step 3.

Hence the preferred procedure is as follows:

1. INSERT an empty LOB, RETURNING the LOB locator.

2. Move data into the LOB using this locator.

3. COMMIT. This releases the SELECT-FOR-UPDATE locks, and makes the LOB data persistent.

Alternatively, you can insert >4,000 byte of data directly for the LOB columns but not the LOB attributes.