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
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
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
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
- 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
- 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
- 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 -
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
- 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.
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
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.