Re: Tuning for CLOB usage

In article <Qomdnb-MzcRaV1bbnZ2dnUVZ8vmdnZ2d_at_bt.com>, 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.

Thanks.

-- 
jeremy