Re: Tuning 'Alter Modify" column

Are there triggers on the table which may be doing some index operation and thus responsible for the 'cell singleblock read' waits?

On Thu, 25 Aug 2022, 11:59 am 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.
>
> 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_at_gmail.com>
> 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
>>
>> -- http://www.freelists.org/webpage/oracle-l
>
>

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