Temporary LOB Performance Guidelines

In addition to the guidelines described above under "LOB Performance Guidelines" on LOB performance in general, here are some guidelines for using temporary LOBs:

• 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 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:

  • SQL functions on LOBs

  • PL/SQL built-in character functions on LOBs

  • Variable assignments from VARCHAR2/RAW to CLOBs/BLOBs, respectively.

  • Perform a LONG-to-LOB migration

• In PLSQL, use NOCOPY to pass temporary LOB parameters by reference whenever possible. Refer to the "parameter passing by reference" description in the PL/SQL User's Guide and Reference, Chapter 7, under "Understanding Parameter Aliasing".

• Using Temporary LOBs in PL/SQL Procedure Loops. When repeatedly creating temporary LOBs in PL/SQL procedures in a loop, performance is improved when a PL/SQL package LOB locator variable is used inside the procedure instead of using a local variable.

  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.

  Note: Temporary LOBs created using a session locator are not cleaned up automatically at the end of function or procedure calls. The temporary LOB should be explicitly freed by calling DBMS_LOB.FREETEMPORARY().

  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; 
  / 
  
  

• Take advantage of buffer cache on temporary LOBs. Temporary LOBs created with the CACHE parameter set to true move through the buffer cache. Otherwise temporary LOBs are read directly from, and written directly to, disk.

• Be aware of the cost incurred in assigning temporary LOB variables. Temporary LOBs create entirely new copies of themselves on assignments. For example:

    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:

    OCILOBLocator *LOC1; 
    OCILOBLocator *LOC2; 
    OCILOBCREATETEMPORARY (LOC1,TRUE,OCIDURATIONSESSION); 
    LOC2 = LOC1; 
  
  

• Free up temporary LOBs returned from SQL queries and PLSQL programs.

  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:

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

  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.