Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning for CLOB usage

Re: Tuning for CLOB usage

From: Jeremy <>
Date: Thu, 23 Aug 2007 18:24:05 +0100
Message-ID: <>

In article <>, Jonathan Lewis says...

> Start by tracing a few sessions, or running statspack
> snapshots at level 7 for a while, to see where the time
> is going.
> If most of the time spent is used for I/O relating to LOBs
> then you can start thinking about the best way to handle
> LOBs for your requirement. Ditto if the segment statistics
> show most of your I/O to be on the LOB segments.
> General thoughts -
> should your lobs be allowed "in row", or should
> you always disable "in row"
> should your blobs be using cache or not
> is there an optimum block size for your lob handling
> are you seeing side effects of multi-byte character sets.
> "Disable storage in row" is often a good idea, and CACHE
> can make a dramatic difference . But if you CACHE, then
> you probably need to have a separate cache for the LOBs -
> either by using the KEEP or RECYCLE pools, but possibly
> by using a different block size for the LOB segment so that
> most or the most popular LOBs fit in one block. If the LOBs
> don't go through the cache, and are logged, then Oracle uses
> direct path reads and writes on them - which become directly
> visible as wait states to the end-user - and logs whole blocks
> to the redo log.
> For similar reasons to the previous, if you use temporary LOBs
> (dbms_lob.create_temporary) then remember to think about the
> "cache => true" option.
> Remember that if the CLOB uses a multibyte character set
> it will be translated internally to a fixed two-byte character
> set - so the length() or dbms_lob.getlength() calls, that report
> the character count, will be out by a factor of two when it
> comes to counting bytes.

Really grateful for this contribution, as mentioned we have worked around the dependency on CLOBs except where they are absolutely essential. I have taken your (and everyone else's) comments on board for when we make greater use of LOBs in general.


Received on Thu Aug 23 2007 - 12:24:05 CDT

Original text of this message