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.
- 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.GETCHUNKSIZEin 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
- 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:
- 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
setBytes()methods in JDBC.
- For read operations, define the LOB as character/binary type using the
- Otherwise, use the LOB APIs as
- 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
OCILobWrite2operations to minimize the roundtrips 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.
See Also:Data Interface for Persistent LOBs
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
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.
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_lobsview 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 0Following is the interpretation of output:
SIDcolumn is the session ID.
CACHE_LOBScolumn shows that session 141 currently has 2 temporary lobs in the temporary tablespace with CACHE turned on.
NOCACHE_LOBScolumn shows that session 141 currently has 3 temporary lobs in the temporary tablespace with CACHE turned off.
ABSTRACT_LOBScolumn shows that session 141 currently has 4 temporary lobs in the PGA memory.
CON_IDcolumn 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_OBJECTmode, 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_OBJECTmode, the source and the destination locators point to the same LOB until any modification is made through either LOB locator.