Re: Tuning 'Alter Modify" column
Date: Thu, 25 Aug 2022 15:42:38 +0530
Message-ID: <CAEzWdqcn4kBYcgmw-D4J6UWP0aW6_av6yeoDyvYGo+pDzPP5og_at_mail.gmail.com>
> 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-lReceived on Thu Aug 25 2022 - 12:12:38 CEST