RE: Tuning 'Alter Modify" column

AND (not but) there a some things you can do to minimize undo side effects from undo:  

1. Force a non-PGA scan of all the blocks in the table so that delayed block cleanouts become permanent
2. Immediately before the start of the transaction (in this case the ALTER) switch to a large fresh tablespace for UNDO and then as soon as your transaction starts switch back to your usual UNDO.
3. Avoid long running queries on this table that may cause a treadmill construction of current versions of blocks with respect to the query start time.

Regarding #2, I have been warned by Oracle this is NOT in their regression tests. That was probably the same month they introduced the feature of switching UNDO online. Neither I nor anyone taking my advice to try this has ever been able to cause an error by doing this. And yes, it is possible some other transactions may start during the time the alternate “private batch” UNDO is active, so those transactions will also be in the “private batch” UNDO. That is irrelevant unless they are big transactions, which can be avoided with a tiny bit of planning.  

Regarding row chaining, if row chaining is sufficient to be a problem, checking whether that can be fixed before the whole table operation is done. (IF you have too many columns for a single row piece it may not be possible, for example.)  

Good luck.  

PS: Does it really need to be char instead of varchar?  

PPS: I may have missed this skimming the thread: is there an index containing the column being modified? That would be like trying to teach a pig to sing.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Thursday, August 25, 2022 6:17 AM
To: Oracle L
Subject: Re: Tuning 'Alter Modify" column  

There are two common reasons why you might see cell single block reads - one is because you have migrated (or chained) rows and the migrated row (or any chained pieces) has to be fetched through a single block read - I think as far as migrated rows are concerned this behaviour is not identical to what you'd get with a simple select full tablescan.  

The second reason is that your session is having to do reads on undo segments to handle delayed block cleanout or calculations of upper bound commits to ensure that it is looking at the correct version of the data.  

One of the things that you always ought to do when executing, or testing, large scale operations like this is to capture diagnostic information about the session - in particular you could capture the changes in stats in v$sesstat for the session as it runs. If the problem is about migrated rows you'll see a statistic "table fetch continued row" constantly increasing; if it's about cleanout or commit time you see stats about "xxxx - undo records applied" increasing.    

Regards

Jonathan Lewis        

On Thu, 25 Aug 2022 at 07:29, yudhi s <learnerdatabase99_at_gmail.com> wrote:

Thank you Mladen. Actually I saw throughout the 4hrs of Alter query execution the current_obj# was pointing to the table but not indexes, but still the event it was noting was 'cell singleblock physical read' , longops was showing full tablescan which looks odd to me.

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