Re: Oracle Securefile Lobs

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 24 Feb 2020 22:35:39 +0000
Message-ID: <LNXP265MB1562FB604778F35329D856F7A5EC0_at_LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM>

If you update a specific LOB value several times over a short interval of time then you could easily end up with several aged copies of that LOB value since LOB segments don't use the standard undo mechanism, instead Oracle creates a new copy of the value and keeps track of the SCN at which the old copy was marked for deletion. This means that you can find some applications where a LOB segment is several times larger than it ought to be (and some people would use the term "fragmented" to describe this state,) You might want to rebuild the LOB segment from time to time, but if you can use partitioning by time then it's probably nicest to do the rebuild only once after the data in it has got to a state of no further change. (Generally "pack it well when it's about to be made read-only" is a nice strategy to aim for in all cases).

Wasted space won't necessary result in bad performance in LOBs, but there have been several bugs with space management where Oracle spends too much time checking whether or not some space is ready for re-use. Others may be able to give you specific up to date cases for your version of Oracle.

On the other hand, I have seen systems where a LOB value gets updated 30 or 40 times in the space of a few minutes, and each update generates a whole new LOB value, so a transaction that might generate 30 redo entries of 200 bytes and end up writing one or two Oracle blocks actually writes 30 or 40 blocks - one each time it modifies that LOB value. And that's a design that's inherently a bad performer.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Sanjay Mishra <dmarc-noreply_at_freelists.org> Sent: 24 February 2020 22:18
To: Oracle-L Freelists
Subject: Oracle Securefile Lobs

I had one application that stores lots of transactional data in LOB and the same is updated heavily. Does frequent update on LOB

1. Impact system performance heavily
2. Can cause lots of Fragmentation
3. Need any frequent maintenance as part of optimization

The database is 18c and LOBS are secure files and Partitioned.

TIA
Sanjay

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 24 2020 - 23:35:39 CET