9
LOBS: Best Practices

This chapter discusses the following topics:

• Using SQL*Loader
• LOB Performance Guidelines
• Moving Data to LOBs in a Threaded Environment
• Migrating from LONGs to LOBs

Using SQL*Loader

You can use SQL*Loader to bulk load LOBs.

Loading XML Documents Into LOBs With SQL*Loader

Because LOBs can be quite large, SQL*Loader can load LOB data from either the main datafile (inline with the rest of the data) or from LOBFILEs. Figure 9-1 shows the LOBFILE syntax.

Figure 9-1 The LOBFILE Syntax

Text description of the illustration apa30.gif

LOB data can be lengthy enough that it makes sense to load it from a LOBFILE. In LOBFILEs, LOB data instances are still considered to be in fields (predetermined size, delimited, length-value), but these fields are not organized into records (the concept of a record does not exist within LOBFILEs). Therefore, the processing overhead of dealing with records is avoided. This type of organization of data is ideal for LOB loading.

There is no requirement that a LOB from a LOBFILE fit in memory. SQL*Loader reads LOBFILEs in 64K chunks. To load physical records larger than 64K, you can use the READSIZE parameter to specify a larger size.

It is best to load XMLType columns or columns containing XML data in CLOBs, using LOBFILEs.

• When the XML is valid. If the XML data in the LOBFILE is large and you know that the data is valid XML, then use direct-path load since it bypasses all the XML validation processing.
• When the XML needs validating. If it is imperative that the validity of the XML data be checked, then use conventional path load, keeping in mind that it is not as efficient as a direct-path load.

A conventional path load executes SQL INSERT statements to populate tables in an Oracle database. A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files.

A direct-path load does not compete with other users for database resources, so it can usually load data at near disk speed. Considerations inherent to direct path loads, such as restrictions, security, and backup implications, are discussed in Chapter 9 of Oracle9i Database Utilities.

Figure 9-2 illustrates SQL*Loader's direct-path load and conventional path loads.

Tables to be loaded must already exist in the database. SQL*Loader never creates tables. It loads existing tables that either already contain data or are empty.

The following privileges are required for a load:

• You must have INSERT privileges on the table to be loaded.
• You must have DELETE privilege on the table to be loaded, when using the REPLACE or TRUNCATE option to empty out the table's old data before loading the new data in its place.

  See Also: Oracle9i Database Utilities for more information on: loading objects, LOBs, and Collections; and SQL*Loader case studies.

Figure 9-2 SQL*Loader: Direct-Path and Conventional Path Loads

Text description of the illustration sutil018.gif

LOB Performance Guidelines

• General Performance Guidelines
• Temporary LOB Performance Guidelines

General Performance Guidelines

Use the following guidelines to achieve maximum performance with LOBs:

• When Possible, Read/Write Large Data Chunks at a Time: Since LOBs are big, you can obtain the best performance by reading and writing large chunks of a LOB value at a time. This helps in several respects:
  1. If accessing the LOB from the client side and the client is at a different node than the server, large reads/writes reduce network overhead.
  2. If using the 'NOCACHE' option, each small read/write incurs an I/O. Reading/writing large quantities of data reduces the I/O.
  3. Writing to the LOB creates a new version of the LOB CHUNK. Therefore, writing small amounts at a time will incur the cost of a new version for each small write. If logging is on, the CHUNK is also stored in the redo log.
• Use LOB Buffering to Read/Write Small Chunks of Data: If you need to read/write small pieces of LOB data on the client, use LOB buffering -- see OCILobEnableBuffering(), OCILobDisableBuffering(), OCILobFlushBuffer(), OCILobWrite(), OCILobRead(). Basically, turn on LOB buffering before reading/writing small pieces of LOB data.

  See Also: Chapter 5, "Large Objects: Advanced Topics", "LOB Buffering Subsystem" for more information on LOB buffering.

• Use OCILobRead() and OCILobWrite() with Callback: So that data is streamed to and from the LOB. Ensure the length of the entire write is set in the 'amount' parameter on input. Whenever possible, read and write in multiples of the LOB chunk size.
• Use a Checkout/Checkin Model for LOBs: LOBs are optimized for the following operations:
  • SQL UPDATE which replaces the entire LOB value
  • Copy the entire LOB data to the client, modify the LOB data on the client side, copy the entire LOB data back to the database. This can be done using OCILobRead() and OCILobWrite() with streaming.
• Try to commit changes frequently.

  See Also: Chapter 7, "Modeling and Design", "Performance Attributes When Using SQL Semantics with LOBs" , for information about performance issues when using SQL semantics with LOBs

Some Performance Numbers

Table 9-1 lists the results of a performance test that enqueued 500 messages using a chunk size of 8KB for the LOB part of the payload. This performance test used Oracle8i Release 3 (8.1.7), and a DB_BLOCKSIZE = 8192 (8K), identical to the operating system block size.

Table 9-1 Response Time When Enqueueing 500 Messages With and Without CACHE and LOGGING

CHUNK SIZE	CACHE (Y/N)	LOGGING (Y/N)	MESSAGE_SIZE	RESPONSE TIME
8k	NOCACHE	NOLOGGING	3900 bytes	01:33 sec
8k	NOCACHE	NOLOGGING	4000 bytes	06:19 sec
8k	NOCACHE	NOLOGGING	4000 bytes	04:36 sec
8k	CACHE		3900 bytes	01:22 sec
8k	CACHE		4000 bytes	01:22 sec
8k	CACHE		4000 bytes	01:83 sec
8k	CACHE		20000 bytes	02:33 sec

Previous response times using a 16k chunksize, NOCACHE, and NOLOGGING for a message of 4000 bytes was 12:28 sec.

These results indicate that the CACHE parameter is the parameter giving the best performance improvement.

Temporary LOB Performance Guidelines

In addition to the guidelines described earlier 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 islarge 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 PL/SQL User's Guide and Reference, for more information on passing parameters by reference and 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 because, 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 procedures or 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; 
  
  

• Use OCI_OBJECT mode for temporary LOBs

  To improve the performance of temporary LOBs on LOB assignment, use OCI_OBJECT mode for OCILobLocatorAssign. In the OCI_OBJECT mode, Oracle 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.

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

Moving Data to LOBs in a Threaded Environment

Incorrect procedure

The following sequence, requires a new connection when using a threaded environment, adversely affects performance, and is inaccurate:

1. Create an empty (non-NULL) LOB
2. INSERT using the empty LOB
3. SELECT-FOR-UPDATE of the row just entered
4. Move data into the LOB
5. COMMIT. This releases the SELECT-FOR-UPDATE locks and makes the LOB data persistent.

The Correct Procedure

Hence the preferred procedure is as follows:

1. INSERT an empty LOB, RETURNING the LOB locator.
2. Move data into the LOB using this locator.
3. COMMIT. This releases the SELECT-FOR-UPDATE locks, and makes the LOB data persistent.

Alternatively, you can insert >4,000 byte of data directly for the LOB columns but not the LOB attributes.

Migrating from LONGs to LOBs

During migration from LONGs to LOBs, the redo changes for the table are logged only if the table has LOGGING on. Redo changes for the column being converted from LONG to LOB are logged only if the storage characteristics of the LOB indicate LOGGING. The default value for LOGGING|NOLOGGING for the LOB is inherited from the tablespace in which the LOB is being created.

Preventing Generation of Redo Space During Migration

To prevent generation of redo space during migration and migrate smoothly, use the following statements:

1. ALTER TABLE Long_tab NOLOGGING;
2. ALTER TABLE Long_tab MODIFY ( long_col CLOB [default <default_val>]) LOB (long_col) STORE AS (... NOLOGGING ...);
3. ALTER TABLE Long_tab MODIFY LOB long_col STORE AS (...LOGGING...);
4. ALTER TABLE Long_tab LOGGING;
5. Take a backup of the tablespaces containing the table and the LOB.

   See Also: Chapter 8, "Migrating From LONGs to LOBs"