RE: Adding new column for large table with online redefinition

From: Prabhu Krishnaswamy <prabhu_adam_at_hotmail.com>
Date: Fri, 30 May 2014 23:03:29 +0000
Message-ID: <BAY173-W17C5151C85453EB33A7ABC85270_at_phx.gbl>

Hi Mike,
The system is a bi-directional replication environment, hence, we written a block which opens a cursor and does the batch commit for every 10000 rows. For merge we did the same with every 50000 rows. We found the execution plan for the cursor SQL,update and merge statement are going through the index scan and one of the top most wait event was enq: TM-contention. Hence, we have dropped all the un indexed FKs referring this table. ThanksPrabhu

From: mcunningham_at_thedoctors.com
To: prabhu_adam_at_hotmail.com; oracle-l_at_freelists.org Date: Fri, 30 May 2014 14:55:38 -0700
Subject: RE: Adding new column for large table with online redefinition

Hi Prabhu, in my opinion 60+ million rows does not warrant 70 or 58 hours for the update. I’m concerned at that amount of time and wonder if the update is being performed with full scans. I know this does not answer your question about online redefinition and I have not tried redef for column addition so I am not able to offer help in that area. However, my first thought would be to look at why this is taking so long. Can you offer any further info such as execution plan and waits that you are experiencing during the update/merge? Michael Cunningham Senior Database Administrator
The Doctors' Company

707.226.0221 - desk
707.337.0184 - cell  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Prabhu Krishnaswamy

Sent: Friday, May 30, 2014 12:07 PM
To: oracle-l_at_freelists.org
Subject: Adding new column for large table with online redefinition Lists We are adding a new column for a real large table (60+ million rows) and will update the column value from another table based on a matching condition. With normal update and merge statement, its taking around 70 and 58 hours respectively. We are exploring an option using online redefinition method. Is it possible to using the SQL on the column mapping string. Any suggestions or insights will be much helpful for us. Thanks for you help. ThanksPrabhu

Confidentiality Notice: This message and any attachments hereto may contain confidential and privileged communications or information and/or attorney client communications or work-product protected by law. The information contained herein is transmitted for the sole use of the intended recipient(s). If you are not the intended recipient or designated agent of the recipient of such information, you are hereby notified that any use, dissemination, copying or retention of this e-mail or the information contained herein is strictly prohibited and may subject you to penalties under federal and/or state law. If you received this e-mail in error, please notify the sender immediately and permanently delete this e-mail.

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

Received on Sat May 31 2014 - 01:03:29 CEST