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.
