Re: Tuning 'Alter Modify" column
Date: Tue, 23 Aug 2022 21:23:36 +0000
Message-ID: <CO6P220MB06103AFF5CF167349FD46F1BFF709_at_CO6P220MB0610.NAMP220.PROD.OUTLOOK.COM>
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.
HTH
Get Outlook for iOS<https://aka.ms/o0ukef>
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<mailto: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
Until you rename newTable your current_table can be used.
Karth
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
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<mailto: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<mailto: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<mailto: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-lReceived on Tue Aug 23 2022 - 23:23:36 CEST