|Oracle® Database Application Developer's Guide - Large Objects
10g Release 1 (10.1)
Part Number B10796-01
This chapter discusses the following topics:
This section describes performance guidelines for applications that use LOB datatypes.
If most LOBs in your database tables are small in size--8K bytes or less--and only a few rows have LOBs larger than 8K bytes, then use the following guidelines to maximize database performance:
Use the following guidelines to achieve maximum performance with LOBs:
NOCACHE' option, then each small read/write incurs an I/O. Reading/writing large quantities of data reduces the I/O.
OCILobRead2(). Basically, turn on LOB buffering before reading/writing small pieces of LOB data.
"LOB Buffering Subsystem" for more information on LOB buffering.
amount' parameter on input. Whenever possible, read and write in multiples of the LOB chunk size.
In addition to the guidelines described earlier under "LOB Performance Guidelines" on LOB performance in general, here are some guidelines for using temporary LOBs:
If you use the newly provided enhanced SQL semantics functionality in your applications, then there will be many more temporary LOBs created silently in SQL and PL/SQL than before. Ensure that temporary tablespace for storing these temporary LOBs islarge enough for your applications. In particular, these temporary LOBs are silently created when you use the following:
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 as well as 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 pointers to achieve reference semantics as illustrated in the following:
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 will see the change made to LOC2. */ OCILobRead2(...LOC1...)
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 will point to the same LOB until any modification is made through either LOB locator.
In PL/SQL, C (OCI), Java and other programmatic interfaces, SQL query results or PLSQL program executions return temporary LOBs for operation/function calls on LOBs. For example:
If the query is executed in PLSQL, then the returned temporary LOBs automatically get freed at the end of a PL/SQL program block. You can also explicitly free the temporary LOBs any time. In OCI and Java, the returned temporary LOB must be freed by the user explicitly.
Without proper deallocation of the temporary LOBs returned from SQL queries, temporary tablespace gets filled up steadily and you could observe performance degradation.
Be aware of the following performance issues when using SQL semantics with LOBs:
Chapter 9, "SQL Semantics and LOBs" for details on SQL semantics support for LOBs.
There two procedures that you can use to move data to LOBs in a threaded environment, one of which should be avoided.
The following sequence requires a new connection when using a threaded environment, adversely affects performance, and is not recommended:
The recommended procedure is as follows:
Alternatively, you can insert >4,000 byte of data directly for the LOB columns but not the LOB attributes.