Re: Question on large objects

Please clarify an important detail: are the TABLE segments growing rapidly, or are the LOB segments associated with the tables growing rapidly? (Or is it both).
Can you give us a couple of examples of the individual segment space allocated BEFORE the redefinition compared to AFTER the redefinition. Can you check whether the LOBs are securefile LOBs (which they should be in any recent version of Oracle) or basicfile.

If the LOBs are declared to enable storage in row and the values are often small enough to stay in-row, but large enough that you can only get a couple of rows per table block then you may be seeing a lot of row chaining as parts of the row (including the in-row LOB(s)) are moved to other blocks. If the application is one of those that inserts a "short" row, then updates it to add further columns then that could also result in a lot of space wastage due to row chaining - especially if the added values are for the LOB columns.

Another variation on the theme of updates and overheads is about the way the LOB values are generated - if the application is trying to "save a text as a LOB" by appending it to an empty LOB one line at a time then once the LOB went out of row each extra line would require the current version to be copied with the extra line, leaving an older "read-consistent" copy behind.

If you are using securefiles you could adjust the "retention" setting in various ways:

retention none - means you don't keep ANY old versions (this may be dangerous if your users expect to run read-consistent LOB-focused queries)

retention max - means the size of the lob segment will be limited to whatever you've set in as the MAXSIZE in the storage() clause for the lob. When I last tested this I found that Oracle sometimes raise Oracle error ORA-60010 (a complaint about allocating space) for no obvious reasons, but this might have been a side effect of the size (small) of my experiment.

retention min - I've not tested this, but the manual says you can set the number of seconds a old copies will be kept; but to me "min" suggests this means "at least", not "and it will be reused/reusable immeditely after that time".

retention auto - again not tested (properly), but Oracle says it will decide based on time and space requirements. Presumably reusing space might take some extra , time, but if you've set a "reasonable" maximum file size for the LOB tablespaces Oracle might get sensible about limiting the number of old copies it keeps.

Regards
Jonathan Lewis

On Tue, 26 Sept 2023 at 23:01, yudhi s <learnerdatabase99_at_gmail.com> wrote:

> Hello Listers,
> We have some complaints coming from one of the third party databases which
> holds multiple large objects (CLOB/BLOB) in the underlying tables. And the
> size of those growing rapidly and the appdev team keep doing purging
> activity taking downtime. And also mentioned that the size is getting
> decreased significantly if they are doing the table reorg using
> dbms_redefinition method. Not sure if this is expected behaviour in case of
> tables holding large objects.
>
> Can it be because of the heavy DML, but then is it expected that CLOB/BLOB
> would be fragmented severely as compared to normal data types? Or is it
> because UNDO copies are getting stored in the same lob segment thus
> increasing the size of the LOB segments significantly. And any other
> possible easy way to handle such scenarios?
>
> Regards
> yudhi
>

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