Re: question about progress of shrink operation

A warning about testing - make sure the database you're testing on is running in archivelog mode, with force logging enabled (which I assume is the case for your production database). If you don't do this then any performance measures for timing or I/O could be unrealistc. NOCACHE means NOLOGGING by default, so you won't record any redo for the writes of the LOB values that move. In production I think you would want the LOBs to be written to the archivelog or you MIGHT end up in a position where a load of them are unrecoverable on a crash (hence the comment about force logging for, at least, the tablespace)

I've just done a small test that moved 3,000 small LOBs with your definition: total redo 7MB; enable archivelog mode and force logging and total redo 35MB (7MB + 3,000 x 8KB approximately for the single block LOB values) plus read and write to archivelog.

N.B. It may make no difference, but it might be worth testing the effect of creating a RECYCLE cache of 2% to 5% percent of the buffer cache, assigning each LOB in turn to that cache, then modifying the LOB storage clause to CACHE (so that it's automatically cached).

Regards
Jonathan Lewis

On Mon, 29 Jan 2024 at 06:58, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> THank you for your answers.
>
> It is: STORE AS BASICAFILE, ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE
>
> Indeed I observe the operation itself will start a transaction the moment
> the command is executed and subsequently there are a very large number of
> small transactions done.
>
> It seems like the progress is not lost based on tests, but I haven`t found
> this aspect properly documented.
>
>
>
> În sâm., 27 ian. 2024 la 20:43, Jonathan Lewis <jlewisoracle_at_gmail.com> a
> scris:
>
>> A question I forgot to ask - are your LOBs securefile or basicfile, and
>> what are your logging, caching, and "storage in row" settings. Again, it
>> might make a difference.
>> Regards
>> Jonathan Lewis
>>
>>
>> On Fri, 26 Jan 2024 at 08:42, Laurentiu Oprea <
>> laurentiu.oprea06_at_gmail.com> wrote:
>>
>>> Dear all,
>>>
>>> What will happen with progress of a shrink operation of a CLOB if it
>>> fails due to various reasons like for example undo space. Will it be all
>>> lost or partially lost or no lost progress?
>>>
>>> THank you.
>>>
>>

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