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