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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxx> 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.
