Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








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.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.