LOB Performance Guidelines

10.1 LOB Performance Guidelines

This section provides performance guidelines while using LOBs through Data Interface or LOB APIs.

LOBs can be accessed using the Data Interface or through the LOB APIs.

10.1.1 All LOBs

Learn about the guidelines to achieve good performance while using LOBs in this section.

The following guidelines will help you get the the best performance when using LOBs, and minimize the number of round trips to the server:

To minimize I/O:
- Read and write data at block boundaries. This optimizes I/O in many ways, e.g., by minimizing UNDO generation. For temporary LOBs and securefile LOBs, usable data area of the tablespace block size is returned by the following APIs: DBMS_LOB.GETCHUNKSIZE in PLSQL, and OCILobGetChunkSize() in OCI. When writing in a loop, design your code so that one write call writes everything that needs to go in a database block, thus ensuring that consecutive writes don't write to the same block.
- Read and write large pieces of data at a time.
- The 2 recommendations above can be combined by reading and writing in large whole number multiples of database block size returned by the DBMS_LOB.GETCHUNKSIZE/OCILobGetChunkSize() API.

To minimize the number of round trips to the server:
- If you know the maximum size of your lob data, and you intend to read or write the entire LOB, use the Data Interface as outlined below. You can allocate the entire size of lob as a single buffer, or use piecewise / callback mechanisms.
  - For read operations, define the LOB as character/binary type using the OCIDefineByPos() function in OCI and the DefineColumnType() function in JDBC.
  - For write operations, bind the LOB as character/binary type using the OCIBindByPos() function in OCI and the setString() or setBytes() methods in JDBC.
- Otherwise, use the LOB APIs as follows:
  - Use LOB prefetching for reads. Define the LOB prefetch size such that it can accommodate majority of the LOB values in the column.
  - Use piecewise or callback mechanism while using OCILobRead2 or OCILobWrite2 operations to minimize the roundtrips to the server.

10.1.2 Performance Guidelines While Using Persistent LOBs

In addition to the performance guidelines applicable to all LOBs described earlier, here are some performace guidelines while using persistent LOBs.

Maximize writing to a single LOB in consecutive calls within a transaction. Frequently switching across LOBs or having interleaving DML statements prevent caching from reaching its maximum efficiency.

Avoid taking savepoints or commiting too frequently. This neutralizes the advantage of caching while writing.

Note:

Oracle recommends Securefile LOBs for storing persistent LOBs, hence this chapter focuses only on Securefile storage. All mentions of "LOBs" in the persistent LOB context is for Securefile LOBs unless otherwise mentioned.

10.1.3 Temporary LOBs

In addition to the performance guidelines applicable to all LOBs described earlier, following are some guidelines for using temporary LOBs:

Temporary LOBs reside in the PGA memory or the temporary tablespace, depending on the size. Please ensure that you have a large enough PGA memory and temporary tablespace for the temporary LOBs used by your application.
Use a separate temporary tablespace for temporary LOB storage instead of the default system tablespace. This avoids device contention when copying data from persistent LOBs to temporary LOBs.

If you use SQL or PL/SQL semantics for LOBs in your applications, then many temporary LOBs are created silently. Ensure that PGA memory and temporary tablespace for storing these temporary LOBs is large enough for your applications. In particular, these temporary LOBs are silently created when you use the following:
- SQL functions on LOBs
- PL/SQL built-in character functions on LOBs
- Variable assignments from VARCHAR2/RAW to CLOBs/BLOBs, respectively.
- Perform a LONG-to-LOB migration
Free up temporary LOBs returned from SQL queries and PL/SQL programs

In PL/SQL, C (OCI), Java and other programmatic interfaces, SQL query results or PL/SQL program executions return temporary LOBs for operation/function calls on LOBs. For example:
```
SELECT substr(CLOB_Column, 4001, 32000) FROM ... 
```
If the query is executed in PL/SQL, then the returned temporary LOBs are automatically freed at the end of a PL/SQL program block. You can also explicitly free the temporary LOBs at any time. In OCI and Java, the returned temporary LOB must be explicitly freed.

Without proper deallocation of the temporary LOBs returned from SQL queries, you may observe performance degradation.
In PL/SQL, use NOCOPY to pass temporary LOB parameters by reference whenever possible.

See Also:

Oracle Database PL/SQL Language Referencefor more information on passing parameters by reference and parameter aliasing
Temporary LOBs created with the CACHE parameter set to true move through the buffer cache and avoid the disk access.
Oracle provides v$temporary_lobs view to monitor the use of temporary LOBs across all open sessions. Here is an example:
```
SQL> select * from v$temporary_lobs;

       SID CACHE_LOBS NOCACHE_LOBS ABSTRACT_LOBS    CON_ID
---------- ---------- ------------ ------------- ----------
       141          2            3             4          0
       146          0            0             1          0
       148          0            0             1          0
```
Following is the interpretation of output:
- The SID column is the session ID.
- The CACHE_LOBS column shows that session 141 currently has 2 temporary lobs in the temporary tablespace with CACHE turned on.
- The NOCACHE_LOBS column shows that session 141 currently has 3 temporary lobs in the temporary tablespace with CACHE turned off.
- The ABSTRACT_LOBS column shows that session 141 currently has 4 temporary lobs in the PGA memory.
- The CON_ID column is the pluggable database container ID.

For optimal performance, temporary LOBs use reference on read, copy on write semantics. When a temporary LOB locator is assigned to another locator, the physical LOB data is not copied. Subsequent READ operations using either of the LOB locators refer to the same physical LOB data. On the first WRITE operation after the assignment, the physical LOB data is copied in order to preserve LOB value semantics, that is, to ensure that each locator points to a unique LOB value.

In PL/SQL, reference on read, copy on write semantics are illustrated as follows:

LOCATOR1 BLOB; 
LOCATOR2 BLOB; 
DBMS_LOB.CREATETEMPORARY (LOCATOR1,TRUE,DBMS_LOB.SESSION); 

-- LOB data is not copied in this assignment operation:  
LOCATOR2 := LOCATOR;  
-- These read operations refer to the same physical LOB copy: 
DBMS_LOB.READ(LOCATOR1, ...); 
DBMS_LOB.GETLENGTH(LOCATOR2, ...); 

-- A physical copy of the LOB data is made on WRITE:  
DBMS_LOB.WRITE(LOCATOR2, ...);

In OCI, to ensure value semantics of LOB locators and data, OCILobLocatorAssign() is used to copy temporary LOB locators and the LOB Data. OCILobLocatorAssign() does not make a round trip to the server. The physical temporary LOB copy is made when LOB updates happen in the same round trip as the LOB update API as illustrated in the following:

OCILobLocator *LOC1;
OCILobLocator *LOC2;
OCILobCreateTemporary(... LOC1, ... TRUE,OCI_DURATION_SESSION);

/* No round-trip is incurred in the following call. */
OCILobLocatorAssign(... LOC1, LOC2);

/* Read operations refer to the same physical LOB copy. */
OCILobRead2(... LOC1 ...)

/* One round-trip is incurred to make a new copy of the
 * LOB data and to write to the new LOB copy.
 */
OCILobWrite2(... LOC1 ...)

/* LOC2 does not see the same LOB data as LOC1. */
OCILobRead2(... LOC2 ...)

If LOB value semantics are not intended, then you can use C pointer assignment so that both locators point to the same data as illustrated in the following code snippet:

OCILobLocator *LOC1;
OCILobLocator *LOC2;
OCILobCreateTemporary(... LOC1, ... TRUE,OCI_DURATION_SESSION);

/* Pointer is copied. LOC1 and LOC2 refer to the same LOB data. */
LOC2 = LOC1;

/* Write to LOC2. */
OCILobWrite2(...LOC2...)

/* LOC1 sees the change made to LOC2. */
OCILobRead2(...LOC1...)

Use OCI_OBJECT mode for temporary LOBs

To improve the performance of temporary LOBs on LOB assignment, use OCI_OBJECT mode for OCILobLocatorAssign(). In OCI_OBJECT mode, the database tries to minimize the number of deep copies to be done. Hence, after OCILobLocatorAssign() is done on a source temporary LOB in OCI_OBJECT mode, the source and the destination locators point to the same LOB until any modification is made through either LOB locator.