Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: [Q] wait time /lob def

Re: [Q] wait time /lob def

From: Tanel Poder <>
Date: Thu, 29 Jan 2004 17:19:26 -0800
Message-ID: <>

One more similar reason for using different block sizes for different LOBs is that a LOB item always allocates space in chunks for it and a chunk can be a multiple of blocksize, not less. Big blocks mean space wastage for lots of out of line small LOBs, however small blocks mean performance degradation due fragmentation and LOB index growth for large LOBs.
This situation can be relieved by having tablespaces with different blocksizes for different LOBs.
----- Original Message -----
To: Multiple recipients of list ORACLE-L
Sent: Friday, January 30, 2004 1:04 AM
Subject: Re: [Q] wait time /lob def

That is an interesting use of an alternate block size Jonathan.

"Jonathan Lewis" <>
Sent by:

 01/29/2004 09:59 AM
 Please respond to ORACLE-L

        To:        Multiple recipients of list ORACLE-L <>
        Subject:        Re: [Q] wait time /lob def

There is a note in one of the manuals about nologging
lobs (or nocache lob, I forget which) that points out
that the "unrecoverable SCN" for file that holds the
LOB has to be updated in the control file whenever the
LOB is updated.

If you actually have a performance problem because of
this - i.e. if lots of people/processes are running slowly
because they are waiting on control file writes - then
you might want to make the LOB cache/loggong.  But
control file writes are not inherently a bad thing to be
blocked.   Of course, if the LOBs are quite large, then
the time taken to write the LOB may be far greater
than the time taken to update the controlfile - which
would make any concerns about the controlfile update
irrelevant. So there is no 'obvious' correct answer to
your question, without examing your current activity.

The note (which I think Steve Adams' also has on his
website) mentions an event that can be set to stop the
controlfile update when the LOB is updated.  This may
not be a good idea, though, as it may affect some
aspects of recoverability.

If you do make the LOB 'cached', then remember that
all reads and writes go through the db_block_buffer,
which could affect all the other I/O activity adversely,
so you might want to consider putting the LOBs into
a tablespace with a non-standard block size so that
the LOB activity doesn't affect the rest of the cache.
(You do also have the option in more recent versions
of refining the caching properties so the LOB can be
readcache only, writecache only or read/write cache
or nocache, I believe).


Jonathan Lewis

Please see the official ORACLE-L FAQ:
Author: Tanel Poder

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Jan 29 2004 - 19:19:26 CST

Original text of this message