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.
This same event is showing up even i run the alter query by making the
index unusable which is on that altered column. ( Though there were other
indexes present in usable state on that table on other columns, which i
believe should not affect this alter.). With index unusable it's finishing
in 1.5hrs though
So wondering if this 'Alter' is some special case in which fullscan is
happening using 'cell singleblock physical read' and which is why it's slow
as compared to a normal FTS on that 24gb table. (Note- Even with exact
index unusable it's taking around 1.5hrs vs with indexes it was running for
around 5hrs.)
On Thu, 25 Aug 2022, 7:17 am Mladen Gogala, <gogala.mladen@xxxxxxxxx> wrote:
On 8/24/22 02:46, yudhi s wrote:
And also during the table scan i was seeing event 'cell singleblock
physical read'
Single block physical read is usually an index read. You cannot have index
read if you want to do it fast. You need to find out where are those index
reads coming from.
Regards
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217https://dbwhisperer.wordpress.com
-- //www.freelists.org/webpage/oracle-l
