Re: Big Update/DML
Date: Thu, 27 Aug 2020 19:16:20 +0200
Message-ID: <f501f7f5-63f9-dd1a-32bd-09e6ec3cf473_at_bluewin.ch>
Hi,
Runtime is 1:45 hours not 20??
But 1:45 seems still too long.
- Insert is direct parallel
- Work distribution is about even
- Statement scales on CPU
So that is ok.
My gut feeling is that CPUs are not delivering.
Could be wrong of course, because I am missing many details. (E.g. how
many LOBS are in that table).
- How many cores do you have that can work on this task?
- What kind of cores (old sparcs?)
- What is your compression?
I would run the CTAS without compression. Even if you want the result compressed, you might gain insight.
Regards
Lothar
Am 27.08.2020 um 18:45 schrieb Sanjay Mishra:
> Clay
>
> Thanks for the update. Regarding table, all work for update/CTAS is
> tried only after been refreshed with EXPDP/IMPDP from production to
> test the timeline. I shared the SQL Monitor report in the last email
> and here it is attached again.
>
>
> Sanjay
> On Thursday, August 27, 2020, 12:09:19 PM EDT, Clay Jackson (cjackson)
> <clay.jackson_at_quest.com> wrote:
>
>
> What Lothar said – I’d look at the plan for the CTAS to be sure the
> optimizer isn’t doing something “unusual”, AND, consider the
> possibility that the table is already horribly “row chained” so that
> each read is actually reading several “random” (or worse) blocks.
> More data is clearly key to understanding.
>
> Clay Jackson
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>
> *On Behalf Of *Lothar Flatz
> *Sent:* Thursday, August 27, 2020 3:41 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* Re: Big Update/DML
>
> *CAUTION:*This email originated from outside of the organization. Do
> not follow guidance, click links, or open attachments unless you
> recognize the sender and know the content is safe.
>
> 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> <mailto: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/
> <https://nam05.safelinks.protection.outlook.com/?url=https%3A%2F%2Fconnor-mcdonald.com%2F2016%2F11%2F16%2Fperforming-a-large-correlated-update%2F&data=02%7C01%7Cclay.jackson%40quest.com%7Cd7187791c566418711db08d84a75d598%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637341217248530635&sdata=MAHM40uGfFgjYUJGv%2FpEUo7KrT9NgPZvzpF3zASZqTc%3D&reserved=0>
> (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
> <https://nam05.safelinks.protection.outlook.com/?url=http%3A%2F%2Forasql.org%2F&data=02%7C01%7Cclay.jackson%40quest.com%7Cd7187791c566418711db08d84a75d598%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637341217248540625&sdata=TOD3zIOdZDwp45R4ptmja9tAFNb3DwpnToKef0HMQpI%3D&reserved=0>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 27 2020 - 19:16:20 CEST