|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
LOBS: Best Practices, 4 of 6
In addition to the guidelines described above 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, 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. In particular, these temporary LOBs are silently created when you use the following:
This is due to the fact that by using a package variable which persists in a session, allocating extra memory to manage temporary LOBs in every procedure call is avoided.
CREATE OR REPLACE PACKAGE pk is tmplob clob; END pk; / CREATE OR REPLACE PROCEDURE temp_lob_proc IS BEGIN -- instead of using a local LOB variabe, use a package variable here DBMS_LOB.CREATETEMPORARY(pk.tmplob, TRUE); -- Do some LOB data maniputation here DBMS_LOB.FREETEMPORARY(pk.tmplob); END; / DECLARE doc CLOB; BEGIN FOR i IN 1..400 LOOP temp_lob_proc(); END LOOP; END; /
LOCATOR1 BLOB; LOCATOR2 BLOB; DBMS_LOB.CREATETEMPORARY (LOCATOR1,TRUE,DBMS_LOB.SESSION); LOCATOR2 := LOCATOR;
This code causes a copy of the temporary LOB pointed to by LOCATOR1 to be created. When passing temporary LOB parameters to proecure/functions, you might also want to consider using pass by reference semantics in PL/SQL.
In OCI, to ensure copy semantics of LOB locators and data, OCILobLocatorAssign is used to copy temporary LOB locators as well as the LOB data. To avoid the deep copy, pointer assignment can be done, if copy semantics of locator copy is not intended. For example:
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, 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 needs to 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.