Re: Tuning for CLOB usage

On Aug 22, 3:34 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:

> "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.

Just a couple of additional notes here.

A LOB stored "off-row" will be stored in one single LOB per block, if the LOB is smaller than the block size for that tablespace.

If the LOB is larger, of course it will require more than one block.

But in "off row" storage of LOBs, there will not be more than one single LOB per block. Which can have implications in disk space capacity planning.

Think of it this way: LOBs migrate from "in row" when they are larger than 4K. If your db block size is 8k, the most common nowadays, you will NOT fit two LOBs > 4K in one 8k block.

And LOB storage does not span blocks unless the LOB itself is larger than one db block.

The above is valid and verified for CLOB and BLOB in 9i and 10g.

I don't know about 11g. Yet... ;-)
Perhaps Kevin Closson might have done some work on this subject when looking at the new storage options for unstructured data? Received on Wed Aug 22 2007 - 05:54:34 CDT