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@xxxxxxxxx> 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
