Re: Tuning 'Alter Modify" column

Thank you.
Considering the size of table i.e 25gb 120Million rows and possible downtime, I think both the option of ctas + truncate + partition exchange also the one you highlighted i e creating new table altogether is possible.

What I was trying to understand is why during that Alter query execution, sudden rollback happen post 80% completion of tablescan and also that rollback ran for hours without finish. We endup killing the session. And also during the table scan i was seeing event 'cell singleblock physical read'. It was running too slow considering its an exadata system. I was expecting 'cell multiblock physical read' or 'cell smartscan'.

On Wed, 24 Aug 2022, 2:53 am Karthikeyan Panchanathan, <keyantech_at_gmail.com> wrote:

> Have you tried
>
> Create table newTable as select col1,….,cast(colchar8name as char(9))
> colchar9
> from current_table;
>
> CTAS might faster without index on new table.
> Further adding no logging to make it fast.
>
> Finally rename table and rebuild index.
> Until you rename newTable your current_table can be used.
>
> HTH
> Karth
>
>
>
> Get Outlook for iOS <https://aka.ms/o0ukef>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Pap <oracle.developer35_at_gmail.com>
> *Sent:* Tuesday, August 23, 2022 4:36:46 PM
> *To:* learnerdatabase99_at_gmail.com <learnerdatabase99_at_gmail.com>
> *Cc:* Jonathan Lewis <jlewisoracle_at_gmail.com>; Oracle L <
> oracle-l_at_freelists.org>
> *Subject:* Re: Tuning 'Alter Modify" column
>
> Also I think, in the partition exchange method you may need to truncate
> the main table and modify/increase the column length to char 9 before
> exchange , else exchange partition operation will mostly fail because of
> structure/column length mismatch.
>
> On Wed, 24 Aug 2022, 1:13 am yudhi s, <learnerdatabase99_at_gmail.com> wrote:
>
> Thank you so much Jonathan. You explained it so nicely. This really helped.
>
> We will give it a try by making the index unusable or through the
> partition exchange method. Its legacy app so making it Varchar2 will
> possibly need some impact analysis.
>
> However, while the Alter query was running along with a valid index on the
> same column (which was getting altered)we were monitoring tablescan
> operation through longops. We saw after ~80% of the table scan done i.e
> around ~4hrs past the execution, the longops suddenly disappeared. And the
> session started showing continuously increasing value of "rollback changes
> - undo records applied". It means the session was starting to rollback. But
> we were trying to understand if it's really the effect of the "Update
> restarts" bug which you pointed out? As the default session trace generated
> shows 'TIMEOUT' so I'm wondering if we hit something else. Below is a
> snippet of the trace couple of lines above and below the 'alter' statement.
>
> user session for deadlock lock 0x4220165d0
>
> sid: 2085 ser: 65435 audsid: 836497845 user: 428/USER1
>
> flags: (0x145) USR/- flags2: (0x4009) DDLT1/-/INC
>
> flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
>
> pid: 323 O/S info: user: grid, term: UNKNOWN, ospid: 187203
>
> image: oracle_at_mchn06.city1.comp1.com
>
> client details:
>
> O/S info: user: XXXXX, term: , ospid: 105399
>
> machine: oxxxxx1 program: sqlplus_at_oxxxxxx1 (TNS V1-V3)
>
> application name: SQL*Plus, hash value=3669949024
>
> current SQL:
>
> Alter Table tab1 Modify COL1 CHAR(9 BYTE)
>
> 2022-08-23 08:55:37.738*:kjdglblkrdmpint(): DUMP LOCAL BLOCKER: initiate
> state dump for TIMEOUT
>
> possible owner[323.187203] on resource
> LB-0EACC583-109A9AD2-00000000-00000000
>
> 2022-08-23 08:55:37.738 :kjzddmp(): Submitting asynchronized dump request
> [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
>
> *** 2022-08-23T08:55:44.108570-04:00
>
> ERROR: KGL Lock timeout. Handle=0x872c70138
>
> LibraryObjectLock: Address=0x87bf0b7d8 Handle=0x872c70138 RequestMode=S
>
> CanBeBrokenCount=9652 Incarnation=3 ExecutionCount=0
>
> On Tue, Aug 23, 2022 at 4:17 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>
> Because you're using a char() column (rather than varchar2() Oracle has to
> update every row that has a value (i.e. is not null) by appending a space
> to it, and it also has to update every index that references that column
> doing a "delete/insert".
>
> The update will be by tablescan, in a single transaction, so you will
> acquire a HUGE undo segment. The redo generated will be one entry of about
> 280 bytes per row updated IF THERE ARE NO INDEXES; plus you might find that
> some rows migrate but that's a little unlikely unless the rows are all very
> short to start with anyway.
>
> For every index that includes the column you're likely to generate a
> bigger problem. Every "updated" index entry will be an insert/delete pair,
> generating at least 450 bytes between them (plus 2 x the length of a
> typical index entry). Moreover, because it's a single transaction and
> Oracle doesn't cleanout, or even try to "stub" index entries until the
> transaction has committed (and even then some other session has to do the
> cleanout) you will basically double the size of the index and probably
> generate more redo on index leaf block splits - which generate about 18KB
> of redo per block split if you're using 8KB blocks.
>
> A further time-waster is that Oracle has a funny "restart" mechanism the
> first time the undo segment its using needs to add an extent (see:
> https://jonathanlewis.wordpress.com/2019/09/10/update-restarts/ and its
> comments). At the scale you're operating your modify is likely to work for
> a few million rows than roll back and start again.
>
> Given the huge amount of redo and the catastrophic impact on the indexes
> options are:
> a) try dropping any affected indexes before you do the modify - this MAY
> help.
> or
> b) use dbms_redefinition to make the change if you need it to be online
> or
> c) Create an empty single-partition partitioned table matching the
> definition. Insert /*+ append */ from old to new. Create local indexes
> matching your current indexes on the partitioned table. Exchange partition
> with table. This means you only copy the table once (and minimise the
> redo) and don't lose privileges etc.- but there are some little details you
> would want to investigate and adjust around the edges of this strategy.
>
> Regards
> Jonathan Lewis
>
>
>
>
>
>
> On Tue, 23 Aug 2022 at 10:35, yudhi s <learnerdatabase99_at_gmail.com> wrote:
>
> Hi All, We have got an ALTER table modify column column in which we are
> increasing the length of a column from char(8 byte) to char(9 bytes) as
> below. The table holds ~100million rows. We are seeing in the long ops its
> showing to take 4-5hrs of time and the message showing up as 'Table Scan'.
> I think one way is to move the data to another table and then truncate this
> table and modify the column and then move the table back using INSERT
> APPEND.But we were trying to understand if there exists any other less
> impact and faster approach like making it happen in parallel threads etc. I
> tried making the table Degree to parallel-16, buty the ALTER is still
> running in serial.
>
> alter table tab1 modify COL1 CHAR(9 BYTE);
>
> Regards
> Yudhi
>
>

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