logging with LOB, BLOB, CLOB Oracle Database Tips by Donald Burleson

Question:  I enabled LOGGING on a tablespace with large objects (LOB, like CLOB and BLOB) and I'm experiencing a huge amount of redo now.

Answer: It is expected that you would greatly increase redo log generation if you are logging large objects, and we must also remember that the blocksize and the size of the LOB (i.e. CLOB, BLOB) will effect performance and redo generation.  BLOB and CLOB objects follow the same row chaining and row migration rules as any other object and this effects redo. 

This Oracle document titled "LOB performance Guidelines" suggests that it might be a good idea to make the LOB tablespace blocksize at least as large as the average LOB, to minimize I/O

"If you store a 3900 byte LOB in a row with 2K block size then the row will be chained across two or more blocks.

Both REDO and UNDO are written for in-line LOBs as they are part of the normal row data. The CHUNK option does not affect in-line LOBs"

Oracle recommends LOGGING for LOB tablespaces

The Oracle recommendation is to use NOLOGGING only when doing bulk loads or migrating from LONG to LOB, followed by backup after all bulk operations:

LOGGING: enables logging of LOB data changes to the redo logs.  NOLOGGING: changes to LOB data (stored in LOBSEGMENTs) are not logged into the redo logs, however in-line LOB changes are still logged as normal.   Points to Note:

• The CACHE option implicitly enables LOGGING.
   
• If NOLOGGING was set, and if you have to recover the database, then sections of the LOBSEGMENT will be marked as corrupt during recovery (LOBINDEX changes are logged to redo logs and are recovered, but the corresponding LOBSEGMENTs are not logged for recovery)
   
• LOGGING|NOLOGGING can be altered. The NOCACHE option isrequired to turn off LOGGING, e.g. (NOCACHE NOLOGGING).

Because Oracle incurs the overhead of logging with BLOB's, we can expect higher redo log volume problem. There are several possible ways to reduce this I/O, but it depends on the details of your database:

• Store BLOB's outside Oracle - You know that the BLOB's never change. If so, consider storing them outside the database with the BFILE option.
   
• Increase default blocksize - Find the average and maximum LOB size, and move all datafiles into a larger blocksize, larger than the average LOB size. This will reduce multi-block writes to redo, but it's a large effort that required a complete database reorganization.
   
• Increase log buffer size - Try a super-large log_buffer size, to reduce overhead and log switches. This is contrary to Oracle recommended maximum of 2 meg, but we must get around the issue of multiple block writes for a single block with a chained or migrated BLOB column.  Also note that the newest TPC benchmark tests use a huge log_buffer and Oracle Applications 11i, recommends a log_buffer size of 10 megabytes for Oracle Applications.
   
• Change your archiving procedures - If your archived redo is "moved" to another location, install an OS script to "force" a fast-enough move. A 40 gig archived redo directly is very small for a high volume of redo. Get the Oracle sizing spreadsheets and run the numbers.  You can run scripts to monitor the archived redo log directory and send an alert when it's over 80% full.

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.