|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Modeling and Design, 20 of 21
Be aware of the following performance issues, when using SQL semantics with LOBs.
In Oracle9i, the maximum length restriction for all column data and buffer size when processing SQL queries, can be more than 4K bytes. You can process LOB data, which can be as long as 4G bytes, in SQL!
Temporary LOBs are used internally if the data is greater than 4K bytes to store intermediate results.
For large VARCHARs, SQL queries now perform in a similar fashion to when accessing CLOBs through the previous set of LOB APIs.
In PL/SQL, C (OCI), and Java, SQL query results return temporary LOBs for operation/function calls on LOB columns. For example:
Returned temporary LOBs automatically get freed at the end of a PL/SQL program block.
You can choose to free any unneeded temporary LOBs at any time to free up system resources and temporary tablespace. Without proper deallocation of the temporary LOBs returned from SQL queries, temporary tablespace gets filled up steadily and you could observe performance degradation. See "PL/SQL Example 6: Freeing Temporary LOBs Automatically and Manually", for an example of freeing temporary LOBs explicitly.
The performance of an SQL query execution on CLOB columns should be compared to that of a query on VARCHAR2s or LONGs of the same size. Expect the performance on LOBs to be within 80% of VARCHAR2s/LONGs or better.
System/Database Management: After this newly provided enhanced SQL semantics functionality is used in your applications, 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 is large enough for your applications!