Performance Attributes When Using SQL Semantics with LOBs

Be aware of the following performance issues, when using SQL semantics with LOBs.

Inserting More than 4K Bytes Data Into LOB Columns

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.

Note: This could degrade performance. The extra load in query processing comes from both the cost of dealing with the larger amount of intermediate results and the lower efficiency of accessing temporary LOBs.

For large VARCHARs, SQL queries now perform in a similar fashion to when accessing CLOBs through the previous set of LOB APIs.

Temporary LOB Creation/Deallocation

In PL/SQL, C (OCI), and Java, SQL query results return temporary LOBs for operation/function calls on LOB columns. For example:

SELECT substr(CLOB_Column, 4001, 32000) FROM ...

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.

Performance Measurement

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.

Note: 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!