RE: question about progress of shrink operation

When addressing shrink, there are also useful questions to determine whether a goal of making shrink work is the actual goal. My null hypothesis for that is that probably the actual goal is to use less storage without negatively affecting performance (and likely improving performance).  

One of those questions is whether (or not) some large predicate identifiable fraction of the existing data is immune from updates taking place. The simplest case of this is if there is either a relevant date column or status column meaning that the row should not logically be updated in the future.  

That is worth a thought. IF there is some large predicate identifiable fraction of the existing data that can be definitely excluded from concurrent updates, you are very likely better off copying the now quiescent data somewhere else at your operational leisure and copying the non-quiescent data later in an update controlled window.  

Optimization for the future use of the quiescent data (both for storage and for performance) is usually pretty simple. Even just reducing the pctfree to something like zero or close to zero (and altering it back to whatever is most useful before copying the non-quiescent data) can save a lot of space.  

If a predominantly used index can be identified that is not counter ordered worse than any existing cluster factor for other indexes, then ordering the quiescent data into the new place in order (or at least block sized chunks in order) can improve the CBOs impression of that index and likely permanently shrink indexes in that order. (Except for laboratory tests to demonstrate counter ordering [which IS a true possibility] I have never seen the resulting cluster factor of any index be degraded to be worse than the current cluster factor other than monotonically increasing unique id columns which are typically used infrequently for other than uniqueness constraint support, where cluster factor is irrelevant for looking up a single row key value.)  

There are many other things, most of which the attribute ordering feature of non-dinosaur-aged releases can give you for free.  

And, of course, if you CAN move to partitioning, you can move to a “Scaling to Infinity” strategy, so you should consider that.  

If you CANNOT move to partitioning, you may still be able to attain manageable chunk size for future maintenance by creating union all view of a set of tables and inserting the quiescent data into a new table or tables and then the non-quiescent data into a different table. (To be useful without a lot of other engineering which you probably do not want to do, this strategy requires that the data at insert time has some key that will never be changed in a way that would require the row to be moved to a different table [which is the equivalent engineering to make partitioning with row movement reliable.])  

Sigh. It is a lot to think about.  

Good luck. Shrink is the simplest thing to do. I can’t remember whether Oracle ever un-deprecated creating a giant UNDO, altering it active, starting your monolithic transaction and then quickly altering back to your usual UNDO. IF you can time that so only your shrink (or other monolithic transaction) is the only one started in this special UNDO, it changes several operational factors in your favor for the monolithic transaction.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Laurentiu Oprea Sent: Friday, January 26, 2024 4:52 AM
Cc: ORACLE-L (oracle-l_at_freelists.org) Subject: Re: question about progress of shrink operation  

Apologies I missed these details: so is about a big non-partitioned table (around 5TB - table + 3 CLOBS). The version is 19.20. Plan is to shrink each clob individually then run shrink at table level. Is kind of hard to estimate the undo requirements and the first trial failed due to lack of UNDO space after a few days of running .    

În vin., 26 ian. 2024 la 11:31, Jonathan Lewis <jlewisoracle_at_gmail.com> a scris:

It would be a good idea to specify which version(s) of Oracle you want to do this on (and whether the tables are (composite) partitioned) ; it's possible that the correct answer for a newer version of Oracle is wrong for an older version. I have a few notes about ridiculous problems with the shrink space command for LOBs (on "very old" versions) so I'd test every time I needed to try it on a newer version.    

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