Re: LOB objects

From: Nuno Souto <>
Date: Sun, 20 Mar 2011 21:42:25 +1100
Message-ID: <>

Just one more addition:

With LOBs, the undo segments are kept in the same tablespace as the LOB. At least all the way to 10gr2 and I suspect it's the same in 11g although I haven't checked.
So if you plan to do lots of updates, get ready for heavy I/O on that tablespace! In one particularly bad case, we actually changed from using an update to a delete followed by an insert: it was faster! But that was in 9i and things might have improved now.

Nuno Souto
in wet Sydney, Australia wrote,on my timestamp of 18/03/2011 5:28 AM:

> Hi,
> Some weeks ago I have discussed lob's connection with flashback database
> feature. Read metalink "Flashback Database Best Practices& Performance
> [ID 565535.1]"
> Randolf Geist advised me another MOS document "LOBS - Storage, Redo and
> Performance Issues [ID 66431.1]]
> My personal point of view is this:
> LOB's intensively used as "just bigger varchar2's" with default settings
> can(and did) pose performance problems. Looks like LOB's were designed to
> hold large amounts of data:
> - they are not cached by default. That has consequences.
> - they are not generally stored inline. There exists index structure to
> access them. That has consequences too.
> - they use direct read/write (if not cached.) Again, there are
> consequences. My believe is that this is exactly what causes pressure
> on flashback writer (if flashback is enabled in database.) Haven't had a
> time to test some assumptions how it functions but "Flashback Database Best
> Practices& Performance [ID 565535.1]" points out specifically that LOB
> caching is good for flashback writed and yes, it did help us.
> Basically, for me LOB's are evil if used in tables subject to high
> transactions volume.
> From:<>
> Can anyone point me to some practical pages or experience working with
> CLOBs. We have some databases that are using CLOBs, and will be using
> them more.
> So I am interested it things like Ďdo they grow foreverí i.e. deletions
> really donít happen physically, (if that is so, what to do about it).
> Tuning: If that is necessary, all aspects, gothas. Things I could or
> should do?
Received on Sun Mar 20 2011 - 05:42:25 CDT

Original text of this message