Re: question about progress of shrink operation

I've done a couple of very small tests on 23c with securefile shrink - not sure I like the results.
10,000 single-block LOBs, delete 1,000, then shrink, why does Oracle get through
5 Gigabytes of logical I/O
400,000 db block gets
36,000 "lock row piece"
36,000 "update row piece"

Why, after deleting 8,000 out of 10,000 rows with the single block LOB do I see 2,300 used blocks and 9,000 expired blocks, and no shrinkage in space? (The number of transactions, though was only 1,600 - so in this case was not the one LOB = one transaction of the basicfile example doing the same delete/shrink.

I think this probably needs a more realistic set of tests, though; in particular with a very much larger volume, a much longer duration, and a large number of small deletes / updates to avoid strange side effects of the retention settings on a small, quick test.

Regards
Jonathan Lewis

On Sat, 27 Jan 2024 at 23:14, Martin Berger <martin.a.berger_at_gmail.com> wrote:

> shrinking securefiles are a new feature in 23c
> for anyone interested, please have a look at
> DBMS_SPACE.SECUREFILE_SHRINK_MANUAL
> or DBMS_ADBTASK_ADMIN.RUN_TASK_MANUAL('Auto SF Shrink');
> (you might find a job ORA$_ATSK_AUTOSFSHK)
>
> I had no chance to make a reason out of if yet, so that's all I can
> provide; sorry!
>
> Martin
>
>
> Am Sa., 27. Jan. 2024 um 21:09 Uhr schrieb Mark W. Farnham <mwf_at_rsiz.com>:
>
>> +42.
>>
>>
>>
>> Likewise if conditions are correct for you to copy to a new location to
>> effectively “shrink” rather than making shrink work, a reasonable amount of
>> work should go into the optimization of the details about your LOB storage
>> in the destination. The storage choices should not affect the ability to
>> copy from your current source to a new destination, but they may make a
>> significant change in the storage footprint in the destination.
>>
>>
>>
>> IF memory serves and IF a substantial number of your lobs should no
>> longer be logically changed, things effectively like pctfree for “regular”
>> block storage might be set very small for the copy in of those now
>> unmutable lobs, and then be set optimally for change of your still mutable
>> lobs before they are copied in. Don’t trust my memory on this, especially
>> for more recent releases. A change from basicfile in the original to
>> securefile in the destination may well be more important than anything else
>> if the current lobs are basicfile.
>>
>>
>>
>> Notice in particular than zero of what I have written changes or
>> challenges what JL has written about getting shrink to work at all.
>>
>>
>>
>> mwf
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jonathan Lewis
>> *Sent:* Saturday, January 27, 2024 1:43 PM
>> *To:* ORACLE-L (oracle-l_at_freelists.org)
>> *Subject:* Re: question about progress of shrink operation
>>
>>
>>
>> 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.
>>
>>
>
> --
> Martin Berger Oracle ♠
> martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
> ^∆x http://berxblog.blogspot.com
>

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