Re: Big Update/DML

Hi,

with regards to CTAS it is very hard to believe it takes that long. I am pretty sure that there is something wrong. A sql monitor would be extremly helpfull.

Regards

Lothar

Am 27.08.2020 um 02:43 schrieb Sanjay Mishra (Redacted sender smishra_97 for DMARC):
> Sayan
>
> Update statement is
>
> Update snows.stamp_detail set set stamp_process_calc=processed_calc_amt;
>
> Tried to use
> 1. Parallel DML with 100 --> Taking 20+hrs
> 2. CTAS was tried using half a billion as well as 1 billion rows with
> parallel 50, 75,100 - Almost same result
> 3. CTAS with nologging using same step 2 but still not much improvement
>
> We have 5-10 such big table and so running each with this much
> time-frame need high downtime
> Tx
> Sanjay
>
> On Wednesday, August 26, 2020, 08:23:18 PM EDT, Sayan Malakshinov
> <xt.and.r_at_gmail.com> wrote:
>
>
> Hi Sanjay,
>
> It would be better if you provide more details about your update.
> Exact update statement would be helpful. is this column nullable/not null?
>
>
> On Thu, Aug 27, 2020 at 1:12 AM Sanjay Mishra
> <dmarc-noreply_at_freelists.org <mailto:dmarc-noreply_at_freelists.org>> wrote:
>
> Andy
>
> Yes look like is an option if we are doing work online and despite
> take more time but need not require downtime. In our case multiple
> DDL are running to existing environment due to Application upgrade
> and so all work has to be done with downtime. So challenge is
> reduce time of DML operations on big tables containing few
> billions rows.
>
> Tx
> Sanjay
>
> On Wednesday, August 26, 2020, 11:20:55 AM EDT, Andy Sayer
> <andysayer_at_gmail.com <mailto:andysayer_at_gmail.com>> wrote:
>
>
> It does sound like a virtual column could be the ideal solution.
> But if data needs to be physically stored or cannot be calculated
> deterministically at any point in time then Connor has a great
> demo of using dbms_redefinition to create a new table online with
> a function to map the new column. There’s obviously some overhead
> with context switching but it may be far better than some of the
> obstacles you might be facing at the moment:
> https://connor-mcdonald.com/2016/11/16/performing-a-large-correlated-update/
> (and you might be able to help it with pragma udf in the right
> circumstances).
>
> Obviously, how helpful this is depends where the work is currently
> going and how online this needs to be.
>
>
> Thanks,
> Andrew
>
> On Wed, 26 Aug 2020 at 16:00, Jonathan Lewis
> <jlewisoracle_at_gmail.com <mailto:jlewisoracle_at_gmail.com>> wrote:
>
>
> Is that 3-4 billion rows each, or total ?
>
> I would be a little suspicious of an update which populates a
> new column with a value derived from existing columns. What
> options might you have for declaring a virtual column instead
> - which you could index if needed.
>
> Be extremely cautious about calculating space requirements -
> if you're updating every row on old data might you find that
> you're causing a significant fraction of the rows in each
> block to migrate, and there's a peculiarity of bulk row
> migration that can effectively "waste" 25% of the space in
> every block that becomes the target of a migrated row.
>
> This effects can be MUCH work when the table is compress (even
> for OLTP) since the update has to decompress the row before
> updating and then only "re-compresses" intermittently as the
> block becomes full.  The CPU cost can be horrendous and you
> still have the problem of migration if the addition means the
> original rows can no longer fit in the block.
>
> If it is necessary to add the column you may want to review
> "alter table move online" can do in the latest versions (in
> case you can make it add the column as you move) or review the
> options for dbms_redefinition - maybe running several
> redefinitions concurrently rather than trying to do any
> parallel update to any single table.
>
> Regards
> Jonathan Lewis
>
>
>
>
>
>
>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org

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